CODEWITHSUNDEEP
sqlsql-server-2012delete-row
peter kover
peter kover

Reputation: 77

Deleting row in table with primary key and related rows in different table with foreign key

I have a webapp that connects to a database with several tables.

For testing purposes, I need to delete one of the rows in a table. I'm using the following line:

delete from EventsTable where Name = 'John's Party'

However, when running this I'm getting the below error message:

The DELETE statement conflicted with the REFERENCE constraint "FK_dbo.People_dbo.EventsTable_EventId". The conflict occurred in database "PlannerDatabase", table "dbo.People", column 'EventId'.

This, I assume, is happening because of the foreign key being tied to a primary key in the EventsTable but my question is:

Is there any way to, with the least amount of SQL code possible, delete "John's Party" from the EventsTable and all rows related to that party in the rest of tables?

I'm using SQL Server Management Studio 2012.

Upvotes: 0

Views: 5103

Answers (1)

domenicr
domenicr

Reputation: 352

This can be accomplished with no code per se, by adding a On Delete Cascade trigger to the table.

You can see all the references to your table by executing EXEC sp_fkeys 'EventsTable'

Then you can create cascading deletes like so:

ALTER TABLE dbo.People DROP CONSTRAINT FK_dbo.People_dbo.EventsTable_EventId -- or whatever it's called

ALTER TABLE dbo.People ADD CONSTRAINT FK_T1_T2_Cascade FOREIGN KEY (EventId) REFERENCES dbo.EventsTable(EventId) ON DELETE CASCADE

Upvotes: 1

Related Questions