/*
        This sample shows how UDFs can be used in a CHECK constraint to
        maintain a one-to-one relation between 2 tables. This method can be
        used to implement more complex relationships / checks.

        A simple example of using an UDF in a DEFAULT constraint is also provided
        for completeness.
*/
USE tempdb;
GO
-- Create the parent table & child tables first
CREATE SCHEMA AUTHORIZATION dbo
CREATE TABLE PkTbl ( Col1 int IDENTITY PRIMARY KEY )
CREATE TABLE FkTbl ( Col1 int FOREIGN KEY REFERENCES PkTbl( Col1 ) ,
                     Col2 int )
GO
/*
        Create the UDF that can maintain the one-to-one check. The UDF is created
        with the SCHEMABINDING option to avoid accidental drops. It also creates a 
        hard link between the table & the UDF.
*/
CREATE FUNCTION ChkPrimaryVal (
        @Val int
)
RETURNS INTEGER
WITH SCHEMABINDING
AS
BEGIN
        RETURN( SELECT COUNT( * ) FROM dbo.FkTbl WHERE Col1 = @Val )
END
GO
CREATE FUNCTION RandDefault (
)
RETURNS INTEGER
WITH SCHEMABINDING
AS
BEGIN
        RETURN( @@OPTIONS  % @@SPID )
END
GO

-- Add the constraint that calls the UDF
ALTER TABLE FkTbl ADD 
        CONSTRAINT CK_FkTbl_ChkPrimaryVal CHECK ( dbo.ChkPrimaryVal( Col1 ) = 1 ) ,
        CONSTRAINT DF_FkTbl_RandDefault DEFAULT ( dbo.RandDefault() ) FOR Col2
GO

INSERT INTO PkTbl DEFAULT VALUES;
PRINT 'Adding a new row into FkTbl...';
INSERT INTO FkTbl VALUES ( 1 , DEFAULT );
GO
-- Try to insert a non-existant value
PRINT ''
PRINT 'Adding a new row into FkTbl with non-existant value...';
INSERT INTO FkTbl VALUES ( 11 , DEFAULT );
-- Sample Output shows failure due to FK violation
/*
Adding a new row into FkTbl with non-existant value...
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK__FkTbl__Col1__31EC6D26'. The conflict occurred in database 'tempdb', table 'PkTbl', column 'Col1'.
The statement has been terminated.
*/
GO
-- Try to insert duplicate value
PRINT ''
PRINT 'Adding a duplicate value into FkTbl...';
INSERT INTO FkTbl VALUES ( 1 , DEFAULT );
-- Sample Output shows failure due to CONSTRAINT violation
/*
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN CHECK constraint 'CK_FkTbl_ChkPrimaryVal'. The conflict occurred in database 'tempdb', table 'FkTbl', column 'Col1'.
The statement has been terminated.
*/
GO

SELECT * FROM FkTbl;
-- Sample Output:
/*
Col1 Col2
---- ----
   1   19
*/
GO
-- Cleanup the sample objects
ALTER TABLE FkTbl DROP CONSTRAINT CK_FkTbl_ChkPrimaryVal , DF_FkTbl_RandDefault;
DROP FUNCTION dbo.ChkPrimaryVal;
DROP FUNCTION dbo.RandDefault;
DROP TABLE FkTbl;
DROP TABLE PkTbl;
GO

This page was last updated on May 01, 2006 04:28 PM.