CREATE TABLE p (id INT,u_id INT,INDEX (id),CONSTRAINT `p_ibfk_1` FOREIGN KEY (u_id) REFERENCES p(id));
u_id必须等于id的值
foreign key (col1) referengs table(col2)
col1与col2可以是不同表,但类型必须相同
删除外键
SHOW CREATE TABLE `aa`.`p`;
CREATE TABLE `p` (
`id` int(11) DEFAULT NULL,
`u_id` int(11) DEFAULT NULL,
KEY `id` (`id`),
KEY `u_id` (`u_id`),
CONSTRAINT `p_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `p` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
ALTER TABLE p DROP FOREIGN KEY p_ibfk_1
---------------------------------------------------------------------------------
定义父表与子表直接的关系
CREATE TABLE `parent` (
`id` INT(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
CREATE TABLE `child` (
`id` INT(11) DEFAULT NULL,
`parent_id` INT(11) DEFAULT NULL,
KEY `par_ind` (`parent_id`),
CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=INNODB DEFAULT CHARSET=utf8
CASCADE:当父表发生DELETE或UPDATE操作时,相应子表中的数据也被DELETE或UPDATE
SET NULL:当父表发生DELETE或UPDATE操作时,相应的子表中的数据被更新为NULL。当然子表中相应的列必须允许NULL值
ON ACTION:当父表发生DELETE或UPDATE操作时,抛出错误,不允许这类操作发生
RESTRICT:当父表发生DELETE或UPDATE操作时,抛出错误,不允许这类操作发生.如果定义外键时没有指定ON DELETE或ON UPDATE,这就是默认的外键设置
修改父表与子表关系
先删除外键
ALTER TABLE child DROP FOREIGN KEY `child_ibfk_1`;
创建外键
ALTER TABLE `child` ADD CONSTRAINT
`child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
阅读(2044) | 评论(0) | 转发(0) |