CODEWITHSUNDEEP
sqlpostgresqlforeign-keysdatabase-schemaddl
Perelan
Perelan

Reputation: 406

ERROR: No unique constraint matching given keys for referenced table

For some reason I'm getting an error* in my code. I'm quite new to PostgreSQL, and simply SQL. What is causing this error?

*there is no unique constraint matching given keys for referenced table "tech".

BEGIN;

CREATE TABLE Person (
    person_id   SERIAL PRIMARY KEY,
    firstname   VARCHAR(128),
    lastname    VARCHAR(128),
    email_adr   VARCHAR(128),

    UNIQUE(person_id, email_adr)
);

CREATE TABLE Phone (
    person_id   INT REFERENCES Person(person_id),
    phone_nr    INT PRIMARY KEY,

    UNIQUE(phone_nr)
);


CREATE TABLE Tech (
    tech_id INT REFERENCES Person(person_id),
    username    VARCHAR(80) PRIMARY KEY,
    password    VARCHAR(80) NOT NULL,
    location    Varchar(128),

    UNIQUE(username, tech_id)
);

CREATE TABLE Customer (
    customer_id    INT REFERENCES Persons(person_id),
    addresse       VARCHAR(255) NOT NULL,

    UNIQUE(customer_id)
);

CREATE TABLE Task (
    task_id     SERIAL PRIMARY KEY,
    payment     MONEY,
    tech        INT REFERENCES Tech(tech_id)         NOT NULL,
    customer    INT REFERENCES Customer(customer_id) NOT NULL,
    start_date  DATE                                 NOT NULL,
    end_dato    DATE,

    UNIQUE(tech, customer, start_date, end_date)
);

COMMIT;

Upvotes: 2

Views: 4163

Answers (2)

Perelan
Perelan

Reputation: 406

What do you think of this code?

BEGIN;

CREATE TABLE Person (
    person_id   SERIAL PRIMARY KEY,
    firstname   VARCHAR(128),
    lastname    VARCHAR(128),
    email_adr   VARCHAR(128),

    UNIQUE(person_id),
    UNIQUE(email_adr)
);

CREATE TABLE Phone (
    person_id   INT,
    phone_nr    INT PRIMARY KEY,
);


CREATE TABLE Tech (
    tech_id INT,
    username    VARCHAR(80) PRIMARY KEY,
    password    VARCHAR(80) NOT NULL,
    location    Varchar(128),

    FOREIGN KEY(tech_id) REFERENCES Person(person_id),
    UNIQUE(username),
    UNIQUE(tech_id)
);

CREATE TABLE Customer (
    customer_id    INT REFERENCES Persons(person_id),
    addresse       VARCHAR(255) NOT NULL,

    FOREIGN KEY(tech_id) REFERENCES Person(person_id),
    UNIQUE(customer_id)
);

CREATE TABLE Task (
    task_id     SERIAL PRIMARY KEY,
    payment     MONEY,
    tech        varchar(80) REFERENCES Tech(username)       NOT NULL,
    customer    INT        REFERENCES Customer(customer_id) NOT NULL,
    start_date  DATE                                 NOT NULL,
    end_dato    DATE,

    UNIQUE(tech, customer, start_date, end_date)
);

COMMIT;

Upvotes: 0

Gabriel's Messanger
Gabriel's Messanger

Reputation: 3298

In table Task you trying to reference to table Tech by tech_id. To do that you must add UNIQUE CONSTRAINT to tech_id in Tech. Right now in table Tech you have UNIQUE(username, tech_id) that means that values in column tech_id could by doubled Ex.

Tech
-------------------------------
tech_id     username,    ....
------------------------------
1           'John'
2           'Tony'
1           'Nataly'

Acctually the better idea is to set reference by PRIMARY KEY, so in your case username in table Tech.

If you want to leave structure the way present in question, you should just add UNIQUE(tech_id) in column Tech.

Upvotes: 2

Related Questions