CODEWITHSUNDEEP
sql-serverdatabaseconstraints
user3910554
user3910554

Reputation: 11

Constraint on a column based on values at another column in another table

I have table A which has two columns policy and rule. Table values are as below:

policy | rule
-------------
1  | A
-------------
1  | B
-------------
2  | C
-------------
3  | D 
-------------

I have another table B with a column (id).

I want to write a constraint on this table B at column (id) such that if a number of distinct policies in table A are equal to 1, then the value of (id) should always be NULL.

I am using SQL Server and I am looking at a query-based solution (not via GUI steps).

Upvotes: 1

Views: 3788

Answers (1)

Maurício Pontalti Neri
Maurício Pontalti Neri

Reputation: 1080

Add CHECK Constraints into the table

create table tableA (policy int , [rule] char(1)) 
create table tableB (Id int , policy int ) 
GO

Function to check the constraint 

CREATE FUNCTION CheckFnctn(@policy int) -- Adapt if necessary
RETURNS bit
AS 
BEGIN
DECLARE @retval bit = 1
IF EXISTS(SELECT TOP 1 1 FROM tableA WHERE policy = @policy and policy = 1)
BEGIN
    RETURN 0
END
RETURN @retval

END;
GO

Add the constraint into the table

ALTER TABLE tableB ADD CONSTRAINT ck_id_policy_1 CHECK (dbo.CheckFnctn(policy) = 1)

Test

    INSERT tableA Values
     (1,'A')
    ,(1,'B')
    ,(2,'B')
    ,(2,'D')
GO
    (4 row(s) affected)


INSERT tableB (Id, policy) values (1,2)
GO
(1 row(s) affected)


INSERT tableB (Id, policy) values (1,1)
GO
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the CHECK constraint "ck_id_policy_1". The conflict occurred in database "XXXX", table "dbo.tableB", column 'policy'.
The statement has been terminated.

If you need to check if policy 1 exists you can never insert an Id different from null. Use this function and constraint

CREATE FUNCTION CheckFnctn(@Id int)
RETURNS bit
AS 
BEGIN
DECLARE @retval bit = 1
IF EXISTS(SELECT TOP 1 1 FROM tableA WHERE  policy = 1 AND @Id IS NOT NULL)
BEGIN
    RETURN 0
END
RETURN @retval

END;
GO



ALTER TABLE tableB ADD CONSTRAINT ck_id_policy_1 CHECK (dbo.CheckFnctn(Id) = 1) 

INSERT tableB (Id, policy) values (1,2)
GO
Msg 547, Level 16, State 0, Line 3
The INSERT statement conflicted with the CHECK constraint "ck_id_policy_1". The conflict occurred in database "XXXXX", table "dbo.tableB", column 'Id'.
The statement has been terminated.

INSERT tableB (Id, policy) values (NULL,1)
GO
(1 row(s) affected)

Upvotes: 5

Related Questions