limiting sql server constraint checks to only new data

Question

I need to import a lot of data into a sql server database. Some of this data was imported from long ago and much of it does not maintain referential integrity but I want all new data to. I can turn off the constraint checks prior to the import but how do it turn them back on only for data that will be added later? Is this possible or do I have to fix the historical data?

Thanks,

Answer 1

As an ugly alternative, I suppose that you could have two tables -- one for old bad data, and one for new. Put the DRI constraints only on the new table. Then wrap them together with a UNION in a VIEW, so that other code can query them together as a single unit.

Answer 2

drop your FK constraints, add the data, add your FKs back with NOCHECK

Answer 3

You can add a constraint that will check data only from that time forward, but if you try to update that row (as in an import or even through the user interface), it will hit the constraint and fail unless you fix the data. I strongly recommend you fix the data before adding constraints. It's bad data anyway and should be fixed. YOu might need to add a default record for UNKNOWN in the parent table if you have records that have a child id but no parent. Then change the refernce to the UNKNOWN record ID.

Answer 4

use NOCHECK

example

CREATE TABLE CheckTbl (col1 int, col2 int);
GO


insert CheckTbl values(1,1)
insert CheckTbl values(2,1)

ALTER TABLE CheckTbl  WITH NOCHECK 
ADD CONSTRAINT chkRowCount CHECK (col1 > 1) ;
GO

-- will fail now
insert CheckTbl values(1,1)