How to create CHECK CONSTRAINT based on a query?

• KoldCoffee

  SSCoach

  Points: 18971

  More actions

  June 30, 2011 at 11:53 pm

  #243096

  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: 19799

  More actions

  July 1, 2011 at 12:54 am

  #1346886

  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

  More actions

  July 1, 2011 at 1:12 am

  #1346887

  Thank you, Kingston.

• yasirbam

  SSC Veteran

  Points: 208

  More actions

  July 1, 2011 at 9:12 am

  #1347089

  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