对于2,需要使用on delete set null建立外键约束。...实验: create table dept_test (deptno number(10) not null, deptname varchar2(30) not null, constraint...set null; insert into dept_test values(1,'销售部'); insert into dept_test values(2,'财务部'); insert into...对于3,需要使用on delete cascade建立外键约束。...cascade; delete from dept_test where deptno = 1; 1 row deleted.
update 则是主键表中被参考字段的值更新,delete是指在主键表中删除一条记录: on update 和 on delete 后面可以跟的词语有四个:no action , set null ,...no action 表示 不做任何操作, set null 表示在外键表中将相应字段设置为null set default 表示设置为默认值(restrict) (1)on delete cascade...如果没有使用`on delete/update cascade`,不能删除或更新父表数据,当删除父表的数据时候报错!...上述on delete cascade换成on update cascade,可以发现只能更新父表的主键,同时父子表数据都会被更新,但是在子表的外键上做更新操作无效!...如果没有使用on delete/update cascade,不能删除或更新父表数据。
| SET NULL | NO ACTION | SET DEFAULT 该语法可以在 CREATE TABLE 和 ALTER TABLE 时使用,如果不指定CONSTRAINT symbol,MYSQL...ON DELETE、ON UPDATE表示事件触发限制,可设参数: RESTRICT(限制外表中的外键改动) CASCADE(跟随外键改动) SET NULL(设空值) SET DEFAULT(设默认值...SET NULL:表示父表进行更新和删除的时候,子表的对应字段被设为NULL 4.案例演示 以CASCADE(级联)约束方式 1....(id), foreign key(country_id) references country(id) on delete cascade on update cascade, ); 4....on update set null, ); 2.
| cascade | set null | on action }] [on update {restrict | cascade | set null | on action...}] 该语法可以在create table 和 alter table时使用,如果不指定 constraint 外键名 ,mysql会自动生成一个名字,可以通过 show create...cascade on update cascade); 解除外键:alter table 表名 drop foreign key 外键名; 注意:删除外键后发现...cascade: on delete cascade on update cascade 当主表删除记录或更改被参照字段的值时,从表会级联更新 :这个比较危险,容易级联把数据都删除...set null: on delete set null on update set null 当主表删除记录时,从表外键字段值变成 null 当主表更新主键字段值时,
[ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET...CASCADE: 从父表中删除或更新对应的行,同时自动的删除或更新自表中匹配的行。ON DELETE CANSCADE和ON UPDATE CANSCADE都被InnoDB所支持。 2....ON DELETE SET NULL和ON UPDATE SET SET NULL都被InnoDB所支持。 3. NO ACTION: InnoDB拒绝删除或者更新父表。 4....cascade on update cascade; // 执行删除主表数据 mysql> select * from userInfo; +----+-----------+------+ | id...set null ON UPDATE set null; ERROR 1215 (HY000): Cannot add foreign key constraint 结论:Innodb存储引擎暂不支持
author(id) -> on delete cascade -> on update cascade, -> constraint fk_book...foreign key(book_id) references book(id) -> on delete cascade -> on update cascade...) references author(id) on delete cascade on update cascade, foreign key(book_id) references...book(id) on delete cascade on update cascade, primary key(author_id, book_id) );...customer(id) #外键的字段一定要保证unique on delete cascade on update cascade ); 实践: mysql> create table customer
(0.00 sec) mysql:yeyztest ::>>delete from fk_test_1 where id=; ERROR (): Cannot delete or update...既然delete不成功,试试update, mysql:yeyztest ::>>update fk_test_1 set id= where id=; ERROR (): Cannot...父表的情况: cascade,set null,no action,restrict update父表的情况: cascade,set null,no action,restrict 其中 restrict...是默认操作,它表示拒绝父表删除或者修改外键已经被子表所依赖的列,这是最安全的设置; cascade表示在父表发生删除的时候直接删除子表的记录,这是最危险的设置; set null表示父表删除的时候,对子表进行...{cascade | set null | no action| restrict}] [on update {cascade | set null | no action| restrict}]
[ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET...ON DELETE SET NULL和ON UPDATE SET SET NULL都被InnoDB所支持。 3. NO ACTION: InnoDB拒绝删除或者更新父表。 4....指定RESTRICT(或者NO ACTION)和忽略ON DELETE或者ON UPDATE选项的效果是一样的。 5. SET DEFAULT: InnoDB目前不支持。...前一种情况,在外键定义中,我们使用ON UPDATE CASCADE ON DELETE RESTRICT; 后一种情况,可以使用ON UPDATE CASCADE ON DELETE CASCADE...CASCADE ON UPDATE CASCADE 此时如下操作: 代码如下 复制代码 --此时article中的记录也会被删除 delete from category where
, #关联的字段,一定要保证是可以重复的 foreign key(press_id) references press(id) on delete cascade on update cascade..., book_id int not null, foreign key(author_id) references author(id) on delete cascade on update cascade..., foreign key(book_id) references book(id) on delete cascade on update cascade, unique (author_id,book_id...key(customer_id) references customer(id) #外键的字段一定要保证unique on delete cascade on update cascade );...cascade on update cascade, foreign key(新表_字段名2) references 表1(字段名) on delete cascade on update cascade
在MySQL 3.23.44版本后,InnoDB引擎类型的表支持了外键约束。...[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}] [ON UPDATE {RESTRICT | CASCADE...| SET NULL | NO ACTION | SET DEFAULT}] 该语法可以在 CREATE TABLE 和 ALTER TABLE 时使用,如果不指定CONSTRAINT symbol,...MYSQL会自动生成一个名字。...ON DELETE、ON UPDATE表示事件触发限制,可设参数: RESTRICT(限制外表中的外键改动) CASCADE(跟随外键改动) SET NULL(设空值) SET DEFAULT(设默认值
> delete from teacher where id=1; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign...teacher(id) on update cascade on delete cascade; Query OK, 6 rows affected (0.05 sec) Records: 6 Duplicates...: 0 Warnings: 0 7、验证cascade级联行为 验证1:我修改《teacher》表中id=1的数据改为id=4 mysql> update teacher set id=4...teacher(id) on update set null on delete set null; Query OK, 6 rows affected (0.04 sec) Records: 6...Duplicates: 0 Warnings: 0 验证1:更新《teacher》表中id=1的数据,改为id=4 mysql> update teacher set id=4 where id
, dpt_id int, constraint fk_name foreign key(dpt_id) references department(id) on delete cascade on update..., foreign key(press_id) references press(id) on delete cascade on update cascade ); insert into press...(author_id) references author(id) on delete cascade on update cascade, constraint fk_book foreign key...(book_id) references book(id) on delete cascade on update cascade, primary key(author_id,book_id) );...key(customer_id) references customer(id) #外键的字段一定要保证unique on delete cascade on update cascade );
> set session auto_increment_increment=5; 设置全局级别步长 注意:设置全局级别步长需要退出本次会话再次登录才生效 mysql> set global auto_increment_increment...mysql> set global auto_increment_offset=3; 不连续主键 +----+--------------+------------+ | id | title...4.更新数据 强行更新被关联表中的记录的主键也会报错,因为关联表中还存在一些记录的外键指向被关联表 update dep set id=333 where id=3; 解决方案 创建关联表(员工表)的时候增加删除同步和更新同步...on delete cascade on update cascade mysql> create table emp( -> id int not null primary key,...cascade -> on update cascade -> ); Query OK, 0 rows affected (0.02 sec) 注意:下图红框中换行无逗号 因为同属于一句
最近有开始做一个实验室管理系统,因为分了几个表进行存储・所以要维护表间的关联・・研究了一下MySQL的外键。...和on update , 可设参数cascade(跟随外键改动), restrict(限制外表中的外键改动),set Null(设空值),set Default(设默认值),[默认]no action...cascade on update cascade); 说明:把id列 设为外键 参照外表outTable的id列 当外键的值删除 本表中对应的列筛除 当外键的值改变 本表中对应的列值改变。...代码如下: create table temp( id int, name char(20), foreign key(id) references outTable(id) on delete cascade...on update cascade); 缺点:在对MySQL做优化的时候类似查询缓存,索引缓存之类的优化对InnoDB类型的表是不起作用的,还有在数据库整体架构中用得同步复制也是对InnoDB类型的表不生效的
需要注意的地方是如果你的语句是update 或 delete的方式进行如上的语句操作,尤其是子查询的方式,进行数据的修改,那么以上的工作将无法进行,所以对于在MYSQL 8 中的数据修改还是建议分两步走...` (`store_id`) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=4582 DEFAULT CHARSET...`inventory` (`inventory_id`) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT `fk_rental_staff` FOREIGN...KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB...` (`store_id`) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=600 DEFAULT CHARSET
节选择《Netkiller MySQL 手札》 13.9....SET @newid=0; UPDATE mytable SET id = (SELECT @newid:=@newid+ 1); 使用max()查看最大值,然后使用 alter修改起始位置。...CASCADE ON DELETE CASCADE, CREATE TABLE `contact` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT...CASCADE ON DELETE CASCADE, CONSTRAINT `FK_group_has_contact_group` FOREIGN KEY (`group_id`) REFERENCES...`group` (`id`) ON UPDATE CASCADE ON DELETE CASCADE ) COMMENT='N:M' COLLATE='utf8_general_ci' ENGINE=
, dpt_id int, constraint fk_name foreign key(dpt_id) references department(id) on delete cascade on update..., foreign key(press_id) references press(id) on delete cascade on update cascade ); insert into press...(author_id) references author(id) on delete cascade on update cascade, constraint fk_book foreign key...(book_id) references book(id) on delete cascade on update cascade, primary key(author_id,book_id) );...customer(id) #外键的字段一定要保证unique on delete cascade on update cascade ); #增加客户 insert into customer(name
`last_update` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY...RESTRICT ON UPDATE CASCADE, CONSTRAINT `fk_payment_rental` FOREIGN KEY (`rental_id`) REFERENCES `rental...` (`rental_id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_payment_staff` FOREIGN KEY (`staff_id...`) REFERENCES `staff` (`staff_id`) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT...: YES Expression: NULL 4 rows in set (0.0112 sec) 用户通过创建索引改善查询性能,例如,查询一个索引字段的值,可以快速地返回包含该值的行,如果查询非索引字段的值
row in set (0.01 sec) mysql> show create table city\G *************************** 1. row *********...set country_id=100 where country_id=1; ERROR 1451 (23000): Cannot delete or update a parent row: a...然后自己又重新看了下书本,发现自己的sql语句中没有innodb的外键约束方式(cascade,set null,no action,restrict),感觉这就是自己出问题的地方。...CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.02 sec) 在大家(老师和网友)的帮助下终于搞定了,做法先drop...CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
领取专属 10元无门槛券
手把手带您无忧上云