CODEWITHSUNDEEP
sqloracle11g
stg
stg

Reputation: 2797

How to achieve that exactly one of two foreign keys is NULL and the other one is not

In my datatable I have a table B which entries either belong to a Table A1 or a table A2. Each A1 has exactly one B and so does A2. Up to now my model looks like this:

TABLE A1
ID ... PK
B  ... FK

TABLE A2
ID ... PK
B  ... FK

TABLE B
ID ... PK
A1 ... FK
A2 ... FK

But now a B could belong to an A1 and an A2, what I want to avoid. So in table B exactly one of the two foreign keys should ne NULL and the other one not. Is it possible to realize this in SQL?

I am using Oracle DB 11g.

Upvotes: 0

Views: 63

Answers (2)

Justin Cave
Justin Cave

Reputation: 231741

It sounds like you're just missing a CHECK constraint on B

ALTER TABLE b
  ADD CONSTRAINT chk_one_a 
           CHECK(    (a1 IS NULL or a2 IS NULL)         -- 1 is NULL
                 and (a1 IS NOT NULL or a2 IS NOT NULL) -- 1 is MOT NULL 
                );

Now, from a data model standpoint, it's a bit odd that you have relationships both from B to A1 and A2 and from A1 and A2 to B. That sort of cycle generally isn't ideal. It seems like you'd want to eliminate one of the two sets of relationships.

Upvotes: 2

MarkD
MarkD

Reputation: 1063

As both A tables already have a B.ID as a foreign key could you remove the A.ID's from the B table and force a check constraint on both A tables to ensure the B.ID isn't already in use by either table

This would allow for the 1 to 1 relationships you require and ensure that a B.ID in one table could not be used by the other

Upvotes: 0

Related Questions