CODEWITHSUNDEEP
sqlsql-server
webdad3
webdad3

Reputation: 9080

Cascade Delete turned on?

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.

Upvotes: 23

Views: 18981

Answers (3)

msi77
msi77

Reputation: 1632

You can use INFORMATION_SCHEMA for standard approach, ex.

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

Upvotes: 34

RGV
RGV

Reputation: 742

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

Upvotes: 7

webdad3
webdad3

Reputation: 9080

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.

Upvotes: 1

Related Questions