PostgreSQL外键语法 [英] postgresql foreign key syntax

问题描述

我有2个表格，如您在下面的posgresql代码中所见.第一个表student有两列，一列用于student_name，另一列是student_id，这是主键. 在我称为测试的第二张表中，该表有4列，一列用于subject_id，一列用于subject_name，然后一列用于在该学科中具有最高分数的学生，该分数最高.我正在尝试使highestStudent_id引用我的students表中的student_id.这是我下面的代码，不确定语法是否正确:

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:

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

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

语法highestStudent_id SERIAL REFERENCES students是否正确?因为我看过另外一个像highestStudent_id REFERENCES students(student_id))

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

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

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

推荐答案

假设此表:

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

定义外键有四种不同的方法(当处理单列PK时)，它们都导致相同的外键约束:

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:

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

1. Inline without mentioning the target column:

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

内嵌提及目标列:

Inline with mentioning the target column:

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

create table内的行:

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

作为单独的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);

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

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)

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

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.

serial数据类型实际上不是数据类型.这只是一种简写形式，它定义了从序列中获取的列的默认值.因此，必须使用适当的基本类型integer(对于bigserial列为bigint)来定义任何被定义为serial的列引用.

The serial data 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 referencing a column defined as serial must be defined using the appropriate base type integer (or bigint for bigserial columns)

这篇关于PostgreSQL外键语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！