How to Create a Table with a Foreign Key in SQL

Problem:

You want to create a foreign key for a table in a database.

Example:

We would like to create a table named student that contains a foreign key that refers to the id column in the table city.

Solution 1: Creating new table with single-column foreign key

CREATE TABLE student (
  id INT PRIMARY KEY,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  city_id INT FOREIGN KEY REFERENCES city(id)
);

Discussion:

To create a new table containing a foreign key column that references another table, use the keyword FOREIGN KEY REFERENCES at the end of the definition of that column. Follow that with the name of the referenced table and the name of the referenced column in parentheses.

In our example, we create the table student using a CREATE TABLE clause. We list the columns’ names and put their respective data types in parentheses. The column city_id is the foreign key in this table and indicates the value of the ID stored in the column id in the table city. We write FOREIGN KEY REFERENCES at the end of the definition of this column and follow it with the referenced table and column: city(id).

Keep in mind that you can create more than one foreign key for a table.

Solution 2: Creating new table with foreign key definition at the end of table definition

CREATE TABLE student (
  id INT PRIMARY KEY,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  city_id INT,
  FOREIGN KEY (city_id) REFERENCES city(id)
);

Discussion:

Another way to define a foreign key during table creation is to use the FOREIGN KEY REFERENCES clause at the end of the column definitions. In this case, after the FOREIGN KEY clause, we designate the foreign key column. Next comes the REFERENCES clause along with the name of the referred table and column.

You can create foreign keys on more than one column, as shown below:

Solution 3: Creating new table with a named two-column foreign key

CREATE TABLE student (
  id INT PRIMARY KEY,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  score_id INT,
  subject_id INT,
  CONSTRAINT fk_student_score_subject_id 
  FOREIGN KEY (subject_id, score_id) REFERENCES score_subject(subject_id, score_id)
);

In this example, the constraint fk_student_score_subject_id is a foreign key consisting of two columns: score_id and subject_id. These two foreign key columns refer to two columns in the table score_subject – score_id and subject_id.

Here’s another example:

Solution 4: Creating new table with a named foreign key

CREATE TABLE student (
  id INT PRIMARY KEY,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  city_id INT,
  CONSTRAINT fk_student_city_id 
  FOREIGN KEY (city_id) REFERENCES city(id)
);

Discussion:

In this code, we again have the CONSTRAINT clause with the name of this constraint. Use names that are easy to read and understand. In our example, we use the name fk_student_city_id, which indicates the relevant table and column. Next, we write FOREIGN KEY and add (in parentheses) the name of the column that becomes the foreign key. Then we have the REFERENCES clause followed by the name of the referenced table and column (here: id).

Solution 5: Modifying an existing table

ALTER TABLE student
ADD FOREIGN KEY (city_id) REFERENCES city(id);

Discussion:

It is also possible to add a new foreign key to an existing table. Here, the table is altered using an ALTER TABLE clause. The table name (in our example, student) is placed after the ALTER TABLE keyword. Next, the ADD FOREIGN KEY clause is followed by the name of the column that will be used as the foreign key. Then we have the REFERENCES clause with the name of the referenced table and the name of the primary key column in parentheses.

Note that the table you’re modifying must exist before this command is executed.

Solution 6: Modifying an existing table with a named foreign key constraint

ALTER TABLE student
      ADD CONSTRAINT fk_student_city_id 
      FOREIGN KEY (city_id) REFERENCES city(id)

Discussion:

Use a query like this if you want to name a foreign key column as a constraint for an existing table. Here, the foreign key constraint is named fk_student_city_id. If you do not specify the constraint name, the database generates a default constraint name (which will vary by database).