Adding foreign key constraints to existing columns

Question

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

Accepted Answer

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)
```

Adding foreign key constraints to existing columns

Solution

Problem