May 18, 2011 at 10:27 am
opc.three (5/17/2011)
ooops...
Sorry for the earlier ooops-post, I posted an incomplete thought and was getting pulled away so just wiped it.
I was going to point out that you can also add FKs "WITH NOCHECK" from the outset which has a certain advantage when trying to harden legacy databases by allowing you to add RI incrementally. If you take over a legacy system you may also encounter untrusted constraints for this reason as well. This code sample and inline comments demonstrate the approach:
SET NOCOUNT ON ;
GO
USE tempdb
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.child_test')
AND type IN (N'U') )
DROP TABLE dbo.child_test ;
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.parent_test')
AND type IN (N'U') )
DROP TABLE dbo.parent_test ;
GO
PRINT '> Create tables and test data' ;
CREATE TABLE dbo.parent_test
(
id INT NOT NULL
IDENTITY(1, 1)
PRIMARY KEY,
name VARCHAR(100) NOT NULL
) ;
CREATE TABLE dbo.child_test
(
id INT NOT NULL
IDENTITY(1, 1)
PRIMARY KEY,
parent_id INT NOT NULL,
name VARCHAR(100) NOT NULL
) ;
GO
INSERT INTO dbo.parent_test
(name)
VALUES ('William') ;
GO
INSERT INTO dbo.child_test
(parent_id, name)
VALUES (999, 'Sam') ;
GO
PRINT '> add FK - will fail because there is data that does not conform to the key declaration' ;
ALTER TABLE dbo.child_test
ADD CONSTRAINT [fk_child_parent] FOREIGN KEY (parent_id) REFERENCES dbo.parent_test (id) ;
/*
above fails with error:
Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fk_child_parent ". The conflict occurred in database "test", table "dbo.parent_test", column 'id'.
*/
GO
PRINT '> add FK WITH NOCHECK - will succeed despite bad data' ;
ALTER TABLE dbo.child_test
WITH NOCHECK -- says not to check the data to verify the keys all work
ADD CONSTRAINT [fk_child_parent] FOREIGN KEY (parent_id) REFERENCES dbo.parent_test (id) ;
/* above succeeds */
GO
PRINT '> FK is untrusted' ;
IF EXISTS ( SELECT *
FROM sys.foreign_keys
WHERE name = N'fk_child_parent'
AND is_not_trusted = 1 )
BEGIN
-- 1 row - UNTRUSTED
PRINT '> the constraint is untrusted' ;
END
GO
PRINT '> this state of the system presents the opportunity to research and carefully handle orphaned data.
While in this state the optimizer will not be able to use the new keys to make better decisions however
the keys are in place and will at least prevent new orphans from being created...a slight improvement
over the previous situation where no keys existed at all.' ;
PRINT '> delete orphaned data' ;
DELETE c
FROM dbo.child_test c
LEFT JOIN dbo.parent_test p ON c.parent_id = p.id
WHERE p.id IS NULL ;
GO
PRINT '> enable the CHECK CONSTRAINT' ;
ALTER TABLE dbo.child_test
CHECK CONSTRAINT [fk_child_parent] ;
GO
IF EXISTS ( SELECT *
FROM sys.foreign_keys
WHERE name = N'fk_child_parent'
AND is_not_trusted = 1 )
BEGIN
-- 1 row - STILL UNTRUSTED!!!
PRINT '> the constraint is still untrusted...why? because all we did was enable it' ;
END
GO
PRINT '> Now make SQL Server actually check the data relationship by issuing WITH CHECK' ;
ALTER TABLE dbo.child_test
WITH CHECK -- important
CHECK CONSTRAINT [fk_child_parent] ;
-- success!
GO
IF EXISTS ( SELECT *
FROM sys.foreign_keys
WHERE name = N'fk_child_parent'
AND is_not_trusted = 0 )
BEGIN
-- 0 rows - NOW TRUSTED :-)
PRINT '> The constraint is now trusted. why? because we forced SQL Server to check the data and all data
conformed to the key so it is now trusted and the optimizer
can now use the key to make decisions' ;
END
GO
If you see any flaws or know of any gotchas when employing this type of approach I am all ears.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing post 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply