Relationships between SQL tables. FOREIGN KEY examples

FOREIGN KEY – allow you to establish relationships between SQL tables.

[CONSTRAINT constraint_name]
FOREIGN KEY(column1,column2, ...columnN) //the table column that will represent the foreign key
REFERENCES main_table (main_table_column1, main_table_column2, ... main_table_columnN) //the name of the associated
table is specified, and in parentheses the name of the associated column
//action on deleting and adding row
[ON DELETE action]
[ON UPDATE action]

For example, let’s define two tables and link them with a foreign key:

First:

CREATE TABLE Customers
(
Id INT PRIMARY KEY AUTO_INCREMENT,
Age INT,
FirstName VARCHAR(20) NOT NULL,
LastName VARCHAR(20) NOT NULL,
Phone VARCHAR(20) NOT NULL UNIQUE
);

Second:

CREATE TABLE Orders
(
Id INT PRIMARY KEY AUTO_INCREMENT,
CustomerId INT,
CreatedAt Date,
FOREIGN KEY (CustomerId) REFERENCES Customers (Id)
);

FOREIGN KEY (CustomerId) – the name of the column of 2 tables for which you want to link.
REFERENCES Customers (Id) – The name of the main table and the column of the column to be linked.

That is, the CustomerId column is a foreign key that points to the Id column from the Customers table.

You can use the CONSTRAINT statement to specify a name for a foreign key constraint:

CREATE TABLE Orders
(
Id INT PRIMARY KEY AUTO_INCREMENT,
CustomerId INT,
CreatedAt Date,
CONSTRAINT orders_custonmers_fk
FOREIGN KEY (CustomerId) REFERENCES Customers (Id)
);

With the ON DELETE command you can set the actions that will be performed when deleting the associated line from the main one
tables.

With the ON UPDATE command you can set the actions that will be performed when updating the associated line from the main
tables.

These commands can perform the following actions:

CASCADE – automatically deletes or modifies data in the row of the dependent table when changing or deleting data from the main row
tables.

Changing the value of a primary key will automatically change the value of its associated foreign key. However
since primary keys change very rarely, and in principle it is not recommended to use them as primary keys
keys are columns with mutable values, the ON UPDATE statement is rarely used in practice.

SET NULL – when deleting or updating a related row from the main table, sets the foreign key column
NULL value.

RESTRICT – Rejects deletion or modification of rows in the main table if there are related rows in the dependent table.

NO ACTION – same as RESTRICT.

SET DEFAULT – when deleting a related row from the main table, sets the value of the foreign key column to
the default, which is set using the DEFAULT attribute.