on 2018 Nov 20 10:01 AM
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?
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;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
79 | |
11 | |
10 | |
8 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.