Issues with adding foreign key constraint in SQL Server?

Question

I am using SQL Server, and I need to add a foreign key to an already existing table.

The issue is the column which will act as the foreign key already has a few inconsistent values (which do not occur as a primary key) in another table.

I was wondering, when I alter the table and add the foreign key constraint, what will happen to the rows in the table with foreign key constraint , which has inconsistent values??

--Neeraj

Answer 1

In this case it is your decision. You can leave this values in table using WITH NOCHECK clause. But all new inserted values will be checked.

Answer 2

You'll get an error and nothing will be inserted.

To find all inconsistent rows (supposing that A and B are the target tables, A.id is a parent key and B.fk_id is a child, foreign key, id):

   SELECT B.fk_id
     FROM B
LEFT JOIN A ON A.id = B.fk_id
    WHERE A.id IS NULL

After executing it you'll have all child rows that refers to "nowhere". So you either need to remove them, modify to point to the existing rows or set B.fk_id to NULL (if there is no NOT NULL constraint).

And after that query returns 0 rows - you can safely create foreign key constraint without any magic options.