What is wrong with this Query ? PostgreSQL-CodePudding

I want to create 3 tables and join them with foreign keys. Unfortunately, it doesn't work and I have no idea where is a mistake.

CREATE TABLE Students (
       Student_Id SERIAL PRIMARY KEY,
       Name VARCHAR (20) NOT NULL,
       Surname VARCHAR (30) NOT NULL,
       Date_of_Birth DATE NOT NULL,
       Phone INT NOT NULL UNIQUE,
       Email VARCHAR(225) NOT NULL UNIQUE,
       Course_Id INT
       FOREIGN KEY (Course_Id) REFERENCES Course (Course_Id)
);

CREATE TABLE Course (
       Course_Id SERIAL PRIMARY KEY,
       Student_Id INT NOT NULL,
       Teacher_Id INT NOT NULL,
       Category VARCHAR (30) NOT NULL,
       FOREIGN KEY (Student_Id) REFERENCES Students (Student_Id)
);

CREATE TABLE Teachers (
       Teacher_Id SERIAL PRIMARY KEY,
       Name VARCHAR (20) NOT NULL,
       Surname VARCHAR (30) NOT NULL,
       Phone INT NOT NULL UNIQUE,
       Salary INT NOT NULL,
       Course_Id INT NOT NULL, 
       FOREIGN KEY (Teacher_Id) REFERENCES Course (Teacher_Id)
    );

I should create a Foreign Key to join all three tables. I get this error every time: relation "course" does not exist I can't find where is the mistake. Please help.

CodePudding user response：

CREATE TABLE Students (
       Student_Id SERIAL PRIMARY KEY,
       Name VARCHAR (20) NOT NULL,
       Surname VARCHAR (30) NOT NULL,
       Date_of_Birth DATE NOT NULL,
       Phone INT NOT NULL UNIQUE,
       Email VARCHAR(225) NOT NULL UNIQUE,
       Course_Id INT
);

CREATE TABLE Course (
       Course_Id SERIAL PRIMARY KEY,
       Student_Id INT NOT NULL,
       Teacher_Id INT NOT NULL,
       Category VARCHAR (30) NOT NULL,
       FOREIGN KEY (Student_Id) REFERENCES Students (Student_Id)
);

CREATE TABLE Teachers (
       Teacher_Id SERIAL PRIMARY KEY,
       Name VARCHAR (20) NOT NULL,
       Surname VARCHAR (30) NOT NULL,
       Phone INT NOT NULL UNIQUE,
       Salary INT NOT NULL,
       Course_Id INT NOT NULL, 
       FOREIGN KEY (Teacher_Id) REFERENCES Course (Teacher_Id)
    );

Now I get a different error: ERROR: there is no unique constraint matching given keys for referenced table "course" SQL state: 42830

CodePudding user response：

It appears like you are attempting to crate a many-to-many (M:M) between Students and Teachers with Course as the resolution table. You are close, however, your definition sets up bi-directional relationships. This is normally not necessary. Define Students and Teachers without the FK to Course. Then define Course with a FK to each.

create table students (
       student_id     serial       primary key
     , name           varchar (20) not null
     , surname        varchar (30) not null
     , date_of_birth  date         not null
     , phone          int          not null unique
     , email          varchar(225) not null unique
);

create table teachers (
       teacher_id     serial       primary key      
     , name           varchar (20) not null
     , surname        varchar (30) not null
     , phone          int          not null unique
     , salary         int          not null
    );
    

   create table course (
       course_id    serial       primary key      
     , student_id   int          not null
     , teacher_id   int          not null
     , category     varchar (30) not null
     , foreign key (student_id)  references students (student_id)
     , foreign key (teacher_id)  references teachers (teacher_id) 
     , unique      (student_id, teacher_id)
);

If you must define bi-directional FK in Students and Teachers then create the tables without the FK then use alter table after Course is defined to add the FK and make them DEFERRABLE INITIALLY DEFERRED. Necessary for eventual Inserts.

alter table teachers add column course_id int references course(course_id) deferrable initially deferred;
alter table students add column course_id int references course(course_id) deferrable initially deferred;