there is no unique constraint matching given keys for referenced table "category"?

can 0
Viewed 17

I'm trying to define more than one foreign key in the process table. but I am getting the error that the columns I am trying to define as foreign key are not 'unique value'.

For this, I wanted to define id and name columns as primary keys in category and subject tables. However, when I want to create the process table, I still get this error." there is no unique constraint matching given keys for referenced table "category"

I have researched and continue to do so on Stackoverflow and many more. but I couldn't figure it out with solutions or viewpoints of the issues that got the same error I was facing. Maybe there is something I'm not seeing.

first table;

CREATE TABLE category(
    category_id  INT GENERATED ALWAYS AS IDENTITY,
    category_name VARCHAR(210),
    category_description TEXT,
    CONSTRAINT category_pk PRIMARY KEY(category_id,category_name)
    
);

second table;

CREATE TABLE subject(
    subject_id  INT GENERATED ALWAYS AS IDENTITY,
    subject_name VARCHAR(210),
    subject_description TEXT,
    CONSTRAINT subject_pk PRIMARY KEY(subject_id,subject_name)
);

I tried that too but I keep getting the same error

ALTER TABLE category ADD CONSTRAINT some_constraint PRIMARY KEY(category_id,category_name);

third table;

CREATE TABLE process(
    process_id  INT GENERATED ALWAYS AS IDENTITY,
    fk_category_id INTEGER,
    fk_subject_id INTEGER,
    FOREIGN KEY(fk_category_id) REFERENCES category(category_id) ON DELETE CASCADE ON UPDATE 
    CASCADE,
    FOREIGN KEY(fk_subject_id) REFERENCES subject(subject_id) ON DELETE CASCADE ON UPDATE 
    CASCADE
    
);
postgresql
2 Answers

In your FOREIGN KEY declaration either:

  1. Include both the columns that make up the PRIMARY KEY on category and subject e.g. ... REFERENCES category(category_id, category_name) ...

OR

  1. Do not refer to any column and let the FK pick up the PK automatically e.g. ... REFERENCES category ON DELETE ....

I am going to say that what you are really after is:

CREATE TABLE category(
    category_id  INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    category_name VARCHAR(210) UNIQUE,
    category_description TEXT
    
);

CREATE TABLE subject(
    subject_id  INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    subject_name VARCHAR(210) UNIQUE,
    subject_description TEXT
);

CREATE TABLE process(
    process_id  INT GENERATED ALWAYS AS IDENTITY,
    fk_category_id INTEGER,
    fk_subject_id INTEGER,
    FOREIGN KEY(fk_category_id) REFERENCES category ON DELETE CASCADE ON UPDATE 
    CASCADE,
    FOREIGN KEY(fk_subject_id) REFERENCES subject ON DELETE CASCADE ON UPDATE 
    CASCADE
    
);
Adrian Klaver0

An identity column isn't automatically a primary key. So your tables category and subjectdon't have any primary keys and thus can't be referenced by a foreign key.

You need to add PRIMARY KEY to the columns of the tables category and subject

a_horse_with_no_name0
Related