I want to have a FK constraint in a child table so that when I delete a record from the child table, the primary record is NOT deleted.

I did create a FK constraint in the child table with ON DELETE CASCADE thinking that this would delete only the child-table record when a child table delete record operation is intended and when deleting the primary table record.

I know that cascade delete operation will/should cascade to the child-table.

ALTER TABLE [dbo].[PTINSPECTION] WITH CHECK 
    ADD CONSTRAINT [FK_PTINSPECTION_MOTRIP] 
        FOREIGN KEY([UID_MOTRIP])
        REFERENCES [dbo].[MOTRIP] ([UID_MOTRIP])
        ON DELETE CASCADE

This may sound stupid, but I googled questions about cascade delete and a couple of responses stated that cascade delete would apply ALSO to the primary table (meaning it would delete the corresponding parent record).

This did not make sense to me and this is why I am asking this question...

Is it possible that the child FK constraint allows for deleting the primary record when a deleting a child table record or records?

I want to have a FK constraint in a child table so that when I delete a record from the child table, the primary record is NOT deleted.

I did create a FK constraint in the child table with ON DELETE CASCADE thinking that this would delete only the child-table record when a child table delete record operation is intended and when deleting the primary table record.

I know that cascade delete operation will/should cascade to the child-table.

ALTER TABLE [dbo].[PTINSPECTION] WITH CHECK 
    ADD CONSTRAINT [FK_PTINSPECTION_MOTRIP] 
        FOREIGN KEY([UID_MOTRIP])
        REFERENCES [dbo].[MOTRIP] ([UID_MOTRIP])
        ON DELETE CASCADE

This may sound stupid, but I googled questions about cascade delete and a couple of responses stated that cascade delete would apply ALSO to the primary table (meaning it would delete the corresponding parent record).

This did not make sense to me and this is why I am asking this question...

Is it possible that the child FK constraint allows for deleting the primary record when a deleting a child table record or records?

• sql
• sql-server
• t-sql

• 9 No.. child deletion will not delete parent, – P.Salmon Commented Feb 6 at 20:03
• 1 If you want the parent to not be deleted, why would you put ON DELETE CASCADE? You are telling it to also delete the parent. – Eric Commented Feb 6 at 20:06
• 1 @Eric -- so is your answer YES deleting a child-record with ON DELETE CASCADE will delete the parent record? Please confirm. – John D Commented Feb 6 at 20:14
• 2 Consider, if deleting a single child record then caused the deleting of the parent, that would require deleting any other children also... – Dale K Commented Feb 6 at 20:32
• 1 Again, you can delete all children and the parent row won't be deleted, regardless of the ON DELETE clause. Just to make sure it's all well understood. – The Impaler Commented Feb 6 at 22:17

1 Answer 1

The font of all knowledge is the manual - in the create table section the pertinent details for FKs are

ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }

Specifies what action happens to rows in the table created, if those rows have a referential relationship and the referenced row is deleted from the parent table.

CASCADE

Corresponding rows are deleted from the referencing table if that row is deleted from the parent table.

enter link description here

A consequence of this is (as others have pointed out) you may end up in the situation where parents/referenced row has no children/referencing rows. SQLSERVER does not have inbuilt utility to deal with this - but that's a different question.