Foreign key constraint ON DELETE "NO ACTION"

[huber1]

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

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

[VolkerBarth]

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"?

[huber1]

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.

[huber1]

Volker, thanks for asking. No, I did not mean or refer to SET NULL. Although, we use that as well, but not so often.

[VolkerBarth]

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:

NO ACTION and RESTRICT are very much alike. The main difference between NO ACTION and RESTRICT is that with NO ACTION the referential integrity check is done after trying to alter the table. RESTRICT does the check before trying to execute the UPDATE or DELETE statement. Both referential actions act the same if the referential integrity check fails: the UPDATE or DELETE statement will result in an error.

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.

[huber1]

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.

[Breck_Carter]

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.