delete statement conflicting with foreign key

[fikir]

I have two tables they have a foreign key relationship

tbl1

col1 col2 col3
1 1 a
2 1 b
3 2 c

tbl2

col1 col2
1 aa
2 bb

the foreign key is defined on col2 of tbl1 referncing col1 of tbl2

I am tring to delete data from both tables and wrote this kind of query

delete from tbl1
from tbl1 inner join tbl2
on tbl1.col2 = tbl2.col1
where tbl2.col1 = 1

delete from tbl2
where col1 = 1

it delete the records from tbl1 but when it is trying to delete records from tbl2, it is failing because of foreign key conflict

here is the error

DELETE statement conflicted with COLUMN REFERENCE constraint fk_col2 in database x table tbl1 column col2

and it is not deleting data from tbl1, I couldn't figuring it out why it is failing,

somebody has an idea why is is failing?

Thanks,

 

[SQLDenis]

you first need to delete from table 2 (FK) and then from table1 (PK)

or you can set up cascade deletes and it will do it automatically when you delete from table 1

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[fikir]

I am deleting first from the child table which is tbl1 i.e the foreign key then the primary key, but still it is failing because of the same reason

couldn't figure out still

 

[SQLSister]

Best advice I can give you is to check the recordset that your delete would return if it was a select and then check those items in the other table. Possibly the first statement didn't delete all the records you thought it would delete.

"NOTHING is more important in a database than integrity." ESquared

 

[SQLDenis]

is there another relationship between another table and tbl1?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[fikir]

the first query is deleting every thing,

is there a possibilty that when a child table is deleted and the parent is not recognizing it?

thanks

 

[SQLSister]

is it all in the same transaction?

"NOTHING is more important in a database than integrity." ESquared

 

[SQLSister]

or read the message about the foreign key conflict, possibly there is another foreign key table that needs to be deleted first.

"NOTHING is more important in a database than integrity." ESquared

 

[ESquared]

exec sp_helpconstraint 'yourtablenamehere'

It's so easy to find these things out.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]