Foreign key on a non-unique index? (oracle)

Question

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.

It is not SQL standard... foreign key must point to an unique record, by referecing the Primary Key or a Unique Key on the parent table... — Andrea Ligios, Nov 05 '12 at 13:28
It looks like you're missing a table in your model (the table whose primary key would be this column) — Vincent Malgrat, Nov 05 '12 at 13:30

score 6 · Answer 1 · edited Oct 27 '20 at 13:07

6

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.

edited Oct 27 '20 at 13:07

gouessej

3,640
3
33
67

answered Nov 05 '12 at 13:29

APC

144,005
19
170
281

Thank you for you reply. I added a comment explaning that I can't change table B structure. – André Wagner Nov 05 '12 at 14:57
_”This does not fit in a relational model”_ - How so? Codd’s seminal paper alludes to the possibility of an RDBMS supporting arbitrary database-scoped constraints - it just wouldn’t be a _key_-constraint, but I believe it fits in the mathematical relational model just fine. – Dai Oct 15 '22 at 00:40

score 5 · Accepted Answer · answered Nov 05 '12 at 13:30

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.

score 0 · Answer 3 · edited May 23 '17 at 11:59

I'm pretty sure the only way to enforce such a relationship is with a trigger.

As you mention, the data in table B is non-unique, so a foreign key will not work. (See also Can a foreign key reference a non-unique index?)

Check constraints come to mind, but they will not work here because:

must not reference other tables
cannot contain subqueries.

That being said, it may be possible that the reason the data in table B is not unique is that it is not normalized. It may be worth reviewing your schema to see if you can extract a unique relationship between A and B, possibly with the use of an intermediate table.

Foreign key on a non-unique index? (oracle)

3 Answers3