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)