I have three tables a,b and c and need to add a constraint like below for checking the data integrity
The below is wrong but I need help in enforcing the below condition.
ALTER TABLE [a]
ADD CONSTRAINT UOMGROUPIG CHECK UNITOFMEASURID IN (SELECT UnitOfMeasureId FROM b WHERE UOMGroupId=1 )
ALTER TABLE [c]
ADD CONSTRAINT UOMGROUPIG CHECK UNITOFMEASURID IN (SELECT UnitOfMeasureId FROM b WHERE UOMGroupId=2 )
Thanks
A scalar valued function like this works for your example, you can easily modify it and create your second constraint:
CREATE FUNCTION your_schema_name.udf_Check1(
@UNITOFMEASURID INT
)
RETURNS BIT
AS
BEGIN
DECLARE @returnValue BIT = 0
SELECT @returnValue = CASE WHEN COUNT(UnitOfMeasureId) > 0 THEN 1 ELSE 0 END
FROM your_schema_name.b
WHERE UOMGroupId=1
AND @UNITOFMEASURID = UnitOfMeasureId
RETURN @returnValue
END
GO
ALTER TABLE [a]
ADD CONSTRAINT UOMGROUPIG
CHECK (your_schema_name.udf_Check1(UNITOFMEASURID) = 1)
GO
Here is the example: SQL Fiddle
a.UNITOFMEASURID to be already present in b.UnitOfMeasureId and b.UOMGroupId is required to be 1. For instance UNITOFMEASURID with value 111 can only be inserted into a if there is a row in b where b.UnitOfMeasureId=111 and b.UOMGROUPIG=1, was that right?b and some rows that should and shouldn't be allowed to be inserted into a).You cannot write query within Check constraint. Instead you can call user defined function.
You can use same function in both constraints by passing @UOMGroupId value along with UNITOFMEASURID.
CREATE FUNCTION CheckFnctn(@UNITOFMEASURID int, @UOMGroupId int)
RETURNS int
AS
BEGIN
if exists(SELECT UnitOfMeasureId FROM b WHERE UOMGroupId=@UOMGroupId
and UnitOfMeasureId = @UNITOFMEASURID)
BEGIN
RETURN 1;
END
RETURN 0; --missed this line
END;
ALTER TABLE [a]
ADD CONSTRAINT UOMGROUPIG CHECK (dbo.CheckFnctn(UNITOFMEASURID, 1)=1);
ALTER TABLE [c]
ADD CONSTRAINT UOMGROUPIG CHECK (dbo.CheckFnctn(UNITOFMEASURID, 2)=1);
RETURN 0 if record is not found.
WHERE UOMGroupId=1condition, I believe this eliminates the use of a foreign key.