PostgreSQL Foreign Key syntax

Question

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

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

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

Yes the syntax is "correct". However the FK column should not be defined as serial it should defined as integer. serial is not a "real" data type, it's a short hand for populating the default value from sequence — user330315
– user330315, Commented Feb 17, 2015 at 9:45
If the FK references a primary key, no columns are needed. If the FK references an alternate key, columns are needed. — jarlh
– jarlh, Commented Feb 17, 2015 at 9:47
Your foreign key references the table "players". You don't appear to have a table named "players". — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Feb 17, 2015 at 10:06
@Mike Sherrill 'Cat Recall sorry , my mistake i meant highestStudent_id integer REFERENCES students — Hamza
– Hamza, Commented Feb 17, 2015 at 10:12

busytraining · Accepted Answer · 2018-01-29 17:56:46Z

525

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:

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

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

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)

edited Jan 29, 2018 at 17:56

busytraining

7838 silver badges14 bronze badges

answered Feb 17, 2015 at 11:10

user330315

Sign up to request clarification or add additional context in comments.

4 Comments

wordsforthewise Over a year ago

This link (postgresqltutorial.com/postgresql-foreign-key) shows another way to do what you said can only be done with the 'constraint' command in 3 and 4. Also, what about putting FOREIGN KEY before the FK? It looks like when we do that, we don't have to declare the variable type?

Venryx Over a year ago

For the alter table variant, you don't need to specify the constraint name if you don't want to, btw. This works: ALTER TABLE "FROM_TABLE" ADD FOREIGN KEY ("FROM_COLUMN") REFERENCES "TO_TABLE" ("TO_COLUMN");

TheRealChx101 Over a year ago

@Venryx It's always a good idea to specify the constraint name. When you have to drop these constraints through migrations it becomes easier. Without the names it's a nightmare because each server will generate its own name and your migration script may fail.

Dan Lenski Over a year ago

@Venryx, you can also omit the constraint name for (3), e.g. FOREIGN KEY (col1, col2) REFERENCES other_table [(colA, colB)] works fine.

Michael Easter · Accepted Answer · 2025-05-02 07:52:38Z

11

ALTER TABLE table_name ADD FOREIGN KEY(column_name) REFERENCES reference_table_name(reference_column_name);

Note the column must have already been created.

edited May 2 at 7:52

Michael Easter

24.6k9 gold badges81 silver badges111 bronze badges

answered Oct 4, 2022 at 13:47

Ndongmo christian

1511 silver badge8 bronze badges

Collectives™ on Stack Overflow

PostgreSQL Foreign Key syntax

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related