0

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.

peter kover
  • 77
  • 2
  • 10
  • You should take a look at [Stack OverFlow Question](http://stackoverflow.com/questions/6260688/how-do-i-use-cascade-delete-with-sql-server). And [Cascade Delete](http://www.techonthenet.com/sql_server/foreign_keys/foreign_delete.php) – Roberto Apr 21 '16 at 15:13

1 Answers1

1

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

domenicr
  • 352
  • 3
  • 14