Check constraint with multiple conditions and other table column referring

Question

I have two table called tbl_1 and tbl_2 with below schema

CREATE TABLE tbl_1(id int, disabled bit, qtype int)

and

CREATE TABLE tbl_2 (qtype int, qname nvarchar(MAX))

i want to add a constraint to the tbl_1 so that if (disabled=0 or disabled is null) qtype must be a number that exists in the tbl_2.qtype column?

I tried creating a function and added a check constraint

CREATE FUNCTION fn_Check_qtype(@qtype INT)
RETURNS int 
AS
BEGIN
IF EXISTS(SELECT qtype from tbl_1 where (disabled=0 or disabled is null) and qtype=@qtype)
IF EXISTS (SELECT qtype FROM tbl_2 WHERE qtype is not null and qtype = @qtype)
      return 1
  return 0
END

Constraint

alter table tbl_1
add constraint ck_qtyppe
check (dbo.fn_Check_qtype(qtype) =1)

But even no non-matching records it is throwing error

The ALTER TABLE statement conflicted with the CHECK constraint "ck_qtyppe". The conflict occurred in database "TestDB", table "dbo.tbl_1", column 'qtype'.

But if i am deleting qtype null value from tbl_1 it is working no matter disabled=0, disabled=, disabled = null.

The first `if exists` statement doesn't make sense as it runs on the whole table. Why not a foreign key constraint? — Charlieface, Mar 18 '21 at 12:29
A constraint is only check when a row in **specific table** is inserted or modified. In a relationship like this, you would need to checking in both directions, correct? Think long and hard about the path you have chosen. And lazy coding / design will not help you. I can think of no good reason why `disabled` should be nullable - can you? Either something is or is not - there should not be a "don't know but treat it like xxx" interpretation (which might change based on context). — SMor, Mar 18 '21 at 13:06
You may find https://stackoverflow.com/questions/65825778/adding-constraints-that-check-a-separate-linked-table-for-a-value/65827550#65827550 interesting, it has a much better solution to multi-table constraints — Charlieface, Mar 18 '21 at 14:23

score 1 · Answer 1 · answered Mar 18 '21 at 12:30

1

First, a foreign key references should be to a primary key, so let me assume tbl_2 is defined as:

CREATE TABLE tbl_2 (
    qtype int primary key,
    qname nvarchar(MAX)
);

Then, you can do this without a user-defined function. All you need is a persisted computed column:

CREATE TABLE tbl_1 (
    id int, 
    disabled bit,
    qtype int,
    qtype_enabled as (case when disabled = 1 then qtype end) persisted,
    foreign key (qtype_enabled) references tbl_2 (qtype)
);

Here is a db<>fiddle.

answered Mar 18 '21 at 12:30

Gordon Linoff

1,242,037
58
646
786

Thanks Gordon! Suppose we have two column instead of one CREATE TABLE tbl_1(id int, disabled bit, isdaily bit, qtype int) and i want to add a constraint to the tbl_1 so that if isdaily = 1 and (disabled=0 or disabled is null) qtype must be a number that exists in the tbl_2.qtype column. What could be the solution in that case? – Kishor Kumar Mar 18 '21 at 12:54
A FK can be associated with either a unique constraint or primary constraint - though the former is rarely used. – SMor Mar 18 '21 at 13:02
@KishorKumar . . . This answers the question asked here. A new question should be asked as a question not as a comment. – Gordon Linoff Mar 18 '21 at 13:41

Check constraint with multiple conditions and other table column referring

1 Answers1