What are the repercussions of not checking existing data when adding a foreign key?

Question

I've inherited a database that doesn't exactly strive for data integrity. I am trying to add some foreign keys to change that, but there is data in some tables that doesn't fit the constraints. Most likely, the data won't be used again so I want to know what problems I might face by leaving it there. The other option I see is to move it into some kind of table without referential constraints, just for historical purposes.

So, what are the repercussions of not checking existing data?

If I create a foreign key constraint on a table and don't check existing data, will all new data inserted into the table be enforced?

Edit: I am indeed talking about using the WITH NOCHECK option when creating the foreign key.

Answer 1

The foreign key constraint should fail to create if it would be violated by existing data. The system will check for you. What you should do is create the column to use in the foreign key, point every row at an existing record (it could even be a single 'dummy' record that you indicate is linked to by legacy data or conversion data) by populating this column, then create the FK.

Answer 2

As I see it, you are no worse off if you do this than if you leave it alone since the bad data will still be in your database - if this data is not subject to change. It will check data from that point onward. So you are ahead in making sure future data is correct.

However, if any of the data in the bad records could change, you will have a problem as it will not allow the change as the FK check will fail. If this is the case and you cannot clean up the bad data, it might be best to fix the data as best you can by relating it to a parent record that is essentially named "Unknown". So if the parent table is customer and you no longer can tell who the customer was because the customer associated with the detail record was deleted without checking for FK records, then insert a customer record where the customer name is 'Unknown' and change the customer ID to the ID of that record.