CODEWITHSUNDEEP
sql-servert-sqlforeign-keysforeign-key-relationship
hofnarwillie
hofnarwillie

Reputation: 3660

Adding foreign key constraints to existing columns

I have a database that has gone through some major transformations over several years of having input from several different coders. Some of the tables contain references to other data through a reference column, but do not have foreign key constraints. Meaning that there are some tables that may contain dead links.

I would like to add foreign key constraints to all the reference columns. How can I ensure that this would be successful when some data may be referencing dead links and others may actually be null.

I think I need to say something like this:

ALTER TABLE dbo.Users ADD CONSTRAINT FK_Users_Persons FOREIGN KEY
(
    Person_ID --In some cases Person_ID will be null
) REFERENCES dbo.People
(
    Person_ID
) ON UPDATE  NO ACTION 
ON DELETE  NO ACTION 
NOT FOR REPLICATION

GO
ALTER TABLE dbo.Users
    NOCHECK CONSTRAINT FK_Users_Persons
GO
COMMIT

Please let me know if this is accurate and also if there are any hidden pitfalls?

Thanks in advance

Upvotes: 2

Views: 7695

Answers (2)

Timothy Gonzalez
Timothy Gonzalez

Reputation: 1908

Use a LEFT JOIN. This will show NULLs on items that can't join meaning the reference was deleted.

Upvotes: 0

marc_s
marc_s

Reputation: 754230

Just try it!! If there is a problem - a value that doesn't exist in the referenced table - SQL Server will tell you, and nothing bad will happen - trust me! :-)

Seriously: if there are issues, e.g. rows in Users that have invalid Person_ID values, then SQL Server will not create the FK constraint - as simple as that.

In that case, if you do have entries in the Users table that do not reference a valid Person_ID in the Person table - you will need to fix these first. Either set them to NULL (if you cannot figure out what person they reference), or setting them to a valid Person_ID - and then try to add your FK constraint again.

You can also find those entries before applying the FK constraint with a query something like this:

 SELECT (columns)
 FROM dbo.Users
 WHERE Person_ID IS NOT NULL 
 AND Person_ID NOT IN (SELECT DISTINCT Person_ID FROM dbo.Person)

Upvotes: 6

Related Questions