mysql删除外键约束语句_sql 删除带外键约束的表的语句是什么

展开全部

1、创建主键表,test_class,并建立class_id字段为主键;

create table test_class(class_id number, class_name varchar2(20));

-- Create/Recreate indexes

alter table TEST_CLASS

add constraint P_CLASS_ID primary key (CLASS_ID)

using index

tablespace USERS

pctfree 10

initrans 2

maxtrans 255;

349560336d66993af514378a0303bccc.png

2、创建外键表,test_stu,其中字段class_id为test_class表的class_id字段;

create table test_stu(id number, class_id number);

-- Create/Recreate primary, unique and foreign key constraints

alter table TEST_STU

add constraint f_class_id foreign key (CLASS_ID)

references test_class (CLASS_ID) on delete cascade;

e9b8f73eb8915eebb1f19e34ee55f33e.png

3、两张表分别e69da5e887aa3231313335323631343130323136353331333431376532插入记录;

insert into TEST_CLASS values(1001,'语文');

insert into TEST_CLASS values(1002,'数学');

insert into TEST_CLASS values(1003,'英语');

insert into TEST_STU values(1,1001);

insert into TEST_STU values(2,1001);

insert into TEST_STU values(3,1002);

insert into TEST_STU values(4,1003);

2240815041d73044552b553f9720525d.png

4、查询TEST_STU表中的记录;select t.*, rowid from test_stu;

4ed437d984bf24f6b23fbe3beaf98f3a.png

5、删除主表TEST_CLASS中class_id=1001的记录,会发现从表TEST_STU中class_id中的记录也被删除;

delete test_class where class_id = 1001;

commit;

select t.*, t.rowid from TEST_STU t

f28717f7e64fb3a69e1351b952903d46.png

表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
相关推荐
©️2020 CSDN 皮肤主题: 1024 设计师:白松林 返回首页