I do not understand the practical use of FOREIGN KEY for tables. For example, I have three tables:

 Users {user_id, name, surname} Orders {order_id, user_id, invoice_id} Invoices {invoice_id, summ}

It is logical to use user_id and invoice_id as foreign keys from the corresponding tables Users and Invoices. As far as I understand, assigning user_id and invoice_id foreign keys will not allow me to make a mistake and add an order to the Orders table with nonexistent user_id and invoice_id .

On this practical use FOREIGN KEY ends? I have doubts about this.

How else should i use FOREIGN KEY ?

I can imagine if I had a name field in the Orders table, the value of which is automatically updated if its value in the parent table changes. This would be useful, but it seems to me that it is not correct to create duplicate fields in different tables ... Therefore, I am trying to understand how FOREIGN KEY ?

  • Not true for MySql, but other DBMSs support constructions like on delete cascade , etc. - Viktorov
  • Control of referential integrity is the main and only purpose of foreign keys. And yes, as @Viktorov noted, in addition, foreign keys can cascade to delete records when deleting from the parent table or set links to NULL (if asked about it). But in general, this is one of the varieties of integrity monitoring. But this sole purpose is more than enough, the database without link control very quickly comes to an inconsistent state and after that it is impossible to figure out where it came from - Mike
  • Thanks for the clarification. Please write the answer and I will accept it. - Nastro

2 answers 2

FOREIGN KEY is used to limit references. When all values ​​in one field of a table are presented in a field of another table, it is said that the first field refers to the second. This indicates a direct relationship between the values ​​of the two fields.

When one field in a table refers to another, it is called a foreign key, and the field to which it refers is called the parent key. The names of the foreign key and the parent key do not have to be the same. A foreign key can have any number of fields, all of which are processed as a single module. The foreign key and the parent key to which it refers must have the same field number and type, and be in the same order. When a field is a foreign key, it is definitely associated with the table to which it refers. Each value, (each line) of a foreign key must explicitly refer to one and only this value (line) of the parent key. If this condition is met, the database is in a state of referential integrity.

SQL supports referential integrity with a FOREIGN KEY constraint . This function should limit the values ​​that can be entered into the database to force the foreign key and the parent key to comply with the principle of referential integrity. One of the actions of the FOREIGN KEY constraint is to drop values ​​for fields that are restricted as a foreign key that is not already present in the parent key. This restriction also affects the ability to change or delete parent key values.

The FOREIGN KEY constraint is used in the CREATE TABLE command (or ALTER TABLE (intended to modify the table structure) containing a field that is declared a foreign key. The parent key is given a name referenced within the FOREIGN KEY constraint .

Like most restrictions, it can be a table or column constraint, in the form of a table allowing multiple fields to be used as one foreign key.

Example: there are two users and emails tables:

 CREATE TABLE users ( user_id int(11) NOT NULL AUTO_INCREMENT, user_name varchar(50) DEFAULT NULL, PRIMARY KEY (user_id) ) CREATE TABLE sys.emails ( email_id int(11) NOT NULL AUTO_INCREMENT, email_address varchar(100) NOT NULL, user_id int(11) NOT NULL, PRIMARY KEY (email_id) )

There is a dependency between them in the form of users.user_id and emails.user_id, and we want that in the emails.user_id field nothing could be written except the value from the users table in the user_id column, for this we create a restriction in the form of a foreign key:

 ALTER TABLE emails ADD FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE NO ACTION;

Suppose if we delete a user then automatically delete all the records associated with him, in this case his email address.

  • Thanks for the explanation. With a simple example, it would be easier to track down the meaning. - Nastro

In MySQL 3.23.44 and above, InnoDB tables check the integrity constraints of foreign keys

In fact, to connect two tables, foreign keys are not needed.

The only thing that MySQL currently does not implement (in table types other than InnoDB) is to check (CHECK) that the keys you use do exist in the table (s) you refer to and does not automatically delete the records from the tables with the definition of foreign keys.