Shulou(Shulou.com)05/31 Report--

This article is to share with you about how to use foreign keys in MYSQL. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article.

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.

Www.2cto.com

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)

Www.2cto.com

Take an example and briefly demonstrate the use of two tables, dage and xiaodi. The big brother table is the primary key, and the junior watch is the foreign key:

Build a table:

1CREATE TABLE `dage` (

2 `id`int (11) NOT NULL auto_increment

3 `name`varchar (32) default''

4 PRIMARY KEY (`id`)

5) ENGINE=InnoDB DEFAULT CHARSET=latin1

six

7CREATE TABLE `xiaodi` (

8 `id` int (11) NOT NULL auto_increment

9 `dage_ id` int (11) default NULL

10 `name`varchar (32) default''

11 PRIMARY KEY (`id`)

12 KEY `dage_ id` (`dage_ id`)

13 CONSTRAINT `xiaodi_ibfk_ 1` FOREIGN KEY (`dage_ id`) REFERENCES `dage` (`id`)

14) ENGINE=InnoDB DEFAULT CHARSET=latin1

Insert a big brother:

1 > insert into dage (name) values ('Causeway Bay')

2Query OK, 1 row affected (0.01sec)

3mysql > select * from dage

4. Talk, talk

5 | id | name |

6, talk, talk

7 | 1 | Causeway Bay |

8, talk, talk

91 row in set (0.00 sec)

Www.2cto.com

Insert a little brother:

1mysql > insert into xiaodi (dage_id,name) values (1) 'Causeway Bay _ Brother A')

2Query OK, 1 row affected (0.02 sec)

three

4mysql > select * from xiaodi

5Flowmeter, color, color, price, price, price and price.

6 | id | dage_id | name |

7 pay, talk, talk.

8 | 1 | 1 | Causeway Bay _ Brother A |

9 colors, houses, houses, houses.

Delete the eldest brother:

1mysql > delete from dage where id=1

2ERROR 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`) www.2cto.com

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

Insert a new brother:

1mysql > insert into xiaodi (dage_id,name) values (2jue 'Mong Kok _ Brother A')

2ERROR 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`))

three

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

Add a foreign key constraint to the event trigger limit:

1mysql > show create table xiaodi

2...

3 CONSTRAINT `xiaodi_ibfk_ 1` FOREIGN KEY (`dage_ id`) REFERENCES `dage` (`id`)

4...

5mysql > alter table xiaodi drop foreign key xiaodi_ibfk_1

6Query OK, 1 row affected (0.04 sec)

7Records: 1 Duplicates: 0 Warnings:

8mysql > alter table xiaodi add foreign key (dage_id) references dage (id) on delete cascade on update cascade

9Query OK, 1 row affected (0.04 sec)

10Records: 1 Duplicates: 0 Warnings: 0

Try to delete the eldest brother again:

1mysql > delete from dage where id=1

2Query OK, 1 row affected (0.01sec)

3 www.2cto.com

4mysql > select * from dage

5Empty set (0.01sec)

six

7mysql > select * from xiaodi

8Empty set (0.00 sec)

The above is how to use foreign keys in MYSQL. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.