Hi,

I am developing with ultralite 12 with build 3873, recently I found a problem that foreign key MUST be defined in the order which each column is defined in the "referenced to " table. otherwise it will give me an error.

For example: I have two table


    CREATE TABLE "DBA"."test1" (
        "pk1"                     integer NOT NULL
       ,"pk2"                     integer NOT NULL
       ,PRIMARY KEY ("pk1" ,"pk2" ) 
    );

CREATE TABLE "DBA"."test2" (
    "fk2"                     integer NOT NULL
   ,"fk1"                     integer NOT NULL
   ,"pk3"                     integer NOT NULL
   ,PRIMARY KEY ("pk3" ) 
);

In test1 we have value


    insert into test1 ("pk1", "pk2") values(1,2);

Then if I have a reference constraint defined as following


    ALTER TABLE "DBA"."test2"
    ADD FOREIGN KEY "fk_Test" ( "fk1", "fk2" )
    REFERENCES "DBA"."test1" ("pk1", "pk2");
it works fine. i can do the insert

insert into test2 ("fk1", "fk2", "pk3") values(1,2,0);

However, if I define the constraint as following


    ALTER TABLE "DBA"."test2"
    ADD FOREIGN KEY "fk_Test" ( "fk2", "fk1" )
    REFERENCES "DBA"."test1" ("pk2", "pk1");
When I try same insert it gives me an error. Note that the only change is the foreign key order in both

What is worse is that when I convert my sybase anywhere12 database to ultralite, the foreign key constraint is created based on current table's column id, which may lead to the issue I was talking about.

Thanks,

asked 07 Jun '13, 23:34

Peng84's gravatar image

Peng84
66337
accept rate: 0%

1

"gives me an error"... what exactly is the error message and/or SQLCODE?

(08 Jun '13, 08:41) Breck Carter
Replies hidden

SQLCODE=-194, ODBC 3 State="23000" [Ultralite Database] No primary key value for foreign key 'fk_Test' in table 'test2'

(10 Jun '13, 09:40) Peng84

Looks like a [cough] bug to me.

(10 Jun '13, 17:04) Breck Carter

This appears to be a bug. I have opened CR #741393 to investigate this issue further.

Notably, the reverse order on the insert does work, but would be incorrect:

insert into test2 ("fk1", "fk2", "pk3") values(2,1,0);

I will update this thread when we have additional information available.

permanent link

answered 11 Jun '13, 09:18

Jeff%20Albion's gravatar image

Jeff Albion
10.7k171174
accept rate: 24%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×135
×22

question asked: 07 Jun '13, 23:34

question was seen: 804 times

last updated: 11 Jun '13, 09:18