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
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
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.
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.