MySQL Advanced (1) Primary and Foreign Keys Explanation

1. What is a foreign key:

Primary key: It uniquely identifies a record. It cannot be repeated and is not allowed to be empty. It is used to ensure data integrity.

Foreign key: Yes The primary key and foreign key of another table can have duplicates or null values ​​and are used to establish relationships with other tables. Therefore, if we talk about foreign keys, it must involve at least two tables. For example, the following two tables:

#There are two tables above: department table (dept) and employee table (emp). Id=Dept_id, and Dept_id is the foreign key in the employee table: because employees in the employee table need to know which department they belong to, they can find the corresponding department through the foreign key Dept_id, and then find various field information in the department table. So that the two are related. Therefore, the foreign key must be created in the slave table to find the connection with the master table; the slave table is responsible for maintaining the relationship between the two.

2. The use of foreign keys needs to meet the following conditions: (The concept of InnoDB is involved here)

1. Both tables must be InnoDB tables, and they have no temporary tables.

Note: InnoDB is the engine of the database. There are two common MySQL engines: InnoDB and MyISAM, the latter does not support foreign keys.

2. The corresponding columns to establish a foreign key relationship must have similar InnoDB internal data types.

3. The corresponding column to establish a foreign key relationship must be indexed.

4. If the CONSTRAINT symbol is explicitly given, the symbol must be unique in the database. If not given explicitly, InnoDB will be created automatically.

Interview question: What storage engine does your database use? What's the difference?

Answer: Common ones are MyISAM and InnoDB.

MyISAM: Foreign key constraints are not supported. Transactions are not supported. When importing data in large batches, it will build indexes while inserting data. Therefore, in order to improve execution efficiency, you should disable the index first and then enable the index after the complete import.

InnoDB: supports foreign key constraints and transactions. The indexes are processed separately without referencing the index.

CONSTRAINT symbol: You can give this foreign key constraint a name. With the name, it will be very convenient to find it in the future. If this parameter is not added, the system will automatically assign a name.

FOREIGN KEY: Use field 1 in the table as the field of the foreign key.

REFERENCES: Maps to field 2 of the main table.

The four parameters after ON DELETE: represent the agreement made when deleting records in the main table.

RESTRICT (restriction): If the master table you want to delete has records corresponding to the slave table below it, this master table will not be deleted.

            CASCADE (cascade): If the records in the main table are deleted, all related records in the slave table will be deleted.

SET NULL: Set the foreign key to null.

NO ACTION: Do nothing.

Note: Generally, RESTRICT and CASCADE are used the most.

美文美图

The above is the content of MySQL Advanced (1) Primary and Foreign Keys, more For related content, please pay attention to the PHP Chinese website (www.php.cn)!