My PostgreSQL code for foreign key gives foreign key constraint does not exist

Question

my code :

create table courses(
id SERIAL PRIMARY KEY 
)
----------------------
create table teachers(
id SERIAL PRIMARY KEY 
)
----------------------

CREATE TABLE assignments(
    id SERIAL PRIMARY KEY ,
    given_date TIMESTAMP,
    deadline TIMESTAMP,
    FOREIGN KEY(course_id) REFERENCES courses(id),
    FOREIGN KEY(teacher_id) REFERENCES teachers(id),
    pass_mark INT,
    full_mark INT,
    description TEXT,
    assignment_file VARCHAR(100)

);

error:

column "course_id" referenced in foreign key constraint does not exist
SQL state: 42703

i tried running the third create table code after inserting data in the courses and teachers table's id yet no change.

Akhilesh Mishra · Accepted Answer · 2021-06-09T05:14:23.663

You can not reference a column like that. First you have add the column you want to create foreign key then reference it.

create table courses(
id SERIAL PRIMARY KEY 
);
----------------------
create table teachers(
id SERIAL PRIMARY KEY 
);
----------------------

CREATE TABLE assignments(
    id SERIAL PRIMARY KEY ,
    given_date TIMESTAMP,
    deadline TIMESTAMP,
    course_id INT,
    teacher_id INT,
    pass_mark INT,
    full_mark INT,
    description TEXT,
    assignment_file VARCHAR(100),
    FOREIGN KEY(course_id) REFERENCES courses(id),
    FOREIGN KEY(teacher_id) REFERENCES teachers(id)

);

My PostgreSQL code for foreign key gives foreign key constraint does not exist

1 Answers1