Adding a WHERE clause to a Foreign Key

Question

I have a table in my database (TableA) that has a column (TableA.Column1) that only allows values from certain rows of another table (TableB.Column2). This is very similar to a normal foreign key relation except for the fact that only certain rows from TableB.Column2 are allowed. For example, I may only allow rows from TableB.Column2 where TableB.Column3 > 100;

Is there a way to express this type of referential integrity in a database? I've tried adding a where clause to a foreign key and I've tried adding a subquery to a Check constraint. I've not gotten either to work.

Does anyone have any advice?

Answer 1

The answer may differ depending on the database system you are using. But an option is certainly to use a trigger.

Answer 2

If you only allow "certain" values that are held in a column from Table A, you could create a composite FK in Table B, then add a separate check constraint in your table (Table B)? Though, this sounds like the kind of thing I would enforce in the application tier.

Answer 3

No, I don't think there's any way to do this.

What I typically end up doing in cases like this is create a "dummy" entry (e.g. something like ID = -1, Text = "state or entry unknown") into the table being referenced, and then setting those TableB.Column2 values to this dummy ID value.