ERROR: there is no unique constraint matching given keys for referenced table "fruit"

Question

CREATE TABLE Gruppelærer(
  brnavn VARCHAR(8),
  emnekode VARCHAR(8),
  år INT,
  vh CHAR(4),
  antallgr INT,
  CONSTRAINT larer_id PRIMARY KEY(brnavn, emnekode, år, vh)
);

CREATE TABLE Søknad(
  brnavn VARCHAR(8),
  emnekode char(4),
  år VARCHAR(8),
  vh INT,
  antallgr INT,
  prioritet INT,
  FOREIGN KEY (brnavn, emnekode, år, vh, antallgr) REFERENCES Gruppelærer(brnavn, emnekode, år, vh, antallgr),
  UNIQUE (brnavn, emnekode, år, vh, prioritet)
);

ERROR:

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

Why does this not work?

Aside from the `vh` column, there's also the issue that your data types are badly mismatched. `fruitid` is an `int` in `InStore` but a `varchar(8)` in the `fruit` table. — Damien_The_Unbeliever, Nov 10 '16 at 08:21
With edit: Well, now you have the opposite problem. You're trying to reference five columns when there's only a constraint defined on 4 of those columns. And, still, you have data type mismatches. — Damien_The_Unbeliever, Nov 10 '16 at 09:14

score 0 · Answer 1 · answered Nov 10 '16 at 08:17

0

either, you need to remove [vh] field from PRIMARY_KEY or add it in the FOREIGN_KEY:

CREATE TABLE fruit(
  fruitname VARCHAR(128),
  fruitid VARCHAR(8) NOT NULL,
  yearplanted INT,
  vh CHAR(4),
  numberoffruits INT,
  CONSTRAINT fruit_id PRIMARY KEY(fruitname, fruitid, yearplanted, vh)
);

CREATE TABLE InStore(
  fruitname VARCHAR(8),
  fruitid INT,
  yearplanted CHAR(4),
  quantity INT,
  FOREIGN KEY (fruitname, fruitid, yearplanted, vh) REFERENCES fruit(fruitname, fruitid, yearplanted, vh),
  UNIQUE (fruitname, fruitid, yearplanted, quantity, vh)
);

answered Nov 10 '16 at 08:17

Tedo G.

1,556
3
17
29

this is the actual table CREATE TABLE Gruppelærer( brnavn VARCHAR(8), emnekode VARCHAR(8), år INT, vh CHAR(4), antallgr INT, CONSTRAINT larer_id PRIMARY KEY(brnavn, emnekode, år, vh) ); CREATE TABLE Søknad( brnavn VARCHAR(8), emnekode char(4), år VARCHAR(8), vh INT, antallgr INT, prioritet INT, FOREIGN KEY (brnavn, emnekode, år, vh, antallgr) REFERENCES Gruppelærer(brnavn, emnekode, år, vh, antallgr), UNIQUE (brnavn, emnekode, år, vh, prioritet) ); Cant get it to work even though I implement the given code into the file. – Dank meme Nov 10 '16 at 08:39
1

@Dankmeme: Post the above code in the question not as a comment – Clodoaldo Neto Nov 10 '16 at 11:40

ERROR: there is no unique constraint matching given keys for referenced table "fruit"

1 Answers1