Skip to content

Foreign Key

FOREIGN KEY is used to form a relationship between two tables and to prevent actions that would destroy links between the linked tables. One or multiple fields can be made as a foreign key. Foreign key should always refer to the primary key in another table.

Example Usage

In this example we've created an table called users that holds all the users. We also have a table called cars and each of the car belongs to one user. So we have created a foreign key for column ownerId in the cars table to reference to userId in the users table:

MySQL
CREATE TABLE users (
    userId INT NOT NULL AUTO_INCREMENT,
    name CHAR(250) NOT NULL,
    PRIMARY KEY (userId));

CREATE TABLE cars (
    carId INT NOT NULL AUTO_INCREMENT,
    platenumber VARCHAR(50) NOT NULL,
    ownerId INT NOT NULL,
    FOREIGN KEY (ownerId) REFERENCES users(userId),
    PRIMARY KEY(carId));

Testing the Behavior

Insert couple users to the table:

MySQL
INSERT INTO users (name) VALUES ("Aleksi"), ("Toni");

Verify that the users exist:

MySQL
SELECT * FROM users;

Example Output 
+--------+--------+
| userId | name   |
+--------+--------+
| 1      | Aleksi |
| 2      | Toni   |
+--------+--------+

Then, insert couple cars:

MySQL
INSERT INTO cars (plateNumber, ownerId) VALUES
    ("ABC-123", 1),
    ("MNB-987", 1),
    ("POI-312", 2);

And verify that the cars exist:

MySQL
SELECT * FROM cars;

Example Output 
+-------+-------------+---------+
| carId | platenumber | ownerId |
+-------+-------------+---------+
| 1     | ABC-123     | 1       |
| 2     | MNB-987     | 1       |
| 3     | POI-312     | 2       |
+-------+-------------+---------+

And as a last thing, try removing from user, which still has car assigned to him / her:

MySQL
DELETE FROM users WHERE userId = 1;

The last query should give you an error because you cannot remove user with an ID of 1 because that user still has cars in the cars table.

Learn More