Unable to explain this error: there is no unique constraint matching given keys for referenced table

Question

Background -
I'm extremely new to Postgres and SQL in general. I'm working my way through a homework task, where we have to take 6x .csv files containing data and conceptionlse them as tables in the form of a ERD, before exporting the ERD as SQL and running in Postgres.

My SQL script -
-- Staff Title Table

CREATE TABLE "titles" (
    "title_id" VARCHAR   NOT NULL,
    "title" VARCHAR   NOT NULL
);

-- Employees Table
CREATE TABLE "employees" (
    "emp_no" INT   NOT NULL,
    "emp_title_id" INT   NOT NULL,
    "birth_date" DATE   NOT NULL,
    "first_name" VARCHAR   NOT NULL,
    "last_name" VARCHAR   NOT NULL,
    "sex" INT   NOT NULL,
    "hire_date" DATE   NOT NULL,
    CONSTRAINT "pk_employees" PRIMARY KEY (
        "emp_no"
     )
);

-- Salaries Table
CREATE TABLE "salaries" (
    "emp_no" INT   NOT NULL,
    "salary" INT   NOT NULL
);

-- Department Manager
CREATE TABLE "dept_manager" (
    "dept_no" VARCHAR   NOT NULL,
    "emp_no" INT   NOT NULL
);

-- Department Numbers
CREATE TABLE "departments" (
    "dept_no" VARCHAR   NOT NULL,
    "dept_name" VARCHAR   NOT NULL,
    CONSTRAINT "pk_departments" PRIMARY KEY (
        "dept_no"
     )
);

-- Employee vs. Department
CREATE TABLE "dept_emp" (
    "emp_no" INT   NOT NULL,
    "dept_no" VARCHAR   NOT NULL
);

ALTER TABLE "employees" ADD CONSTRAINT "fk_employees_emp_title_id" FOREIGN KEY("emp_title_id")
REFERENCES "titles" ("title_id");

ALTER TABLE "salaries" ADD CONSTRAINT "fk_salaries_emp_no" FOREIGN KEY("emp_no")
REFERENCES "employees" ("emp_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_emp_no" FOREIGN KEY("emp_no")
REFERENCES "employees" ("emp_no");

ALTER TABLE "dept_emp" ADD CONSTRAINT "fk_dept_emp_emp_no" FOREIGN KEY("emp_no")
REFERENCES "employees" ("emp_no");

ALTER TABLE "dept_emp" ADD CONSTRAINT "fk_dept_emp_dept_no" FOREIGN KEY("dept_no")
REFERENCES "departments" ("dept_no");

Issue
I export the SQL script fine, however when I run

ALTER TABLE "employees" ADD CONSTRAINT "fk_employees_emp_title_id" FOREIGN KEY("emp_title_id")
REFERENCES "titles" ("title_id");

"I get the error message ERROR:there is no unique constraint matching given keys for referenced table "titles" SQL state: 42830"

My thoughts -
...is that perhaps the name mismatch between the foreign key in the titles table (title_id) and it's primary key in the employees table (emp_title_id) is causing this issue. Both title_id and emp_title_id contain the same values (that being an employee id e.g S0002).

(Clearly,) This is a faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. — philipxy, Jun 11 '20 at 23:26
Does this answer your question? [No unique constraint matching given keys for referenced table](https://stackoverflow.com/questions/8317006/no-unique-constraint-matching-given-keys-for-referenced-table) — philipxy, Jun 11 '20 at 23:32

GMB · Accepted Answer · 2020-06-11T22:28:28.700

The error message is trying to tell you. A foreign key can only reference a set of columns that is unique, or primary key. But the target of your foreign key, titles(title_id), fails to meeting this requirement.

So basically you need to declare title_id as the primary key in the titles table to be able to create this foreign key.

You also need to align the datatypes between the columns (that's another requirement of foreign keys): as of now, titles(title_id) is declared as a string, while the referencing column is INT. Presumably, the source column should be INT too.

So:

CREATE TABLE titles (
    title_id int   NOT NULL,
    title VARCHAR   NOT NULL,
    CONSTRAINT pk_titles PRIMARY KEY (title_id)
);

With this fix, your entire code runs fine in this DB Fiddle

Unrelated note: you should avoid double quoting all the identifers; it does not help, and might make things more complicated later on.

Thank you for the useful pointer about double quoting (bad habit) and of course putting me right. — William, Jun 11 '20 at 22:35

score 0 · Answer 2 · answered Jun 11 '20 at 22:23

0

In the table "titles" you forgot to define the primary key. Therefore you cannot create a foreign key against this table.

To fix this, the table definition should look like:

CREATE TABLE "titles" (
    "title_id" VARCHAR   NOT NULL,
    "title" VARCHAR   NOT NULL,
    CONSTRAINT "pk_titles" PRIMARY KEY (
        "title_id"
     )
);

answered Jun 11 '20 at 22:23

The Impaler

45,731
9
39
76

I appreciate your input with the answer also. Such a silly simple thing. – William Jun 11 '20 at 22:36

Unable to explain this error: there is no unique constraint matching given keys for referenced table

2 Answers2