March 12, 2010 at 8:06 am
I'm migrating a bunch of data from an old database schema (no FOREIGN keys in the database) to a new schema (with FOREIGN keys) I know that there are issues in the data to start with so I'm creating All FK with the NOCHECK option. I know that this will mean that the FK is not trusted.
Once the data is corrected I want to re-enable the FK with CHECK. Will this change the FK to a trusted status?
Thanks
Gary
March 12, 2010 at 8:22 am
I handle the situation by putting all my FKrelations in a metadata tables.
Then I created a procedurte that checks alle these Foreign Keys (add indexes upfront do speed up)
Next a procedure to drop all wrong data, this you can do based on the foreign keys(you need to drop first the referenced tables, use a recursice CTe and order on lvl)
Then I created a procedure to add the foreign keys.
This we use before we import the data and want to be sure that it's consistent.
Wim.
March 12, 2010 at 8:26 am
This is customer data so I can't just delete..... I need to add the WITH NOCHECK for that no futher integrity errors occur while the customer fixes there data and the application is still in use.
I just need to understand if I need to write a script that will drop all FK and re-create wihout the NOCHECK clause or if I can just use ALTER tableName WITH CHECK CHECK CONSTRAINT constraintName and the FK will become trusted.
March 12, 2010 at 8:35 am
CREATE TABLE dbo.cnst_example
(id INT NOT NULL,
name VARCHAR(10) NOT NULL,
salary MONEY NOT NULL
CONSTRAINT salary_cap CHECK (salary < 100000)
);
-- Valid inserts
INSERT INTO dbo.cnst_example VALUES (1,'Joe Brown',65000);
INSERT INTO dbo.cnst_example VALUES (2,'Mary Smith',75000);
-- This insert violates the constraint.
INSERT INTO dbo.cnst_example VALUES (3,'Pat Jones',105000);
-- Disable the constraint and try again.
ALTER TABLE dbo.cnst_example NOCHECK CONSTRAINT salary_cap;
INSERT INTO dbo.cnst_example VALUES (3,'Pat Jones',105000);
-- Re-enable the constraint and try another insert; this will fail.
ALTER TABLE dbo.cnst_example CHECK CONSTRAINT salary_cap; <<-- this you need
INSERT INTO dbo.cnst_example VALUES (4,'Eric James',110000) ;
For a foreign key:
ALTER TABLE [Import].[BOM] WITH NOCHECK ADD CONSTRAINT [FK_Base.BOM_LEName] FOREIGN KEY([LECode])
REFERENCES [Import].[LE]([LECode])
ALTER TABLE [Import].[BOM] CHECK CONSTRAINT [FK_Base.BOM_LEName]
This will only check new rows, best is to drop andrecreate the Foreign Key if you want to check all the rows including existing rows.
March 12, 2010 at 8:41 am
Did you try ALTER TABLE [Import].[BOM] WITH CHECK CHECK CONSTRAINT [FK_Base.BOM_LEName]
That will fail also if the data is not correct..... It is checking existing data when you do that. I just what to understand the Trusted part for things that will pass once I run the statement.
March 12, 2010 at 8:46 am
Thx I didn't know that you could do it like that.
If you run that statement and all data is consistent and the foreign key will be placed and everything new will also be checked , so you can say that the foreign key can be trusted.
It will be the same as if you created the foreign key upfront and inserted the data afterwards.
Is this your question?
March 12, 2010 at 8:48 am
Yes that is the question.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply