Is it possible to create a check constraint for a table that references a link? I have a 1:M binary link between two tables and would like a field in the child table to be unique within the parent record (it does not need to be unique within the table).
If I were using foreign keys, I would create a check constraint on the concatenation of the parent table key and the child table field.
I've attached a sample database with sample data.
I would like to create a check that ensures that the name field in the Rooms table is unique within the record in the Houses table to which it is linked.
Can this be done with a check constraint or do I need to check for this programmatically?