May 15, 2014 at 6:18 am
1. In a table having FK relation with couple of tables, How can an delete be performed. How to identify the delete sequence.. which table should delete happen first, second..
2. In a table with FK, (delete/update with no action set), when i delete rows in child table, it also delets the same rows in parent table. Why is that?
May 15, 2014 at 6:46 am
What do you find in the INFORMATION_SCHEMA for the constraints? Look for the table and column names in question.
😎
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
SELECT * FROM INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
May 15, 2014 at 6:55 am
Yes, i do have the list of FK for the parent table. But when i start deleting in child tables it throws up error. I dont know if i have start from a different table. Ex: tables .. a, b, c,d,e,f
Delete from a where ..
Delete from b where ..
..
Delete from f where ..
this is not working. Should i change the order (and not use a..f)
May 15, 2014 at 7:02 am
2. check for a trigger in child table with cascade option
May 15, 2014 at 7:15 am
Assuming TableA is the parent to TableB and that's the parent to TableC, then your delete order should be:
TableC
TAbleB
TableA
If you try anything else, you'll get referential constraint errors, as it should be.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 16, 2014 at 2:03 am
It means i need to know the parenta, then the next child B, which is parent to another table C. Is there a way to find that exact sequence of which is parent and which is child
May 16, 2014 at 5:05 am
Yes, you look at the foreign key relationships.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 16, 2014 at 6:00 am
The child table will have a foreign key constraint to the parent. Do you understand why you have to delete the child rows before deleting the parent row?
May 16, 2014 at 7:20 am
I have stuck up on insert. I ran insert in child table. it gives referential constraint error (with parent table). so i try DELETE parent table. It gives same error (relation to above child table).
May 16, 2014 at 7:26 am
When you try to delete from the child table, are you sure the referential integrity error is with the parent table? It could be that the child table has a foreign key constraint to a child table of its own. In other words, if A is the parent of B and B is the parent of C, when you delete from B, you should be getting an error on the foreign key to C, not to A.
If there really are only two tables involved, I suppose it's possible that you have a situation where you have a constraint defined on both tables to each other, but that would be a serious design issue. Check the definition of the foreign key constraints on both tables. The foreign key should exist in the child and reference the parent. It's most likely (at least I hope it is) that your child table has a child of its own.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply