CREATE TABLE Student
(
Sno Number (5) is not NULL,
sname VARCHAR2 (),
Idcard VARCHAR2 (),
createtime DATE default sysdate,
Nno Number (2),
constraint Pk_student primary KEY (SNO)
);
CREATE TABLE Course
(
CNO Number (5) is not NULL,
CNAME VARCHAR2 (+),
constraint Pk_course primary KEY (CNO)
);
CREATE TABLE Nativeplace
(
Nno Number (2) is not NULL,
np_name VARCHAR2 (),
constraint Pk_nativeplace primary KEY (Nno)
);
CREATE TABLE SC
(
Sno Number (5) is not NULL,
CNO Number (5) is 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;
"ORACLE" constraint Experiment (11g)