PostgreSQL 外键语法

我有2个表，你会看到在我的 PosgreSQL 代码下面。第一个表中的学生有两列，一列是 student_name，另一列是 student_id，即主键。

在我的第二个测试表中，有4列，一列是 subject_id，一列是 subject_name，还有一列是 highestStudent_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);

语法 highestStudent_id SERIAL REFERENCES students正确吗? 因为我已经看到另一个像 highestStudent_id REFERENCES students(student_id))

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

147086 次浏览
小开
最佳答案

假设这张表:

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

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

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

    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 table statement:

    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)

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.

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)

小开 
ALTER TABLE table_name ADD FOREIGN KEY(colunm_name) REFERENCES reference_table_name(reference_column_name);

注意，该列必须已经创建。


提交答案