• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 303
  • Last Modified:

Problem deleting a record from a SQL 2005 parent table

I have a parent table with several child tables.  If I try to delete a record from the parent table I get the error message "Unable to delete the "parent table name" record because it is being referenced by "child table 1 name". This makes sense.  What doesn't make sense is when I delete the record from "child table 1 name" and then attempt to delete the "parent table name" record I get the exact same error.  I swear the record no longer exists in child table 1.  Is there something that needs updating in the parent table?  If I restart SQL will that help?
0
mbegalle
Asked:
mbegalle
  • 5
  • 4
  • 3
1 Solution
 
ThorSG1Commented:
If you are sure the record does not exist then you can do the following.

ALTER TABLE parent_table_name NOCHECK CONSTRAINT pk_constraint_name
DELETE FROM parent_table_name WHERE X = X
ALTER TABLE parent_table_name CHECK CONSTRAINT pk_constraint_name

Open in new window

0
 
BrandonGalderisiCommented:
You don't want to disable the constraint.  If the record truly does not exist then you will not get an error.  I suggest you take a look at your data.

This will tell you if you TRULY have no child record... (swap tables names as necessary).  If you have records
select p.* from ParentTable p
 left join childtable c
  on p.id=c.id
where c.id is null
and p.SomeField='something' -- optional

Open in new window

0
 
mbegalleAuthor Commented:
On my test server I don't see which constraint would be the problem so I removed them all and I get the same error.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
mbegalleAuthor Commented:
I tried this query
select p.* from ParentTable p
 left join childtable c
  on p.id=c.id
where c.id is null
and p.SomeField='something' -- optional

All the fields in the child table come up null.

0
 
ThorSG1Commented:
You may need to disable the foreign key constraint.
In case you have not checked your ChildTable for the value I have added a script below that you can use to look for the value in the ChildTable.
To determine what your primary and foreign keys are you can use sp_help.  One of the results will have PRIMARY KEY in the first column.  The second column will have the name of the key.  I don't remember if it will list the foreign keys I would run it against the child table to see if it will list it for you.  Examples below.

SELECT *
FROM ChildTable
WHERE name = 'Value Your Trying to Delete' -- Leave the single quotes
 
sp_help parent_table_name
sp_help child_table_name
 
ALTER TABLE child_table_name NOCHECK CONSTRAINT fk_constraint_name
ALTER TABLE parent_table_name NOCHECK CONSTRAINT pk_constraint_name
DELETE FROM parent_table_name WHERE X = X
ALTER TABLE parent_table_name CHECK CONSTRAINT pk_constraint_name
ALTER TABLE child_table_name CHECK CONSTRAINT fk_constraint_name

Open in new window

0
 
BrandonGalderisiCommented:
No.... You don't need to disable any constraints.  They are there for a reason.

I should have clarified...


and p.SomeField='something' -- optional


you should change this line to filter on the line you are trying to delete.  
0
 
ThorSG1Commented:
Brandon
I have seen this same problem with an empty child table.  There are times when you have to disable the constraint to do a delete or truncate on the parent table.
0
 
mbegalleAuthor Commented:
sp_help parent_table_name and sp_help child_table_name both return the following message.

No foreign keys reference table 'table_name', or you do not have permissions on referencing tables.
No views with schema binding reference table 'tmfRouthead_HAI'.
0
 
BrandonGalderisiCommented:
At no time do you need to disable a foreign key to delete from a table.  Truncate, sure.  But they're not trying to truncate the table.  They are trying to delete.
0
 
mbegalleAuthor Commented:
There were two fields in the child table that linked up to the parent.  Sorry I missed that.  I still don't know what contraint was keeping me from deleting the child.

Thanks
0
 
BrandonGalderisiCommented:
Well deleting the record would raise the error with the specific constraint name that would be violated.
0
 
BrandonGalderisiCommented:
Had you listened to me, and not gone down the path that ThorSG1 led you with dropping constraints, you would have been able to solve your problems.  You not following the advice given does not mean that the advice given was not correct.

You were given proper information.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now