I'm using SQL Server, and I have to give a foreign answer to a previously existing table.
The problem may be the column that will behave as the foreign key already includes a couple of sporadic values (that do not occur like a primary key) in another table.
I'm wondering, after i affect the table and add the foreign key constraint, what's going to occur to the rows within the table with foreign key constraint , that has sporadic values??
Within this situation it's your decision. You are able to leave this values in table using WITH NOCHECK clause. But brand new placed values is going to be checked.
You will get a mistake and absolutely nothing is going to be placed.
To locate all sporadic rows (assuming that
B would be the target tables,
A.id is really a parent key and
B.fk_id is really 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 performing it you will have all child rows that describes "nowhere". Which means you either have to take them off, modify to suggest towards the existing rows or set
NULL (if there's no
NOT NULL constraint).
And then query returns rows - you are able to securely create foreign key constraint with no miracle options.