CODEWITHSUNDEEP
sqldatabasepostgresqlforeign-keys
francxx
francxx

Reputation: 43

SQL - Best practice to refer two different table primary keys with the same foreign key?

I have a table Auction. Each auction can be handled from a Tribunal or a Court of Appeal.

So I have table Court of Appeal (primary key id):

id name
C001 Court of Appeal of Nevada
C002 Court of Appeal of California

and table Tribunal (primary key id):

id name related_court_of_appeal_id
T001 Tribunal of San Francisco C002
T002 Tribunal of Los Angeles C002
T003 Tribunal of Las Vegas C001

Each court of appeal can group many tribunals, in the sense that a court of appeal has higher importance than a tribunal in the law system.

Each auction in the table Auction (primary key tribunal_or_court_id, auction_id) can be handled either from a Tribunal or directly from a Court of Appeal if the auction has some peculiar requisites.

tribunal_or_court_id auction_id description
C001 000 Auction #1
T002 000 Auction #2
T002 001 Auction #3

I need to reference the Tribunal and Court of Appeal id columns inside the tribunal_or_court_id.

I tried to define two foreign keys between Auction.tribunal_or_court_id and Tribunal.id and Court.id respectively. But it did not work since I cannot insert an auction that has a corresponding tribunal id but a not corresponding court id (and vice versa).

I thought about inserting another table populated with all of the ids from Tribunal and Court of Appeal tables, and referring to this new table inside Auction. But that is poor DB design.

What is best practice?

Upvotes: 1

Views: 234

Answers (3)

Stefanov.sm
Stefanov.sm

Reputation: 13049

I would suggest that you add an institution attribute to the auction table, not NULL with values 'T' for tribunal and 'C' for court of appeal (this design does violate 3NF though). Then

SELECT <..your expression list here, maybe using COALESCE..>
FROM auction a
left outer join court_of_appeal coa on a.tribunal_or_court_id = coa.id 
                                   and a.institution = 'C'
left outer join tribunal t on a.tribunal_or_court_id = t.id 
                          and a.institution = 'T';

Upvotes: 0

joseromerobarc
joseromerobarc

Reputation: 56

Take the auction table and left outer join the other two. After joining, use COALESCE to return the first non-null value of either the tribunal or court value. Since you'll only get one value or the other, it doesn't matter which order you display it in.

I'm using the name for this example:

SELECT COALESCE(coa.name, t.name) as tribunal_court_name
FROM auction a
left outer join court_of_appeal coa on a.tribunal_or_court_id = coa.id 
left outer join tribunal t on a.tribunal_or_court_id = t.id;

COALESCE Function

Upvotes: 0

leftjoin
leftjoin

Reputation: 38325

This is not possible: FOREIGN KEY can point to only one table.

So, you can have two columns tribunal_id and court_id, nullable, and two foreign keys for these columns.

Complex Primary key constraint can not be created for nullable columns. You can use UNIQUE constraint instead. And synthetic PK, or no PK at all, only UNIQUE

Upvotes: 2

Related Questions