这是数据库外键定义的一个可选项,用来设置当主键表中的被参考列的数据发生变化时,外键表中响应字段的变换规则的。update 则是主键表中被参考字段的值更新,delete是指在主键表中删除一条记录:
on update 和 on delete 后面可以跟的词语有四个
no action , set null , set default ,cascade
no action 表示 不做任何操作,
set null 表示在外键表中将相应字段设置为null
set default 表示设置为默认值(restrict)
on update 和 on delete 后面可以跟的词语有四个
no action , set null , set default ,cascade
no action 表示 不做任何操作,
set null 表示在外键表中将相应字段设置为null
set default 表示设置为默认值(restrict)
cascade 表示级联操作,就是说,如果主键表中被参考字段更新,外键表中也更新,主键表中的记录被删除,外键表中改行也相应删除
以下是4张表:
-
CREATE TABLE `groups` (
-
`groupid` bigint( 20) unsigned NOT NULL,
-
`name` varchar( 64) NOT NULL DEFAULT '',
-
`internal` int( 11) NOT NULL DEFAULT '0',
-
`flags` int( 11) NOT NULL DEFAULT '0',
-
PRIMARY KEY ( `groupid`),
-
KEY `groups_1` ( `name`)
-
) ENGINE= InnoDB DEFAULT CHARSET=utf8;
-
CREATE TABLE `scripts` (
-
`scriptid` bigint( 20) unsigned NOT NULL,
-
`name` varchar( 255) NOT NULL DEFAULT '',
-
`command` varchar( 255) NOT NULL DEFAULT '',
-
`host_access` int( 11) NOT NULL DEFAULT '2',
-
`usrgrpid` bigint( 20) unsigned DEFAULT NULL,
-
`groupid` bigint( 20) unsigned DEFAULT NULL,
-
`description` text NOT NULL,
-
`confirmation` varchar( 255) NOT NULL DEFAULT '',
-
`type` int( 11) NOT NULL DEFAULT '0',
-
`execute_on` int( 11) NOT NULL DEFAULT '1',
-
PRIMARY KEY ( `scriptid`),
-
KEY `scripts_1` ( `usrgrpid`),
-
KEY `scripts_2` ( `groupid`),
-
CONSTRAINT `c_scripts_1` FOREIGN KEY ( `usrgrpid`) REFERENCES `usrgrp` ( `usrgrpid`) ON DELETE CASCADE ON UPDATE CASCADE,
-
CONSTRAINT `c_scripts_2` FOREIGN KEY ( `groupid`) REFERENCES `groups` ( `groupid`)
-
) ENGINE= InnoDB DEFAULT CHARSET=utf8;
-
CREATE TABLE `users_groups` (
-
`id` bigint( 20) unsigned NOT NULL,
-
`usrgrpid` bigint( 20) unsigned NOT NULL,
-
PRIMARY KEY ( `id`),
-
UNIQUE KEY `users_groups_1` ( `usrgrpid`),
-
CONSTRAINT `c_users_groups_1` FOREIGN KEY ( `usrgrpid`) REFERENCES `usrgrp` ( `usrgrpid`) ON DELETE CASCADE ON UPDATE CASCADE
-
) ENGINE= InnoDB DEFAULT CHARSET=utf8;
-
CREATE TABLE `usrgrp` (
-
`usrgrpid` bigint( 20) unsigned NOT NULL,
-
`name` varchar( 64) NOT NULL DEFAULT '',
-
`gui_access` int( 11) NOT NULL DEFAULT '0',
-
`users_status` int( 11) NOT NULL DEFAULT '0',
-
`debug_mode` int( 11) NOT NULL DEFAULT '0',
-
PRIMARY KEY ( `usrgrpid`),
-
KEY `usrgrp_1` ( `name`)
-
) ENGINE= InnoDB DEFAULT CHARSET=utf8;
![](https://i-blog.csdnimg.cn/blog_migrate/639fb336c6ec4a4ed4586bee8d7b1ae1.png)
1.
users_groups表引用外键 usrgrp.usrgrpid 并设置 ON DELETE CASCADE ON UPDATE CASCADE
scripts表引用外键 usrgrp.usrgrpid 并设置 ON DELETE CASCADE ON UPDATE CASCADE所以对已引用usrgrp.usrgrpid字段的修改对应引用的表也会修改
2.
scripts.groupid 引用groups.groupid 没有设置操作关联属性
CONSTRAINT `c_scripts_2` FOREIGN KEY (`groupid`) REFERENCES `groups` (`groupid`)
对已引用groups.grouppid字段不可修改
出处:https://blog.csdn.net/u013636377/article/details/51313669