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

问题描述

我有 2 个表，您将在下面的 posgresql 代码中看到.第一个表学生有 2 列，一个用于 student_name，另一个用于主键 student_id.在我的第二个名为 tests 的表中，它有 4 列，一列用于 subject_id，一列用于 subject_name，然后一列用于在最高Student_id 的科目中得分最高的学生.我试图让 highstStudent_id 引用我的学生表中的 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 内部异常:

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内联"，例如外键(a,b)引用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 数据类型并不是真正的数据类型.它只是一个简写符号，它定义了从序列中获取的列的默认值.因此，任何列引用定义为serial的列必须使用适当的基本类型integer(或bigintbigserial 列)

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屋！