Constraint - sql server

Question

I want to add a constraint on a column that depends on a column from a different table. For example, if in table T1 I have a string column str1, and on table T2 I have a string clumn str2, I want str1 to be null except for when srt2 - "ok".

Answer 1

you could use a trigger script (before execution) on t1 that checks when a string shall be inserted into str1, if str2 equals "ok"

Answer 2

You can't use SELECT in a constraint, but a user defined function is allowed. So you can create a function to retrieve str2 from T2. Here's an example setup:

create table t1 (id int, str1 varchar(max))
create table t2 (str2 varchar(10))
go
create function dbo.GetStr2() returns varchar(10)
as
begin
    declare @retval varchar(10)
    select @retval = str2 from t2
    return @retval
end
go
alter table t1 add constraint CheckStr2Constraint
    check (str1 is null or dbo.GetStr2() = 'ok');

You can test this like:

insert into t2 values ('ok')
insert into t1 values (1,'test') -- Succeeds
insert into t1 values (1,null) -- Succeeds
update t2 set str2 = 'not ok'
insert into t1 values (1,'test') -- Fails
insert into t1 values (1,null) -- Succeeds

The third INSERT will fail, because str2 is 'not ok', and you're inserting a non-null string. The error message will look like:

The INSERT statement conflicted with the CHECK 
constraint "CheckStr2Constraint".