How to create CHECK CONSTRAINT based on a query?

  • KoldCoffee

    SSCoach

    Points: 18971

    In pl/sql it's possible to create a CHECK constraints based on a query of values in another table. Is the same posssible in SQL? My searches on line are inconclusive.

  • Kingston Dhasian

    SSCoach

    Points: 19794

    hxkresl (6/30/2011)


    In pl/sql it's possible to create a CHECK constraints based on a query of values in another table. Is the same posssible in SQL? My searches on line are inconclusive.

    Its not possible to create a CHECK constraint on a table( say, table_1 ) based on values in another table( say, table_2 ). You will have to use a trigger to implement the same.


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • KoldCoffee

    SSCoach

    Points: 18971

    Thank you, Kingston.

  • yasirbam

    SSC Veteran

    Points: 208

    Hi

    You can use a UDF function in your check constraint to check the values in another table.

    here is an example;

    assume you have a table called state which have all possible stateIds you use in many tables (so it is not possible to use a foriegn key constraint)

    -- we want to check if the values in the following tables are a subset of the main state table

    CREATE TABLE t1 (stateId INT NOT NULL)

    -- create a function to check the state values and return 1 if stateId is invalid

    CREATE FUNCTION dbo.fnc_IsValidState(@StateId INT, @TypeId Int) RETURNS BIT

    AS

    BEGIN

    DECLARE @flag BIT = 1

    IF NOT EXISTS (SELECT 1 FROM state WHERE stateId =@StateId AND TypeId=@TypeId)

    BEGIN

    SET @flag = 0

    END

    RETURN @flag

    END

    go

    --Add a check constraints to the table to call the function

    ALTER TABLE dbo.[t1] WITH NOCHECK ADD CONSTRAINT [CK_StateCHeck] CHECK ((dbo.fnc_IsValidState(StateId,1)=1))

    -- test by adding some invalid values

    INSERT INTO t1 VALUES(12)

    I personally prefer FK constraints if possible, this code is to show that it is possible to do what you are asking for using a function, but it may not be the best solution..

    hope you find it useful

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply