No unique constraint matching given keys for referenced table with multiple primary keys

Question

I am trying to link these two tables but am receiving the error:

There is no unique constraint matching given keys for referenced table "accomplices".

Note Robberies is another table.
I used this to create the Accomplices table (This is when the error occurs):

CREATE TABLE info.Accomplices ( RobberID INTEGER, BankName VARCHAR, City VARCHAR, RobberyDate DATE, Share DECIMAL NOT NULL, PRIMARY KEY(RobberID, BankName, City, RobberyDate), FOREIGN KEY(BankName, City, RobberyDate) REFERENCES info.Robberies(BankName, City, RobberyDate) );

And this to create the Robbers table:

CREATE TABLE info.Robbers ( RobberID INTEGER, Nickname VARCHAR, Age INTEGER, NoYears INTEGER, PRIMARY KEY(RobberID), FOREIGN KEY(RobberID) REFERENCES info.Accomplices(RobberID), CONSTRAINT AgeCheck CHECK (Age > NoYears) );

Does the foreign key in the Robbers table need to match all components that make up the primary key in the Accomplices table?

Make sure you're creating your tables with no references first. If all have references you need to create one without the references and then add. Sometimes this issue is associated with a missing table — FirebladeDan
– FirebladeDan, Commented Aug 20, 2015 at 3:51
Hi I have been creating my tables in an order such that none are created with foreign keys unless they have a parent table created already so I don't think this is the problem — Rothmanberger
– Rothmanberger, Commented Aug 20, 2015 at 3:54
Awesome! Okay next check postgresql.org/docs/8.3/static/… ... Your accomplices table contains all RobberID's correct? What i'm saying is if you provide one in the robbers table and it doesn't exist in accomplices that is illegal — FirebladeDan
– FirebladeDan, Commented Aug 20, 2015 at 3:55
Yes they should do as RobberID is a primary key in the Accomplices table — Rothmanberger
– Rothmanberger, Commented Aug 20, 2015 at 3:57
Understood but watch this. Remove your foreign key constraint from Robbers. Should work. And this is because Robbers ID might be trying to use an ID not in Accomplices — FirebladeDan
– FirebladeDan, Commented Aug 20, 2015 at 3:59

Community · Accepted Answer · 2017-05-23 11:58:22Z

Does the foreign key in the Robbers table need to match all components that make up the primary key in the Accomplices table?

Not exactly. It does not have to be the PK. A FK constraint requires any UNIQUE or PRIMARY KEY constraint on the (set of) column(s) in the referenced table. If it's not unique it cannot be referenced by FK. Theoretically you could add a UNIQUE constraint to accomplices:

CREATE TABLE info.Accomplices ( robberid integer, bankname varchar, city varchar, robberydate date, share decimal not null, PRIMARY KEY(robberid, bankname, city, robberydate), UNIQUE(robberid), FOREIGN KEY ... );

.. which makes remarkably little sense from a design perspective, but goes to show the requirements for the given FK constraint in robbers.

I suspect there is a logical problem with your database design.

Yeah we talked about this for an hour. The design is wrong but it's hw so probably a bad question. He got it tho woohoo

FirebladeDan · Accepted Answer · 2015-08-20 04:49:04Z

CREATE TABLE info.Robberies ( BankName VARCHAR, City VARCHAR, RobberyDate DATE, Amount DECIMAL NOT NULL, PRIMARY KEY(BankName, City, RobberyDate), FOREIGN KEY(BankName, City) REFERENCES info.Banks(BankName, City) ); CREATE TABLE info.Robbers ( RobberID INTEGER, Nickname VARCHAR, Age INTEGER, NoYears INTEGER, PRIMARY KEY(RobberID), --FOREIGN KEY(RobberID) REFERENCES info.Accomplices(RobberID), CONSTRAINT AgeCheck CHECK (Age > NoYears) ); CREATE TABLE info.Accomplices ( RobberID INTEGER, BankName VARCHAR, City VARCHAR, RobberyDate DATE, Share DECIMAL NOT NULL, PRIMARY KEY(RobberID, BankName, City, RobberyDate), FOREIGN KEY(RobberID) references info.Robbers(RobberID), FOREIGN KEY(BankName, City, RobberyDate) REFERENCES info.Robberies(BankName, City, RobberyDate) );

@user2256772: Nice work. Yes, that design makes more sense. Your table banks should probably have a surrogate PK (use a serial column) and just use that as FK in robberies and accomplices. It's very inefficient to repeat (BankName, City) accross multiple tables.

Collectives™ on Stack Overflow

No unique constraint matching given keys for referenced table with multiple primary keys

2 Answers 2

1 Comment

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Linked

Related