Binaryworld - UDF in CHECK & DEFAULT CONSTRAINTS. ... [ SQL Server -> User Defined Function ]

User-defined functions in SQL2000 provide a powerful way to reuse code / logic. This example shows how you can use UDFs in CHECK & DEFAULT constraints of a table.

Click here to copy the following block

/*
    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