Postgresql foreign key syntax

Question

I have 2 tables, as you will see in my postgresql code below. The first students in the table have 2 columns, one for the student’s name and the other student_id the primary key. In my second table, called tests, it has 4 columns, one for subject_id, one for subject_name, then one for the student with the highest score in the subject, which is the highest Student_id. I am trying to make highStudent_id a reference to student_id in my students table. This is the code I have below, 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 highestStudent_id SERIAL REFERENCES students syntax correct? because I saw another one similar to highestStudent_id REFERENCES students(student_id))

What would be the correct way to create a foreign key in postgresql, please?

Answer 1

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 working with one PK column), and all of them lead to the same foreign key constraint:

• A row without mentioning the target column:

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

• In the row indicating the target column:

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

• Outside the line inside the create table :

   CREATE TABLE tests ( subject_id SERIAL, subject_name text, highestStudent_id integer, constraint fk_tests_students foreign key (highestStudent) 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) REFERENCES students (student_id); 

Which one do you prefer is a matter of taste. But you have to be consistent in your scenarios. The last two statements are the only option, if you have foreign keys that refer to PK, which consists of more than one column - you cannot define FK "inline" in this case, for example. foreign key (a,b) references foo (x,y)

Only versions 3) and 4) will give you the opportunity to define your own name for FK restrictions if you do not like the ones created by the system from Postgres.

---

The serial data type is not a data type. This is just a short notation that defines the default value for a column taken from a sequence. Therefore, any column referencing a column defined as serial must be defined using the appropriate integer base type (or bigint for bigserial columns)