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;
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字段不可修改