asked
Force Delete of thrashed record in SQL table
I have a thrashed record in a SQL Header Table. I try to delte it nbut it doesn't allow me to delete or to update it. The record exist in the Header table and not in the dependent Detail table. When I try to add the record in the Detail table, it gives me a message that the record must first exist in the Header table..... A vicious circle. I want to be able to force the delete! Please RSVP to
richard@ims-stlouis.com
This appears to be a duplicate of a question on the main SSC forums.
http://www.sqlservercentral.com/Forums/Topic977095-8-1.aspx And the recommendation there seems like the right thing to me - don't us a boy's tools to do a man's job. or Don't use SSMS / SQL Enterprise Manager / whatever to edit table contents. One or the other.
Hi. Can you post the schemas (or the relevant parts of the schemas) for the Header & Detail tables? And the details of the PK / FK relationships?
@Thomas - weird, that post has been updated to show he resolved the issue by rebuilding an index. Puzzled. Anyway, can a mod close this question please?
It sounds like you need to remove the constraint(s) between the 'header' and 'detail' tables. This should then let you make the deletion that you want to. Make sure that you recreate them afterwards so that you can replace them after the work is done, just to make sure that the overlying system has the necessary rules in place. It does however sound that these might need to be checked as the constraints should really have prevented this circumstance occurring
I think Fatherjack is abolutely right, but if you disable the FK and delete the record from "master" table, you may find that you can't add the FK again "WITH CHECK" option. In that case you have a record in the "detail" table, be sure to delete all records that is depending on the "master" record. Here you have some pseudo code: DELETE FROM childTable WHERE fkcolumn='pk value of the master' DELETE FROM masterTable WHERE pkcolumn='pk value of the master' If this works, there is no need to drop the FK. If the table don't have a primary or unique key (but then you can't have a FK relationship), you can't delete the record by using the "edit rows" in SQL server management studio. Then you need to do it by SQL statements, but keep in mind that you may delete all record that matches the where clause and as you don't have any unique constraints you may have duplicates. DELETE FROM table1 WHERE mycolumns='myfilter'
