Briefly describe the use and description of MySQL primary key and foreign key

1. Foreign key constraints

MySQL uses foreign key constraints to ensure the integrity and accuracy of data between tables.

What is a foreign key:

Primary key: uniquely identifies a record, cannot be duplicated, cannot be empty, and is used to ensure data integrity

Foreign key: It is the primary key of another table. Foreign keys can have duplicates and can be empty. They are used to establish connections with other tables. So, if we talk about foreign keys, there must be at least two tables involved. For example, the following two tables:

Conditions for using foreign keys:

1. Both tables must be InnoDB tables. MyISAM tables do not support foreign keys for the time being (it is said that future versions may support it, but at least not currently);
2. The foreign key column must have an index. MySQL 4.1.2 and later versions will automatically create an index when creating a foreign key, but if you use an earlier version, you need to explicitly create an index.
3. The columns of the two tables in the foreign key relationship must have similar data types, that is, columns that can be converted to each other, such as int and tinyint, but not int and char;

Benefits of foreign keys: they can associate two tables, ensure data consistency, and implement some cascading operations;

The definition syntax of a foreign key is:

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

This syntax can be used in CREATE TABLE and ALTER TABLE. If you do not specify the CONSTRAINT symbol, MYSQL will automatically generate a name.
ON DELETE and ON UPDATE indicate event triggering restrictions, and you can set parameters:

• RESTRICT (restrict changes to foreign keys in foreign tables)
• CASCADE (follow foreign key changes)
• SET NULL
• SET DEFAULT (Set the default value)
• NO ACTION (no action, default)

Simple demonstration use

Create two tables, dage and xiaodi. The big brother table is the primary key and the little brother table is the foreign key.

Create a table:

CREATE TABLE `dage` (
 `id` int(11) NOT NULL auto_increment,
 `name` varchar(32) default '',
 PRIMARY KEY (`id`))
 ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `xiaodi` (
 `id` int(11) NOT NULL auto_increment,
 `dage_id` int(11) default NULL,
 `name` varchar(32) default '',
 PRIMARY KEY (`id`),
 KEY `dage_id` (`dage_id`),
 CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;

Insert a big brother:

mysql> insert into dage(name) values('Causeway Bay');
Query OK, 1 row affected (0.01 sec)
mysql> select * from dage;
+----+--------+
| id | name |
+----+--------+
| 1 | Causeway Bay |
+----+--------+
1 row in set (0.00 sec)

Insert a little brother:

mysql> insert into xiaodi(dage_id,name) values(1,'Causeway Bay_Little Brother A');
Query OK, 1 row affected (0.02 sec)

mysql> select * from xiaodi;
+----+---------+--------------+
| id | dage_id | name |
+----+---------+--------------+
| 1 | 1 | Causeway Bay_Little Brother A |
+----+---------+--------------+

Delete the big brother:

mysql> delete from dage where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`bstar/xiaodi`, CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`))

Tip: No, there are restrictions. The big brother has younger brothers under him, he can’t abandon us!

Insert a new child:

mysql> insert into xiaodi(dage_id,name) values(2,'旺角_小弟A');              
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bstar/xiaodi`, CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`))

Hint: Boy, you want to rebel! You don’t have a big brother yet!

Add event trigger restrictions to foreign key constraints:

mysql> show create table xiaodi;

 CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`)

mysql> alter table xiaodi drop foreign key xiaodi_ibfk_1;
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings:
mysql> alter table xiaodi add foreign key(dage_id) references dage(id) on delete cascade on update cascade;
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0

Try to delete the big brother again:

mysql> delete from dage where id=1;
Query OK, 1 row affected (0.01 sec)

mysql> select * from dage;
Empty set (0.01 sec)

mysql> select * from xiaodi;
Empty set (0.00 sec)

Oops, this time the corresponding younger brother is gone, there is no way, who told you to on delete cascade (cascade restriction) with me!

Points to note

• Does the table name correspond?
• Do the fields in the table correspond?
• Is the key associated with the foreign key the primary key of another table?

This is the end of this article about the brief introduction to the use and description of MySQL primary keys and foreign keys. For more relevant MySQL primary keys and foreign keys, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!