How to create a table with a foreign key in SQL

Problem

You need to create a new SQL table that includes a foreign key. A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. In simpler words, the foreign key in one table refers to the primary key in another table.

Sample Data

Let's suppose we have two tables, Authors and Books. In the Authors table, each author has a unique author_id. In the Books table, we want to reference the author_id to indicate who wrote each book.

CREATE TABLE Authors (
    author_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

MySQL Solution

In MySQL, you could create a new table using the following syntax:

CREATE TABLE Books (
    book_id INT PRIMARY KEY,
    title VARCHAR(100),
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES Authors(author_id)
);

Explanation (MySQL)

In MySQL, after defining the columns of the table, you specify the foreign key with the FOREIGN KEY keyword followed by the column name in brackets. The REFERENCES keyword is then used to specify the table and column that the foreign key is referring to.

PostgreSQL Solution

In PostgreSQL, you use a similar syntax to create a new table with a foreign key:

CREATE TABLE Books (
    book_id INT PRIMARY KEY,
    title VARCHAR(100),
    author_id INT REFERENCES Authors(author_id)
);

Explanation (PostgreSQL)

In PostgreSQL, you can declare foreign keys in a slightly more compact way. You can directly specify the foreign key after the column declaration, by using the REFERENCES keyword followed by the table and column that the foreign key is referring to. This is the main difference with MySQL, where you need to use the FOREIGN KEY keyword.