Question 243479 in PostgreSQL Question posted in
The official documentation can be found here.

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

I’m using PostgreSQL with the following (simplified) DB design: I have a table Auction, each auction can be handled from a Tribunal or a Court of Appeal.

So that I have the table Court of Appeal (primary key = [id] ),

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

And the 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

Please note that these are fake data.

Each court of appeal can group many tribunals, in the sense that a court of appeal is in a higher layer of 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

What I need to do is to reference the Tribunal and Court of Appeal "id" columns inside the "tribunal_or_court_id".

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

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

Does someone have already faced this king of issue? Is any best practice to apply in this situation?

Thank you for your help.

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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. 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';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search