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
);
2
Answers
An
identity
column isn’t automatically a primary key. So your tablescategory
andsubject
don’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 tablescategory
andsubject
In your
FOREIGN KEY
declaration either:PRIMARY KEY
oncategory
andsubject
e.g.... REFERENCES category(category_id, category_name) ...
OR
... REFERENCES category ON DELETE ...
.I am going to say that what you are really after is: