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?

asked 20 Nov '18, 10:01

pasha19's gravatar image

pasha19
2906818
accept rate: 14%

1

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.

(21 Nov '18, 09:17) Breck Carter

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;
permanent link

answered 21 Nov '18, 03:20

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 21 Nov '18, 05:49

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.

(25 Nov '18, 14:40) pasha19
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:

×31

question asked: 20 Nov '18, 10:01

question was seen: 1,294 times

last updated: 25 Nov '18, 15:19