mysql外键约束innodb_Mysql InnoDB表外键约束

CREATE TABLE `country` (

`country_id` smallint(5) unsigned NOT NULL auto_increment,

`country` varchar(50) NOT NULL,

`last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

PRIMARY KEY (`country_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `city` (

`city_id` smallint(5) unsigned NOT NULL auto_increment,

`city` varchar(50) NOT NULL,

`country_id` smallint(5) unsigned NOT NULL,

`last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

PRIMARY KEY (`city_id`),

KEY `idx_fk_country_id` (`country_id`),

CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON DELETE RESTRICT ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into country(country_id, country) values(1, "china");

insert into city(city_id, city, country_id) values(200, "wenzhou", 1);

mysql> delete from country where country_id=1;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test/city`, CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) R

EFERENCES `country` (`country_id`))

因为DELETE RESTRICT，子表有，删除父表时提示出错

mysql> update country set country_id=1000 where country_id=1;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from country;

+------------+---------+---------------------+

| country_id | country | last_update |

+------------+---------+---------------------+

| 1000 | china | 2009-01-10 22:46:42 |

+------------+---------+---------------------+

1 row in set (0.00 sec)

mysql> select * from city;

+---------+---------+------------+---------------------+

| city_id | city | country_id | last_update |

+---------+---------+------------+---------------------+

| 200 | wenzhou | 1000 | 2009-01-10 22:45:38 |

+---------+---------+------------+---------------------+

1 row in set (0.00 sec)

mysql> show table status like 'city' \G;

*************************** 1. row ***************************

Name: city

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 1

Avg_row_length: 16384

Data_length: 16384

Max_data_length: 0

Index_length: 16384

Data_free: 0

Auto_increment: 201

Create_time: 2009-01-10 22:36:53

Update_time: NULL

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment: InnoDB free: 4096 kB; (`country_id`) REFER `test/country`(`country_id`) ON UPDATE

1 row in set (0.00 sec)