11

I just noticed few tables that has orphan records (foreign keys) that no longer point to any primary key. How can I query the tables to get a list of all such rows? When these parent records got deleted or how to handle this?

I'm using SQL Server 2008 R2 and we are trying to import 3 similar databases into one after cleaning it up, please advise.

Scripted a sample child table (DocumentDistribution) and parent table (DocumentSource):

ALTER TABLE [dbo].[DocumentDistribution] WITH NOCHECK ADD CONSTRAINT [fk_documentdistsourceid_documentsourceid] FOREIGN KEY([DocumentDistSourceID]) REFERENCES [dbo].[DocumentSource] ([DocumentSourceID]) 
GO
 ALTER TABLE [dbo].[DocumentDistribution] CHECK CONSTRAINT [fk_documentdistsourceid_documentsourceid] 

Doesnt that tell me that the tables are in relationships and explicitely defined the foreign key constraints?

0

1 Answer 1

27

This should only be possible if you have a relationship between tables that you know about but SQL Server doesn't.

SELECT fk 
  FROM dbo.ChildTable AS c
  WHERE NOT EXISTS
  (
    SELECT pk FROM dbo.ParentTable AS p
    WHERE p.pk = c.fk
  );

Now, in the future, define this relationship explicitly, then people won't be able to delete the parent rows while child rows still exist. To delete the rows that shouldn't be there today:

DELETE c
  FROM dbo.ChildTable AS c
  WHERE NOT EXISTS
  (
    SELECT pk FROM dbo.ParentTable AS p
    WHERE p.pk = c.fk
  );

Next, take away their rights to disable these constraints so that they stop doing this the wrong way.

1
  • You can also do the opposite action by switching child and parent tables. This would delete all parent rows that are not referred to by the child table - orphan parents, if you will.
    – Jules
    Commented Sep 28, 2015 at 18:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.