Menu
I'm trying to create a constraint on table A to check if a set of records exists in a table B. I could use a foreign key, but the problem is that the data in the table B is non-unique.
Is there a way to create such a constraint without creating a trigger?
Edit: I can't change table B structure.
986
A foreign key can refer to either a unique or a primary key of the parent table. If the foreign key refers to a non-primary unique key, you must specify the column names of the key explicitly.
Although primary key values must be unique, foreign key values are not required to be unique. For example, a single customer at a bank might have multiple accounts.
The foreign key is an attribute in another table. In the original table ("referenced table"), the foreign key should be unique and non- NULL . In fact, it should almost always be the primary key of that table.
There are three options under "Non-unique Index": 1.Index on all database systems. 2.For selected database systems. 3.No database index.
One technique would be to use a materialised view (fast refresh on commit) to store the unique values of the referenced column, and constrain your table against that.
Attempts at using triggers to enforce integrity are generally doomed due to read consistency or locking issues.
44
Foreign keys are a 1:N relationship. There can only be one parent record at the referenced end of the constraint. That's why we can only build foreign key constraints which reference unique keys.
You appear to be want a constraint which is M:N. This does not fit in a relational model. Perhaps what you need is a intersection table (AB) which links many records in table A with many records in table B? In fact, there may be several different modelling solutions, depending on your actual requirements.
Triggers won't work, partly because they won't scale but mainly because they won't work in a multi-user environment.
132
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
