ultralite 12 foreign key issue

[Former Member]

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,

[Breck_Carter]

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

[Former Member]

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

[Breck_Carter]

Looks like a [cough] bug to me.

[jeff_albion]

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.