Shulou(Shulou.com)06/03 Report--

This article mainly explains "the function and usage of MySQL primary key and foreign key". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn the function and usage of MySQL primary key and foreign key.

Catalogue

I. Foreign key constraint

What is a foreign key:

Conditions for using foreign keys:

Definition syntax for foreign keys:

Simple demonstration of use

I. Foreign key constraint

MySQL ensures the integrity and accuracy of the data between tables through foreign key constraints.

What is a foreign key:

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

Foreign key: is the primary key of another table. The foreign key can be duplicated, can be null, and is used to connect with other tables. So, if you talk about foreign keys, at least two tables must be 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 them, but at least not for now)

two。 Foreign key columns must be indexed. Later versions of MySQL 4.1.2 automatically create indexes when creating foreign keys, but need to show build if they are in earlier versions.

3. The columns of two tables in a foreign key relationship must be of similar data types, that is, columns that can be converted into each other, such as int and tinyint, but not int and char.

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

Definition syntax for foreign keys:

[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, and if you don't specify CONSTRAINT symbol,MYSQL, a name is automatically generated.

ON DELETE and ON UPDATE indicate the event trigger limit. You can set parameters:

RESTRICT (restrict foreign key changes in appearance)

CASCADE (follow the foreign key change)

SET NULL (set null)

SET DEFAULT (set default)

NO ACTION (no action, default)

Simple demonstration of use

To make dage and xiaodi tables, the big brother watch is the primary key and the younger brother's watch is the foreign key.

Build 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; Query OK, 1 row affected (0.02 sec) mysql > select * from xiaodi +-+ | id | dage_id | name | +-+ | 1 | 1 | Causeway Bay _ Brother A | +-+

Delete the eldest 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`))

Hint: no, there are restrictions, there are younger brothers below, but you can't leave us alone!

Insert a new brother:

Mysql > insert into xiaodi (dage_id,name) values; 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 a foreign key constraint to the event trigger limit:

Mysql > show create table xiaodi; CONSTRAINT `xiaodi_ibfk_ 1` FOREIGN KEY (`dage`) 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 eldest 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)

Ouch, this time the corresponding younger brother is also gone, there is no way, who let you and me on delete cascade (cascading restrictions)!

Points to pay attention to

Whether the table name corresponds to

Whether the fields of the table correspond

Whether the key associated with the foreign key is the primary key of another table

At this point, I believe you have a deeper understanding of the function and usage of MySQL primary key and foreign key. You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.