『ORACLE』 约束实验(11g)
来源:互联网 发布:诸暨行知小学怎么样 编辑:程序博客网 时间:2024/06/03 11:09
create table student
(
sno NUMBER(5) not null,
sname VARCHAR2(20),
idcard VARCHAR2(18),
createtime DATE default SYSDATE,
nno NUMBER(2),
constraint PK_STUDENT primary key (sno)
);
create table course
(
cno NUMBER(5) not null,
cname VARCHAR2(20),
constraint PK_COURSE primary key (cno)
);
create table nativeplace
(
nno NUMBER(2) not null,
np_name VARCHAR2(20),
constraint PK_NATIVEPLACE primary key (nno)
);
create table sc
(
sno NUMBER(5) not null,
cno NUMBER(5) not null,
grade NUMBER(4,1),
constraint PK_SC primary key (sno, cno)
);
alter table sc add constraint fk_sc_sno foreign key (sno) references student (sno) on delete cascade;
alter table sc add constraint fk_sc_cno foreign key (cno) references course (cno);
alter table student add constraint fk_student foreign key (nno) references nativeplace (nno) on delete set null;
insert into nativeplace values(1,'heilongjiang');
insert into nativeplace values(2,'jilin');
insert into nativeplace values(3,'liaoning');
insert into nativeplace values(4,'beijing');
insert into course VALUES(1,'Oracle');
insert into course VALUES(2,'Java');
insert into course VALUES(3,'C++');
insert into student values(1,'zhangsan','123456789123456780',default,1);
insert into student values(2,'lisi','123456789123456781',default,2);
insert into student values(3,'wangwu','123456789123456782',default,3);
insert into student values(4,'zhaoliu','123456789123456783',default,4);
insert into sc values(1,1,91);
insert into sc values(1,2,92);
insert into sc values(1,3,93);
insert into sc values(2,1,88);
insert into sc values(2,2,77);
insert into sc values(2,3,99);
insert into sc values(3,1,65);
insert into sc values(3,2,75);
insert into sc values(3,3,85);
insert into sc values(4,1,80);
insert into sc values(4,2,88);
insert into sc values(4,3,89);
commit;
alter table sc drop constraint fk_sc_sno;
alter table sc drop constraint fk_sc_cno;
alter table student drop constraint fk_student;
drop table course cascade constraints;
drop table nativeplace cascade constraints;
drop table sc cascade constraints;
drop table student cascade constraints;
(
sno NUMBER(5) not null,
sname VARCHAR2(20),
idcard VARCHAR2(18),
createtime DATE default SYSDATE,
nno NUMBER(2),
constraint PK_STUDENT primary key (sno)
);
create table course
(
cno NUMBER(5) not null,
cname VARCHAR2(20),
constraint PK_COURSE primary key (cno)
);
create table nativeplace
(
nno NUMBER(2) not null,
np_name VARCHAR2(20),
constraint PK_NATIVEPLACE primary key (nno)
);
create table sc
(
sno NUMBER(5) not null,
cno NUMBER(5) not null,
grade NUMBER(4,1),
constraint PK_SC primary key (sno, cno)
);
alter table sc add constraint fk_sc_sno foreign key (sno) references student (sno) on delete cascade;
alter table sc add constraint fk_sc_cno foreign key (cno) references course (cno);
alter table student add constraint fk_student foreign key (nno) references nativeplace (nno) on delete set null;
insert into nativeplace values(1,'heilongjiang');
insert into nativeplace values(2,'jilin');
insert into nativeplace values(3,'liaoning');
insert into nativeplace values(4,'beijing');
insert into course VALUES(1,'Oracle');
insert into course VALUES(2,'Java');
insert into course VALUES(3,'C++');
insert into student values(1,'zhangsan','123456789123456780',default,1);
insert into student values(2,'lisi','123456789123456781',default,2);
insert into student values(3,'wangwu','123456789123456782',default,3);
insert into student values(4,'zhaoliu','123456789123456783',default,4);
insert into sc values(1,1,91);
insert into sc values(1,2,92);
insert into sc values(1,3,93);
insert into sc values(2,1,88);
insert into sc values(2,2,77);
insert into sc values(2,3,99);
insert into sc values(3,1,65);
insert into sc values(3,2,75);
insert into sc values(3,3,85);
insert into sc values(4,1,80);
insert into sc values(4,2,88);
insert into sc values(4,3,89);
commit;
alter table sc drop constraint fk_sc_sno;
alter table sc drop constraint fk_sc_cno;
alter table student drop constraint fk_student;
drop table course cascade constraints;
drop table nativeplace cascade constraints;
drop table sc cascade constraints;
drop table student cascade constraints;
阅读全文
0 0
- 『ORACLE』 约束实验(11g)
- 『ORACLE』 内置约束(11g)
- Oracle 11g系列:约束
- Oracle 11g学习笔记--约束(constraint )的使用
- 『ORACLE』SPM(下)-baseline实验(11g)
- oracle 11g logminer实验
- oracle 11g logminer实验
- 静默安装oracle 11g 【实验笔记】
- Oracle 11g 手工创建数据库实验
- 『ORACLE』 DBLINK(11g)
- Oracle 11g 第十章知识点总结——约束
- 『ORACLE』Oracle GoldenGate搭建(11g)
- 『ORACLE』安装oracle(11g)
- Oracle 10g Flashback实验
- oracle 11g 表空间迁移 实验过程
- oracle 11g实验五——触发器的使用
- oracle 11G 实验 in 和 exists 区别
- Oracle 11g RAC常用检查操作指令实验
- 51Nod-1574-排列转换
- ubuntu+vim+opencv+cmake环境搭建
- MATLAB学习笔记
- HNOI2017总结
- 抽象类和接口的相同点和不同点,学习笔记
- 『ORACLE』 约束实验(11g)
- Linux学习笔记--常用知识
- C++编译OpenCV生成动态库问题解决
- 一句话概括下spring框架及spring cloud框架主要组件
- Excel常用快捷键大全
- Arraylist的用法
- spring的scope理解与自定义scope
- as86汇编语法
- 虚拟机安装centos 6.5 basicserver 配置联网