The basics of foreign keys in MySQL?

Question

Is there a good explanation on how to use the MySQL foreign key construct?

I do not quite understand this from the MySQL docs themselves. So far I have been doing things like foreign keys, with bundles and program code.

And the second part of the question: are there any improvements related to using MySQL built-in foreign keys?

Answer 1

FOREIGN KEYS just make sure your data is consistent.

They do not improve queries in terms of efficiency, they just make the wrong queries.

If you have such a relationship:

 CREATE TABLE department (id INT NOT NULL) CREATE TABLE employee (id INT NOT NULL, dept_id INT NOT NULL, FOREIGN KEY (dept_id) REFERENCES department(id)) 

you cannot delete department if it has employee .

If you specify ON DELETE CASCADE in the FOREIGN KEY definition, the reference lines will be automatically deleted along with the specified ones.

As a limitation, FOREIGN KEY actually slows down queries a bit.

Additional validation should be performed when deleting from a table with links or pasting into a link.

Answer 2

The main advantages of using real foreign keys are to ensure data integrity and the ability to configure cascading actions on related elements when something changes or is deleted.

For example, imagine you are programming a forum. You have a topics table with the main key topics.topic_id , and you have a posts table where posts are attached to topics with a posts.topic_id column, which is the foreign key in the topics table.

This foreign key relationship ensures that each message is attached to a valid subject. If you have only one topic, you have ID # 1, it is impossible to create a post in the database attached to topic No. 2. The database provides this.

For cascading advantage, you can configure it so that if a topic has been removed from the topic table, the database will automatically delete all messages in the message table that were attached to this topic. This is good because it removes the step that you must remember to do manually, which can become quite complicated if you have many tables linked to each other. With foreign keys, all relationships can be cleared automatically.

Answer 3

1.FOREIGN KEYS simply guarantee the consistency of your data.

2. If we apply the delete cascade to the foreign key definition, the link to the row will be automatically deleted when the parent row is deleted.

3. If we apply the Update Cascade update to the foreign key definition, the child row will be automatically updated when the parent row is updated.

Query: ALTER TABLE child ADD FOREIGN KEY (parent_id) LINKS parent (id) ON UPDATE CASCADE ON DELETE CASCADE,

4. you cannot delete the direct parent table, first remove the foreign key from the child table than delete the parent table.

Answer 4

The main advantage is that you can limit what values ​​you can enter into the table; if you try to enter a value that is not in the reference table, you cannot do this.

In addition, if you update or delete a value in a referenced table, you can set it to automatically update the value or delete in cascade any row containing that value.

This is a really great feature using your code.

Answer 5

A foreign key is a field in a table that corresponds to another field in another table. A foreign key restricts data in related tables, which allows MySQL to maintain referential integrity.

Let's look at the following database schema

We have two tables: customers and orders. Each customer has zero or more orders, and each order belongs to only one customer. The relationship between the customer table and the order table is unique, and it is established by a foreign key in the order table specified in the customerNumber field. The customerNumber field in the order table refers to the customerNumber primary key field in the customer table.

The customer table is called the parent table or reference table, and the order table is called the child table or reference table.

A foreign key can be a column or a set of columns. Columns in a child table often refer to primary key columns in a parent table.

A table can have more than one foreign key, and each foreign key in a child table can refer to another parent table.