Adding a WHERE Clause to a Foreign Key

Question

I have a table in my database (TableA) that has a column (TableA.Column1) that allows only values ​​from certain rows of another table (TableB.Column2). This is very similar to the normal foreign key relationship, except that only certain rows from TableB.Column2 are allowed. For example, I can 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 tried to add the where clause to the foreign key, and I tried to add a subquery to the validation constraint. I do not work.

Does anyone have any tips?

Answer 1

The answer may vary depending on the database system used. But the option, of course, should use a trigger.

Answer 2

If you allow only certain values ​​that are stored in a column from table A, can you create a composite FK in table B and then add a separate control constraint to the table (table B)? Although, this is similar to what I will apply at the application level.

Answer 3

No, I don’t think there is a way to do this.

What I usually do in such cases is to create a “dummy” record (for example, something like ID = -1, Text = "state or entry unknown" ) in the referenced table, and then set these values TableB.Column2 value of this dummy identifier.

Answer 4

A trigger seems like the best answer from a database perspective ... although depending on your setup this condition may be easier to implement at the application level. What inserts rows into this table and is it possible to enforce this restriction on what the insert does, and not directly directly in the database? There are several risks to doing this this way, but it may be easier depending on your installation.