Foreign key recursive (into same table) appears to require CHECK ON COMMIT

[Former Member]

Using SQLAnywhere Developer v16 (I believe this observation is valid)

if a table contains columns id, idParent, Name

PK is id and FK between idParent (child) and id (parent) is established for the table

INSERT INTO table ( id, idParent, Name ) VALUES ( 0, 0, 'none');

appears to require the recursive foreign key relationship between id and idParent to be coded with CHECK ON COMMIT

Probably because the PK index for ID has not been updated for the row being added when the foreign key is checked.

QUESTION: Should the foreign key logic test for the special recursive relationship where id = idParent on insertion of a row so CHECK ON COMMIT is not required?

[Breck_Carter]

Do you have a trigger on the insert?

Sometimes "impossible" exceptions are caused by hidden code inside triggers... it is the first place to look in those cases.

[VolkerBarth]

I have used tables with self-references from SQL Anywhere 5.5 to 17 and have never needed to use the CHECK ON COMMIT clause, and the wait_for_commit option has usually not been set to 'On'.

I don't remember that cases with insertions of a row that references itself in one go, i.e. where id and idParent are the same, led to problems.

Here's a simple test, and I'm setting the DBISQL auto_commit to 'Off' to batch the inserts into one transaction:

drop table if exists T_TestRI;
create table T_TestRI (
   id       int not null primary key,
   idParent int not null,
   someText varchar(30) not null,

   foreign key (idParent)
      references T_TestRI(id)
);
set option auto_commit = 'Off';
begin
   insert T_TestRI values (0, 0, 'self');
   insert T_TestRI values (1, 1, 'another self');
   insert T_TestRI values (2, 1, 'parent');
   insert T_TestRI values (3, 1, 'same parent');
   select connection_property('wait_for_commit');
end;
commit;
insert T_TestRI values (4, 6, 'unknown parent'); -- fails
 -- successful when transaction is allowed to continue after previous error
insert T_TestRI values (5, 5, 'yet another self');
commit;
select * from T_TestRI; -- lists entries with ids 0-3 and 5
commit;

[Former Member]

Thanks I am checking. My problem there is a second recursion on the same column I missed; it should work when filled in. Sorry I was too tricky for my own good on this design.