DELETING using Inner join

Question

I followed how to execute delete from: How to Delete using INNER JOIN with SQL Server?

The data I want to delete:

  select * from com.Address a
  inner join com.Contact as c on c.AddressId = a.AddressId
  inner join cqt.CQMTrainer as t on t.ContactId = c.ContactId


 select from com.Contact c
  inner join cqt.CQMTrainer as t on t.ContactId = c.ContactId

Here is the delete I am trying to execute:

  delete a from com.Address a
  inner join com.Contact as c on c.AddressId = a.AddressId
  inner join cqt.CQMTrainer as t on t.ContactId = c.ContactId

  delete c from com.Contact c
  inner join cqt.CQMTrainer as t on t.ContactId = c.ContactId

However, once I execute them I got some foreign key error:

The DELETE statement conflicted with the REFERENCE constraint "FK_com.Contact_com.Address_AddressId". The conflict occurred in database "", table "com.Contact", column 'AddressId'.

What am I following incorrectly? I even tried adding begin transaction and commit transaction.

I think it is the foreign keys that are preventing you from deleting those records. You should delete the records from other tables first — OHHO, Apr 05 '16 at 19:51
Contact is the parent of Address. Im trying to delete BOTH of them. First address and then parent. So technically, I am deleting the records from other tables? — Oct8, Apr 05 '16 at 20:01
@J.Doe If you had the foreign key setup to delete on cascade then it would of also delete from the other tables. If not then the error that you are getting will occur. See this [StackOverflow question](http://stackoverflow.com/questions/6260688/how-do-i-use-cascade-delete-with-sql-server). If you don't want to modify the foreign key then you will need to delete from the other table first. — John Odom, Apr 05 '16 at 20:06
@J.Doe, change delete order. First delete from contact table which references address table (and that's why delete from addresses first causes error), then delete addresses. Also there is a chance that some other records will still refer addresses you're attempting to delete. — Ivan Starostin, Apr 05 '16 at 21:18

score 1 · Accepted Answer · answered Apr 05 '16 at 21:27

what about if you do it on the other way:

delete c from com.Contact c
inner join cqt.CQMTrainer as t on t.ContactId = c.ContactId 

delete a from com.Address a
inner join com.Contact as c on c.AddressId = a.AddressId
inner join cqt.CQMTrainer as t on t.ContactId = c.ContactId

First you are going to delete the information on the contact table as the error
says you have a reference on contact for address id that's why you can not delete the address table first

Jakub Szumiato · Answer 2 · 2016-04-05T20:30:10.753

Looking at your query your relations have been configured to throw exception when you try to delete a record which is referenced by the other one. If you want to avoid it, you have to choose how such case should be handled by the database server. This is being set up on the reference properties. If you are using SQL Server Management Studio, then:

click on the table with right mouse button select 'Design'
then the column on which you have relation defined (it will display it as rows)
then right mouse button click on the column you're interested in, select 'Relationships'
then in the left pane find your relationship by name, click it
then in the right pane, you will sea 'INSERT and UPDATE Specification' group, unfold it
then you will find two rules, one for update, one for delete, you can select:
- No Action - throws exception like in your case
- Cascade - deletes the referencing rows
- Set Null - will put null in the rows that are referencing this row by FK values
- Set Default - will set the default value for such column.

If you're not using such editor, you can set the same by putting after

REFERENCES TableName ColumnName

values like:

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

It's so unusual to see button-clicking scenarios in answers on such a ddl-questions)) — Ivan Starostin, Apr 05 '16 at 21:17
It's a very basic problem to solve here, so I thought the solution should be also fairy easy to implement. I know a lot of people that are using gui to work with SQL Server at the beginning. I added also the script part if someone is familiar with it. Was it a bad answer in your opinion? — Jakub Szumiato, Apr 06 '16 at 04:55

DELETING using Inner join

2 Answers2