Cross foreign key Postgresql

Question

I need to create two tables such as:

Id_faculty_reference and Id_professor are primary keys (that works)
Id_dean is a foreign key with reference to Id_professor
Id_faculty is a foreign key with reference to Id_faculty_reference (the problem).

I tried this:

CREATE TABLE Faculty(
      Id_faculty_reference int PRIMARY KEY,
      faculty_name varchar,
      Id_dean int
);

CREATE TABLE Professors(
      Id_professor int PRIMARY KEY,
      Name varchar,
      Last_name varchar,
      Salary int,
      Id_faculty int REFERENCES Faculty(id_faculty_reference)
);

ALTER TABLE Faculty ADD FOREIGN KEY (Id_dean)
   REFERENCES Professors(id_professor);

The problem comes when I try to add information into the tables. If I try to add info into Faculty, there is no reference, because Professors is empty:

Key is not present in table "Professors"

If I try to add info into Professors, there is no reference because Faculty is empty:

Key is not present in table "Faculty"

The mistake makes sense to me, but my professor says that what he asks can be done; how can I do this?

Laurenz Albe · Accepted Answer · 2020-11-24 03:19:45Z

There are three approaches:

First insert a faculty where id_dean is NULL. Then insert a professors that references that faculty entry. Then update the first entry to point to the second.

This works because id_dean can be NULL, and a foreign key that is set to NULL is not enforced.

In general it is a good idea to have as many columns NOT NULL as possible. In that case use one of the other methods.

Foreign keys are checked at the end of the statement, so insert both rows in a single statement:

WITH newfac AS (
   INSERT INTO faculty (...) VALUES (...)
   RETURNING id
)
INSERT INTO professors (id_faculty, ...)
SELECT newfac.id, ...
FROM newfac;

Use a deferred foreign key constraint:
```
CREATE TABLE faculty(
   ...,
   id_dean int REFERENCES professors DEFERRABLE INITIALLY DEFERRED
);
```
Such a foreign key constraint is not checked at the end of the statement, but at the end of the transaction. So you can first enter the faculty, then the professors, as long as you do it in a single database transaction.

Collectives™ on Stack Overflow

Cross foreign key Postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related