StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POPrimary Keys are a Foreign Key of two different mutually exclusive tables
text
Body
copied!<p>I'm having a problem on how to design a table that must have a Primary Key which is a foreign key to two mutually exclusive tables.</p> <pre><code>+----------------+ +-----------------+ | PARTY | + PERSON + +----------------+ +-----------------+ | Unique ID (PK) | -|------O|-| Unique ID (FK) | | Party Type | | some details | | | +-----------------+ | | | | +-----------------+ | | | BUSINESS | | | +-----------------+ | | | Scheme Code (PK)| | | -|------O|<| Serial No (PK) | | | | Unique ID (FK) | | | | some details | +----------------+ +-----------------+ +-----------+ +-----------------+ | PERSON | -|-----------O|<| BANK ACCOUNT | +-----------+ +-----------------+ | Account No (PK) | +-----------+ | Unique ID (FK) | | BUSINESS | -|-----------O|<| Scheme Code (FK)| +-----------+ | Serial No (FK) | | some details | +-----------------+ </code></pre> <ol> <li><p>There are 4 entities. A PARTY can be a PERSON or BUSINESS (but cannot be both at the same instance). A PERSON can have one or more BANK ACCOUNT and a BUSINESS can also have one or more BANK ACCOUNT.</p></li> <li><p>For a PERSON, only 1 Unique ID can be assigned.</p></li> <li><p>For a BUSINESS, it can share the same unique PARTY id given it has a unique combination for (Scheme Code, Serial No, and Unique Id)</p></li> <li><p>PARTY's primary key is 'Unique ID'</p></li> <li><p>PERSON's primary key is 'Unique ID'</p></li> <li><p>BUSINESS' primary keys are 'Unique ID', 'Scheme Code', and 'Serial No'</p></li> <li><p>BANK ACCOUNT's primary keys are a combination of all they keys of PERSON and BUSINESS with 'Unique ID' as the only common foreign key and 'Scheme Code'/'Serial No' is a foreign key exclusive only from BUSINESS.</p></li> </ol> <p>I thought the problem would be in the BANK ACCOUNT entity. If the PARTY is a BUSINESS, there will be no problem since all primary/foreign keys are populated (Unique ID, Scheme Code, Serial No). However, if a PARTY is a PERSON, only Unique ID will be populated and the other two keys will have NULL values. And I think NULL primary keys are not allowed.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload