mysql innodb 外键_MYSQL INNODB 外键约束 主表和从表

外键中主表与从表的定义

主表(父表)

在数据库中建立的表格即Table，其中存在主键(primary key)用于与其它表相关联，并且作为在主表中的唯一性标识。

从表(子表)

以主表的主键(primary key)值为外键 (Foreign Key)的表，可以通过外键与主表进行关联查询。从表与主表通过外键进行关联查询。

关系及用法概述

从表数据依赖于主表，一般最后查询数据时把主表与从表进行关联查询。

例如：一般来说，产品表中的产品分类属性依赖于产品分类表，那么对于产品分类属性这个外键来说，产品分类表为主表，产品信息表为从表。

外键约束对从表表的含义: 如果在主表中找不到候选键,则不允许在从表上进行insert/update 外键约束对主表的含义: 在主表上进行update/delete以更新或删除在从表中有一条或多条对应匹配行的候选键时,主表的行为取决于：在定义从表的外键时指定的on update/on delete子句, InnoDB支持５种方式, 分列如下

. cascade方式

在主表上update/delete记录时，同步update/delete掉从表的匹配记录 On delete cascade从mysql3.23.50开始可用; on update cascade从mysql4.0.8开始可用 . set null方式

在主表上update/delete记录时，将从表上匹配记录的列设为null 要注意从表的外键列不能为not null On delete set null从mysql3.23.50开始可用; on update set null从mysql4.0.8开始可用 . No action方式

如果主表中有匹配的记录,则不允许对从表对应候选键进行update/delete操作 这个是ANSI SQL-92标准,从mysql4.0.8开始支持 . Restrict方式

同no action, 都是立即检查外键约束 . Set default方式 解析器认识这个action,但Innodb不能识别，不知道是什么意思．．． 注意：trigger不会受外键cascade行为的影响,即不会解发trigger

例子：

ALTER TABLE `quotations` ADD CONSTRAINT `quo_user` FOREIGN KEY (`designer`) REFERENCES `users` (`user_name`) ON DELETE NO ACTION;

例子如：

CREATETABLE`country` (

`country_id` smallint(5) unsignedNOTNULLauto_increment,

`country` varchar(50)NOTNULL,

`last_update` timestampNOTNULLdefaultCURRENT_TIMESTAMPonupdateCURRENT_TIMESTAMP,

PRIMARYKEY(`country_id`)

) ENGINE=InnoDB DEFAULTCHARSET=utf8;

CREATETABLE`city` (

`city_id` smallint(5) unsignedNOTNULLauto_increment,

`city` varchar(50)NOTNULL,

`country_id` smallint(5) unsignedNOTNULL,

`last_update` timestampNOTNULLdefaultCURRENT_TIMESTAMPonupdateCURRENT_TIMESTAMP,

PRIMARYKEY(`city_id`),

KEY`idx_fk_country_id` (`country_id`),

CONSTRAINT`fk_city_country`FOREIGNKEY(`country_id`)REFERENCES`country` (`country_id`)ONDELETERESTRICTONUPDATECASCADE

) ENGINE=InnoDB DEFAULTCHARSET=utf8;

insertintocountry(country_id, country)values(1,"china");

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

Sql代码 约束不能删除

mysql>deletefromcountrywherecountry_id=1;

ERROR 1451 (23000): Cannot deleteorupdatea parent row: aforeignkeyconstraintfails (`test/city`,CONSTRAINT`fk_city_country`FOREIGNKEY(`country_id`) R

EFERENCES `country` (`country_id`))

因为DELETERESTRICT，从表有，删除主表时提示出错

Sql代码 约束可以级联更新

mysql>updatecountrysetcountry_id=1000wherecountry_id=1;

Query OK, 1 row affected (0.01 sec)

Rowsmatched: 1 Changed: 1 Warnings: 0

mysql> select*fromcountry;

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

| country_id | country | last_update |

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

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

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

1 row inset(0.00 sec)

mysql> select*fromcity;

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

| city_id | city | country_id | last_update |

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

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

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

1 row inset(0.00 sec)

Sql代码 

mysql> showtablestatuslike'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`)ONUPDATE

1 row inset(0.00 sec)