Cascade Delete turned on?

Question

Is there a way to check if the table(s) have Cascade Delete turned on? I'm looking at the script of the table (from SQL Server) and I don't see any indication of Cascade Delete.

http://stackoverflow.com/questions/14229277/sql-server-2008-get-table-constraints — RGV, Jun 07 '13 at 14:36
*Tables* don't have cascade delete turned on, per se. Foreign Key constraints do. — Damien_The_Unbeliever, Jun 07 '13 at 14:36

score 32 · Answer 1 · answered Jun 08 '13 at 12:07

32

You can use INFORMATION_SCHEMA for standard approach, ex.

select * from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
where DELETE_RULE ='CASCADE'

answered Jun 08 '13 at 12:07

msi77

1,602
1
11
10

score 7 · Accepted Answer · edited May 23 '17 at 12:01

7

Please use sys.foreign_keys for foreign key relations.

The column - delete_referential_action helps you know if there is a delete on cascade.

http://technet.microsoft.com/en-us/library/ms189807.aspx

Below View help with similar works:

sys.default_constraints for default constraints on columns
sys.check_constraints for check constraints on columns
sys.key_constraints for key constraints (e.g. primary keys)
sys.foreign_keys for foreign key relations

Source: SQL Server 2008- Get table constraints

edited May 23 '17 at 12:01

Community

1
1

answered Jun 07 '13 at 14:41

RGV

732
3
10

Thanks for your answer. This is way quicker and easier than my solution. Do you know how I can turn on Cascade Delete on a FK that has a delete_referential_action = 0 and a delete_referential_action_desc = 'NO_ACTION' – webdad3 Jun 07 '13 at 15:21

score 1 · Answer 3 · answered Jun 07 '13 at 14:48

I found how to do this:

I scripted the FK on the table to a new query window:

ALTER TABLE [dbo].[myTable]  WITH CHECK ADD  CONSTRAINT [FK_myTable_myTableHeaders] FOREIGN KEY([ID])
REFERENCES [dbo].[myTableHeaders] ([_ID])
ON DELETE CASCADE
GO

This how I was able to confirm it.

Cascade Delete turned on?

3 Answers3