NoCheck Constraint
This is more a question to make sure I am doing this right.
I have a table, let's call it "clicks"
click_id int PK
product_id int
I am trying to correct an error from a previous time where the product_id was set to default to zero. I have a table of "products" and need to set the product_id to a null default in "clicks", remove the default constraint of 0 from "clicks" and then foreign key product_id in "clicks" to product_id in "products".
All is well with removing the constraint. However, I have 50,000,000 rows of "clicks" historical data with a 0 in the product_id. The job of sanitizing this data to update all the 0's to null is too big for now.
Question - If I create the FK with a NOCHECK Constraint would I have the correct FK structure going forward and have the DB basically ignore the legacy data (for now)?
Later, I can run the scripts to fix the legacy data and then alter the FK to be correct and check the constraint.
Am I on the right path?
I have a table, let's call it "clicks"
click_id int PK
product_id int
I am trying to correct an error from a previous time where the product_id was set to default to zero. I have a table of "products" and need to set the product_id to a null default in "clicks", remove the default constraint of 0 from "clicks" and then foreign key product_id in "clicks" to product_id in "products".
All is well with removing the constraint. However, I have 50,000,000 rows of "clicks" historical data with a 0 in the product_id. The job of sanitizing this data to update all the 0's to null is too big for now.
Question - If I create the FK with a NOCHECK Constraint would I have the correct FK structure going forward and have the DB basically ignore the legacy data (for now)?
Later, I can run the scripts to fix the legacy data and then alter the FK to be correct and check the constraint.
Am I on the right path?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yep, the plan is to get this going and then once all the product_ids are set to null (in batches) run the ALTER TABLE clicks WITH CHECK CHECK CONSTRAINT ALL so it is trusted.
ASKER
In other words, I get the FK between the tables for data from this point forward, but the legacy data would just not perform as well?