What you are asking for is a constraint across 2 tables or a table-level constraint with a subquery that involves another table (and that is a limitation of SQL-Server table-level constraints). Check the CREATE TABLE syntax, paragraph Check Constraints:

A column-level CHECK constraint can reference only the constrained column, and a table-level CHECK constraint can reference only columns in the same table.

In SQL-92 standard, there is ASSERTION (a constraint across more than 1 table), which is actually what you would use if it was available. See the asnwers in this question: Why don't DBMS's support ASSERTION for details and for info about some products (MS-Access) that have such functionality with limitations.

Firebird documentation says it allows subqueries in CHECK constraints.

In SQL-Server, your only solution would be a trigger, I'm afraid.

What you are asking for is a constraint across 2 tables or a table-level constraint with a subquery that involves another table (and that is a limitation of SQL-Server table-level constraints). Check the CREATE TABLE syntax, paragraph Check Constraints:

A column-level CHECK constraint can reference only the constrained column, and a table-level CHECK constraint can reference only columns in the same table.

In SQL-92 standard, there is ASSERTION (a constraint across more than 1 table), which is actually what you would use if it was available. See the asnwers in this question: Why don't DBMS's support ASSERTION for details and for info about some products (MS-Access) that have such functionality with limitations.

Firebird documentation says it allows subqueries in CHECK constraints.

In SQL-Server, your only solution would be a trigger, I'm afraid.

What you are asking for is a constraint across 2 tables or a table-level constraint with a subquery that involves another table (and that is a limitation of SQL-Server table-level constraints).

In SQL-92 standard, there is ASSERTION (a constraint across more than 1 table), which is actually what you would use if it was available. See the asnwers in this question: Why don't DBMS's support ASSERTION for details and for info about some products (MS-Access) that have such functionality with limitations.

Firebird documentation says it allows subqueries in CHECK constraints.

In SQL-Server, your only solution would be a trigger, I'm afraid.

What you are asking for is a constraint across 2 tables or a table-level constraint with a subquery that involves another table (and that is a limitation of SQL-Server table-level constraints). Check the CREATE TABLE syntax, paragraph Check Constraints:

A column-level CHECK constraint can reference only the constrained column, and a table-level CHECK constraint can reference only columns in the same table.

In SQL-92 standard, there is ASSERTION (a constraint across more than 1 table), which is actually what you would use if it was available. See the asnwers in this question: Why don't DBMS's support ASSERTION for details and for info about some products (MS-Access) that have such functionality with limitations.

Firebird documentation says it allows subqueries in CHECK constraints.

In SQL-Server, your only solution would be a trigger, I'm afraid.