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, Aug 20 '15 at 03: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, Aug 20 '15 at 03:54
Awesome! Okay next check http://www.postgresql.org/docs/8.3/static/ddl-constraints.html#DDL-CONSTRAINTS-FK ... 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, Aug 20 '15 at 03:55
Yes they should do as RobberID is a primary key in the Accomplices table — Rothmanberger, Aug 20 '15 at 03: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, Aug 20 '15 at 03:59
But if I remove the constraint there will be no relation. Is it not because I have multiple PK's in Accomplices and only one of these if being referenced too? — Rothmanberger, Aug 20 '15 at 04:02
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/87434/discussion-between-user2256772-and-firebladedan). — Rothmanberger, Aug 20 '15 at 04:03
You write: `Does the foreign key in the Robbers table need to match all components that make up the foreign key in the Accomplices table?` Do you actually mean? `Does the foreign key in the Robbers table need to match all components that make up the` **`primary`** `key in the Accomplices table?` — Erwin Brandstetter, Aug 20 '15 at 04:33

score 2 · Answer 1 · edited May 23 '17 at 11:58

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, Aug 20 '15 at 04:51

score 1 · Accepted Answer · answered Aug 20 '15 at 04:49

1

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)
 );

answered Aug 20 '15 at 04:49

FirebladeDan

1,069
6
14

Check our chat for how we came to this answer – Rothmanberger Aug 20 '15 at 04:51
Good talk user22blah l8r – FirebladeDan Aug 20 '15 at 04:52
@user2256772: Nice work. Yes, that design makes more sense. Your table `banks` should probably have a surrogate PK (use a [`serial`](http://stackoverflow.com/a/9875517/939860) column) and just use that as FK in `robberies` and `accomplices`. It's very inefficient to repeat `(BankName, City)` accross multiple tables. – Erwin Brandstetter Aug 20 '15 at 04:59

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

2 Answers2