0

I get this error:

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

For this schema:

CREATE TABLE Person (
    svnr VARCHAR(40) PRIMARY KEY,
    anschrift VARCHAR(40) NOT NULL,
    name VARCHAR(20) NOT NULL
);

BEGIN;

CREATE TABLE Mitarbeiter (
    svnr VARCHAR(40) PRIMARY KEY REFERENCES Person(svnr),
    beschaeftigt_seit DATE NOT NULL,
    gehalt NUMERIC(5,2),
    CHECK(gehalt > 0),
    abteilung INTEGER NOT NULL,
    krankenhaus INTEGER NOT NULL
);

CREATE TABLE Krankenhaus (
    kid INTEGER PRIMARY KEY DEFAULT nextval('seq_krankenhaus'),
    anschrift VARCHAR(40) NOT NULL,
    name VARCHAR(20) NOT NULL,
    geleitet_von VARCHAR(40) REFERENCES Mitarbeiter(svnr) DEFERRABLE INITIALLY DEFERRED
);

CREATE TABLE Abteilung (
    abid INTEGER DEFAULT nextval('seq_abteilung'),
    name VARCHAR(40) NOT NULL,
    anschrift VARCHAR(40) NOT NULL,
    koordiniert VARCHAR(40) REFERENCES Mitarbeiter(svnr) DEFERRABLE INITIALLY DEFERRED,
    krankenhaus INTEGER REFERENCES Krankenhaus(kid),
    PRIMARY KEY (abid, krankenhaus)
);

ALTER TABLE Mitarbeiter ADD CONSTRAINT fk_abteilung FOREIGN KEY (abteilung, krankenhaus) REFERENCES Abteilung(abid, krankenhaus) DEFERRABLE INITIALLY DEFERRED;

COMMIT;

What I am doing wrong?

After adding a column krankenhaus to the table mitarbeiter and changing the alter table command but I still get the same error ...

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
prodi22
  • 1
  • 1
  • The error message contradicts your code after you applied @a_horse's advice. It should work. You did pick the inferior option, though. You'd better remove the redundant `mitarbeiter.krankenhaus` and go with just `abid` for PK and FK. – Erwin Brandstetter Oct 28 '14 at 01:47

2 Answers2

3

The primary key of the table Abteilung is (abid, krankenhaus). As a foreign references references exactly one row, you also need to use both columns in the foreign key constraint definition.

But as you don't have a column krankenhaus in your table Mitarbeiter you can't reference Abteilung.

You either need to reduce the primary key of Abteilungto (abid) (which would be possible as it is a generated, artificial key anyway) or add a column Krankenhaus to your Mitarbeiter table.

0

This should work:

CREATE TABLE person (
   svnr text PRIMARY KEY
 , anschrift text NOT NULL
 , name text NOT NULL
);

CREATE TABLE mitarbeiter (
   svnr text PRIMARY KEY REFERENCES person
 , beschaeftigt_seit date NOT NULL
 , gehalt numeric(5,2)
 , abid int NOT NULL
 , CHECK(gehalt > 0)
);

CREATE TABLE krankenhaus (
   kid serial PRIMARY KEY
 , anschrift text NOT NULL
 , name text NOT NULL
 , geleitet_von text REFERENCES mitarbeiter(svnr) DEFERRABLE INITIALLY DEFERRED
);

CREATE TABLE abteilung (
   abid serial PRIMARY KEY
 , kid INTEGER REFERENCES krankenhaus(kid)
 , koordiniert text REFERENCES mitarbeiter(svnr) DEFERRABLE INITIALLY DEFERRED
 , name text NOT NULL
 , anschrift text NOT NULL
);

ALTER TABLE mitarbeiter
   ADD CONSTRAINT fk_abteilung FOREIGN KEY (abid)
   REFERENCES abteilung(abid) DEFERRABLE INITIALLY DEFERRED;

Use a serial column for surrogate PKs:

Don't use varchar(n) with arbitrary limitations for string length, unless you really need to enforce a maximum length and its not going to change. See:

Go with just abid as primary key for abteilung. There would be a related application for a redundant column included in a FK constraint, but that's not what your model does right now:

No need to use an explicit transaction (BEGIN ... COMMIT) for part of the code.

The real difficulty with these circular references is entering and changing data. You probably made your FK constraints DEFERRABLE INITIALLY DEFERRED - which is going to make some queries considerably slower.
You could go with just plain default IMMEDIATE FK constraints, if you wrap INSERT / UPDATE on related rows into data-modifying CTEs. See:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228