Question 291578 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – In PostgresSQL, I am receiving an error message of ERROR: there is no unique constraint matching given keys for referenced table "Employees"

I am receiving this error message.

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

This is what I have run:

-- Exported from QuickDBD: https://www.quickdatabasediagrams.com/
-- Link to schema: https://app.quickdatabasediagrams.com/#/d/Sa6Ijr
-- NOTE! If you have used non-SQL datatypes in your design, you will have to change these here.

-- Modify this code to update the DB schema diagram.
-- To reset the sample schema, replace everything with
-- two dots ('..' - without quotes).
-- Physical

CREATE TABLE "Departments" 
(
    "dept_no" VARCHAR(255)   NOT NULL,
    "dept_name" VARCHAR(255)   NOT NULL,
    CONSTRAINT "pk_Departments" PRIMARY KEY ("dept_no")
);

CREATE TABLE "Dept_Emp" 
(
    "emp_no" INT   NOT NULL,
    "dept_no" VARCHAR(255)   NOT NULL,
    CONSTRAINT "pk_Dept_Emp" PRIMARY KEY ("emp_no")
);

CREATE TABLE "Dept_Manager" 
(
    "dept_no" VARCHAR(255)   NOT NULL,
    "employee_no" INT   NOT NULL,
    CONSTRAINT "pk_Dept_Manager" PRIMARY KEY ("dept_no")
);

CREATE TABLE "Employees" 
(
    "emp_no" INT NOT NULL,
    "emp_title_id" VARCHAR(255) NOT NULL,
    "birth_date" VARCHAR(255) NOT NULL,
    "first_name" VARCHAR(255) NOT NULL,
    "last_name" VARCHAR(255) NOT NULL,
    "sex" VARCHAR(255) NOT NULL,
    "hire_date" VARCHAR(255) NOT NULL,
    CONSTRAINT "pk_Employees" PRIMARY KEY ("emp_no")
);

CREATE TABLE "Salaries" 
(
    "emp_no" INT   NOT NULL,
    "salary" INT   NOT NULL,
    CONSTRAINT "pk_Salaries" PRIMARY KEY ("emp_no")
);

CREATE TABLE "Titles" 
(
    "title_id" VARCHAR(255) NOT NULL,
    "title" VARCHAR(255) NOT NULL,
    CONSTRAINT "pk_Titles" PRIMARY KEY ("title_id")
);

ALTER TABLE "Dept_Emp" 
    ADD CONSTRAINT "fk_Dept_Emp_dept_no" 
        FOREIGN KEY("dept_no") REFERENCES "Departments" ("dept_no");

ALTER TABLE "Dept_Manager" 
    ADD CONSTRAINT "fk_Dept_Manager_dept_no" 
        FOREIGN KEY("dept_no") REFERENCES "Departments" ("dept_no");

ALTER TABLE "Dept_Manager" 
    ADD CONSTRAINT "fk_Dept_Manager_employee_no" 
        FOREIGN KEY("employee_no") REFERENCES "Dept_Emp" ("emp_no");

ALTER TABLE "Salaries" 
    ADD CONSTRAINT "fk_Salaries_emp_no" 
        FOREIGN KEY("emp_no") REFERENCES "Dept_Emp" ("emp_no");

ALTER TABLE "Titles" 
    ADD CONSTRAINT "fk_Titles_title_id" 
        FOREIGN KEY("title_id") REFERENCES "Employees" ("emp_title_id");

I tried to add UNIQUE in "emp_no" but did not work.

May you please explain as to why I am receiving this error message & how can I fix my script?

Thank you.

Tags:

2

Answers


  1. 0

    The error message "ERROR: there is no unique constraint matching given keys for referenced table ‘Employees’" is raised because you are trying to create a foreign key constraint on the "Titles" table that references the "emp_title_id" column in the "Employees" table. However, there is no unique constraint on the "emp_title_id" column in the "Employees" table, and PostgreSQL requires a unique constraint or primary key on the referenced column when you create a foreign key constraint.

    enter image description here

    It would be more suitable to establish the foreign key in the ‘Employees’ table, referring to the ‘Titles’ table. Additionally, consider changing the data types of the columns to integers and establishing references for improved performance.

    CREATE TABLE "Titles" (
    "title_id" VARCHAR(255)   NOT NULL,
    "title" VARCHAR(255)   NOT NULL,
    CONSTRAINT "pk_Titles" PRIMARY KEY (
        "title_id"
     )
);
CREATE TABLE "Employees" (
    "emp_no" INT NOT NULL,
    "emp_title_id" VARCHAR(255)   NOT NULL,
    "birth_date" VARCHAR(255)   NOT NULL,
    "first_name" VARCHAR(255)   NOT NULL,
    "last_name" VARCHAR(255)   NOT NULL,
    "sex" VARCHAR(255)   NOT NULL,
    "hire_date" VARCHAR(255)   NOT NULL,
    CONSTRAINT "pk_Employees" PRIMARY KEY ("emp_no"),
    CONSTRAINT "fk_Employees_emp_title_id" FOREIGN KEY ("emp_title_id")
        REFERENCES "Titles" ("title_id")
);

    Output after changes

    enter image description here

    Login or Signup to reply.
  2. 0

    The error message suggests that although your schema refers the "Employees" table through a foreign key constraint, the referenced column ("emp_title_id") in the "Employees" database missing a unique constraint. You must make sure that the referenced column has a unique constraint in order to fix this problem.
    To add a unique constraint to the "emp_title_id" column, edit the definition of the "Employees" table.  

    Try this updated definition of employee table;

    CREATE TABLE "Employees" (
    "emp_no" INT NOT NULL,
    "emp_title_id" VARCHAR(255)   NOT NULL,
    "birth_date" VARCHAR(255)   NOT NULL,
    "first_name" VARCHAR(255)   NOT NULL,
    "last_name" VARCHAR(255)   NOT NULL,
    "sex" VARCHAR(255)   NOT NULL,
    "hire_date" VARCHAR(255)   NOT NULL,
    CONSTRAINT "pk_Employees" PRIMARY KEY (
        "emp_no"
     ),
    CONSTRAINT "uq_emp_title_id" UNIQUE ("emp_title_id")
);

    Hope it works 🙂

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search