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's gravatar image

robert
853475468
accept rate: 0%


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.

permanent link

answered 01 Sep '15, 10:58

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 01 Sep '15, 10:59

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.

permanent link

answered 01 Sep '15, 11:33

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

edited 01 Sep '15, 11:39

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
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
×11

question asked: 01 Sep '15, 10:48

question was seen: 4,504 times

last updated: 02 Sep '15, 10:21