June 30, 2011 at 11:53 pm
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.
July 1, 2011 at 12:54 am
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.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 1, 2011 at 1:12 am
Thank you, Kingston.
July 1, 2011 at 9:12 am
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply