CODEWITHSUNDEEP
sqlpostgresqldatabase-designforeign-keys
Rothmanberger
Rothmanberger

Reputation: 175

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

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?

Upvotes: 0

Views: 1573

Answers (2)

FirebladeDan
FirebladeDan

Reputation: 1069

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

Upvotes: 1

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656794

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.

Related:

Upvotes: 2

Related Questions