Hi I would like to apply a foreign key constraint with a ON DELETE <action> for do nothing. But the <action> for ON DELETE (according to the docu) can only be: CASCADE | SET NULL | SET DEFAULT | RESTRICT I tried: ALTER TABLE absences ADD CONSTRAINT absence_belonging_to_fk FOREIGN KEY ( person_id ) REFERENCES persons ( id ) ON DELETE NO ACTION ; but this syntax is not accepted. What would be the Syntax for this? Thanks and best regards, Robert asked 01 Sep '15, 10:48 robert |
SQL Anywhere does not offer NO ACTION. However, it offers RESTRICT, and AFAIK that's almost the same - the DELETE will be rejected if it would violate the RI constraint. To cite from Wikipedia:
FWIW, SQL Anywhere allows to delay the check until the transaction is committed (making it semantically to "NO ACTION" according to the cited definition) via the WAIT_FOR_COMMIT option. answered 01 Sep '15, 10:58 Volker Barth Volker Thank you for the cite from Wikipedia. I thought RESTRICT would be like DENY. Have you got an idea what the Sybase correspondence to DENY is? What I mean with DENY is, the 1-side can't be deleted as long as there are m-side records. That is, to delete the record on the 1-side, there must not be any record(s) on the m-side of the relationship.
(01 Sep '15, 11:10)
robert
Replies hidden
RESTRICT is indeed "like deny", and so is NO ACTION. Read the description again. You are not asking for either, you are asking for something that is not available.
(01 Sep '15, 11:31)
Breck Carter
|
You are not asking for "NO ACTION"; that is just a funky (and very badly named) variation of "RESTRICT". You are asking for the foreign key column in the child table to remain unchanged after the parent row is deleted. The only way to implement that is to drop the foreign key constraint altogether, and (if you desire) write your own trigger implement the ON UPDATE action. For performance purposes (joins and the like) you may want to create an index on the absences.id column in lieu of the index that would ordinarily exist on that column because it is in a foreign key constraint. answered 01 Sep '15, 11:33 Breck Carter Robert, or do you mean "SET NULL" - i.e. when the parent record is deleted, the child record's parent_id is set to NULL - say, some kind of "orphaned"?
(01 Sep '15, 16:49)
Volker Barth
Breck, thanks for clarifying, I was used to the NO ACTION term (because the Data Modeling Tool used this one) but RESTRICT is surely more intuitiv. In the Wiki the SQL 2003 Standard is referenced and there NO ACTION is on defined term for foreign key constraint. It seems even in standards body it's difficult to find good namings.
(02 Sep '15, 10:20)
robert
Volker, thanks for asking. No, I did not mean or refer to SET NULL. Although, we use that as well, but not so often.
(02 Sep '15, 10:21)
robert
|