跨外键 Postgresql

Question

我需要创建两个表格，如下所示：

• Id_faculty_reference和Id_professor是主键（可以工作）。

• Id_dean是一个外键，参照Id_professor

• Id_faculty是一个外键，参照Id_faculty_reference （有问题）。

我尝试了这个：

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);

问题出现在我尝试向表中添加信息时。如果我尝试向Faculty添加信息，由于Professors为空，所以没有引用：

Key is not present in table "Professors"

如果我尝试向教授中添加信息，由于系别为空，所以不存在参考。

Key is not present in table "Faculty"

这个错误对我来说是有道理的，但我的教授说他所要求的是可行的；我该怎么做？

- AnthonyLau

Answer 1

有三种方法：

1. 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.

2. 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;
   

3. 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.

- Laurenz Albe