SQL postgresql 外键语法

postgresql foreign key syntax

提问by Hamza

I have 2 tables as you will see in my posgresql code below. The first table students has 2 columns, one for student_name and the other student_id which is the primary key. In my second table called tests, this has 4 columns, one for subject_id, one for the subject_name, then one for a student with the higest score in a subject which is highestStudent_id. am trying to make highestStudent_id refer to student_id in my students table. This is the code i have below , am not sure if the syntax is correct:

我有 2 个表，您将在下面的 posgresql 代码中看到。第一个表 student 有 2 列，一个是 student_name，另一个是 student_id，它是主键。在我的第二个称为测试的表中，它有 4 列，一列用于 subject_id，一列用于 subject_name，然后一列用于在最高学生 ID 的科目中得分最高的学生。我试图让highestStudent_id 引用我的学生表中的student_id。这是我下面的代码，不确定语法是否正确：

CREATE TABLE students ( student_id SERIAL PRIMARY KEY,
                 player_name TEXT);

CREATE TABLE tests ( subject_id SERIAL,
                   subject_name,
                   highestStudent_id SERIAL REFERENCES students);

is the syntax highestStudent_id SERIAL REFERENCES studentscorrect? because i have seen another one like highestStudent_id REFERENCES students(student_id))

语法highestStudent_id SERIAL REFERENCES students正确吗？因为我看过另一个像highestStudent_id REFERENCES students(student_id))

What would be the correct way of creating the foreign key in postgresql please?

请问在postgresql中创建外键的正确方法是什么？

回答by a_horse_with_no_name

Assuming this table:

假设这个表：

CREATE TABLE students 
( 
  student_id SERIAL PRIMARY KEY,
  player_name TEXT
);

There are four different ways to define a foreign key (when dealing with a single column PK) and they all lead to the same foreign key constraint:

有四种不同的方法来定义外键（处理单列 PK 时），它们都导致相同的外键约束：

1. Inline without mentioning the target column:

   CREATE TABLE tests 
   ( 
      subject_id SERIAL,
      subject_name text,
      highestStudent_id integer REFERENCES students
   );
   

2. Inline with mentioning the target column:

   CREATE TABLE tests 
   ( 
      subject_id SERIAL,
      subject_name text,
      highestStudent_id integer REFERENCES students (student_id)
   );
   

3. Out of line inside the create table:

   CREATE TABLE tests 
   ( 
     subject_id SERIAL,
     subject_name text,
     highestStudent_id integer, 
     constraint fk_tests_students
        foreign key (highestStudent_id) 
        REFERENCES students (student_id)
   );
   

4. As a separate alter tablestatement:

   CREATE TABLE tests 
   ( 
     subject_id SERIAL,
     subject_name text,
     highestStudent_id integer
   );
   
   alter table tests 
       add constraint fk_tests_students
       foreign key (highestStudent_id) 
       REFERENCES students (student_id);
   

1. 内联而不提及目标列：

   CREATE TABLE tests 
   ( 
      subject_id SERIAL,
      subject_name text,
      highestStudent_id integer REFERENCES students
   );
   

2. 内联提及目标列：

   CREATE TABLE tests 
   ( 
      subject_id SERIAL,
      subject_name text,
      highestStudent_id integer REFERENCES students (student_id)
   );
   

3. 里面的不合时宜create table：

   CREATE TABLE tests 
   ( 
     subject_id SERIAL,
     subject_name text,
     highestStudent_id integer, 
     constraint fk_tests_students
        foreign key (highestStudent_id) 
        REFERENCES students (student_id)
   );
   

4. 作为单独的alter table声明：

   CREATE TABLE tests 
   ( 
     subject_id SERIAL,
     subject_name text,
     highestStudent_id integer
   );
   
   alter table tests 
       add constraint fk_tests_students
       foreign key (highestStudent_id) 
       REFERENCES students (student_id);
   

Which one you prefer is a matter of taste. But you should be consistent in your scripts. The last two statements are the only option if you have foreign keys referencing a PK that consists of more than one column - you can't define the FK "inline" in that case, e.g. foreign key (a,b) references foo (x,y)

你喜欢哪一个是品味问题。但是你应该在你的脚本中保持一致。如果您有外键引用由多列组成的 PK，则最后两个语句是唯一的选择 - 在这种情况下，您不能定义 FK“内联”，例如foreign key (a,b) references foo (x,y)

Only version 3) and 4) will give you the ability to define your own name for the FK constraint if you don't like the system generated ones from Postgres.

如果您不喜欢系统从 Postgres 生成的名称，则只有版本 3) 和 4) 才能让您为 FK 约束定义自己的名称。

---

---

The serialdata type is not really a data type. It's just a short hand notation that defines a default value for the column taken from a sequence. So any column referencinga column defined as serialmust be defined using the appropriate base type integer(or bigintfor bigserialcolumns)

该serial数据类型是不是一个真正的数据类型。它只是一种简写符号，用于定义从序列中获取的列的默认值。因此，任何引用定义为的列的列都serial必须使用适当的基本类型integer（或bigint用于bigserial列）来定义