Can I create a table with check constrain whose values are dependent on sql query

Question

Is it possible to create a table which has a check constraint on one of the column whose value lies within the result set given by another sql query

eg.

create table tablename
(
name varchar(10),
rollno int
)check rollno in (select rollno from anotherDatabase.TableName,candidateRoll)

or any thing like that.

I dont have to use it anywhere but still want to know.

Answer 1

Yes: foreign key for same database links

create table tablename
(
name varchar(10),
rollno int FOREIGN KEY (candidateRoll) REFERENCES OtherTableName (candidateRoll)
)

If it's a different database then use code e.g. insert via stored proc or enforce via a trigger

Answer 2

If you can't achieve what you want with a foreign key reference, so you can if you wrap the SELECT statement in a function call.

Your check constraint expression may look something like:

(dbo.SomeFunction([col1]) != 0)

The function might look like this (assuming the column is a varchar):

create function dbo.SomeFunction(@arg varchar(max))
returns bit
as
begin
return
(
    select count(*) from SomeOthertable where col2 = @arg
)
end

EDIT (2010/06/9): Regarding Anthony's comment, my testing has shown that a count(*) value of greater than 1 is still returned as 1. So it would seem that the function is okay, even though it should probably explicitly return 1 or 0. Or, if you are interested in the actual rowcount, change the return type from BIT to INT.