Search code examples
sql-serverrestrictcheck-constraints

SQL Server - CHECK constraint on a column where values come from another table

How does one put a CHECK constraint on a column such that its range of acceptable values come from another table, without hardcoding?

Here's a simplified example:

OneManyTable
RoleID  TaskID
10      Val1
10      Val2
20      Val1
20      Val2


MetaDataTable
pkID    Class   Value
1       A       Val1
2       A       Val2
3       B       Val3
4       B       Val4

I want to put a CHECK Constraint on OneManyTable.TaskID column such that acceptable values come from another tables's column, i.e. from MetadataTable.Value where MetadataTable.class= 'A'

I already tried creating a CHECK constraint of the format

TaskID in (Select Value FROM MetadataTable where class= 'A')

BUT THIS IS NOT SUPPORTED.

On the other hand TaskID in ('Val1', 'Val2') works as a check constraint in SQL2k8 (not in SQL2000 !), but its not acceptable due to hardcoding.

How to achieve what i want, whether via CHECK constraint or some other fancy mechanism that i am not aware of?

PS. Has to be on the database side, no client-side checking as has been suggested to me by someone.

Solution

  • It's probably not a good practice to get into, but you can write a user-defined function which accepts your TaskID as a parameter and have it evaluate to true or false depending upon whether or not the TaskID falls within the range provided in your MetaDataTable.

    That'll allow you to get the functionality you're looking for - CHECK constraints are really just meant to be simple functions designed to limit the range of a column and their behavior was designed with that in mind, so that's why you can't write subqueries within a check constraint in SQL server.

    You can however write a SELECT statement within a user defined function and call it from a CHECK constraint.