现在有两张表STUDENT和COURSE,我想建一张表存储选课关系,但是不知道SQL语句出错在哪里,求解释~~STUDENT表CREATE TABLE STUDENT
(SNO CHAR(7) NOT NULL,
CLNO CHAR(7) NOT NULL,
SNAME VARCHAR(8) NOT NULL,
SEX CHAR(2) NOT NULL,
BDATE DATE NOT NULL,
HEIGHT DEC(5,2) DEFAULT 000.00,
PRIMARY KEY(SNO),
FOREIGN KEY(CLNO)
REFERENCES CLAZZ
ON DELETE RESTRICT);
COURSE表CREATE TABLE COURSE
(CNO CHAR(7) NOT NULL,
CNAME VARCHAR(8) NOT NULL,
LHOUR SMALLINT NOT NULL,
CREDIT DEC(1,0) NOT NULL,
SEMESTER CHAR(2) NOT NULL,
PRIMARY KEY(CNO));
选课关系表CRAETE TABLE SC
(SNO CHAR(7) NOT NULL,
CNO CHAR(7) NOT NULL,
GRADE DEC(4,1) DEFAULT NULL,
PRIMARY KEY(SNO, CNO),
FOREIGN KEY(SNO)
REFERENCES STUDENT
ON DELETE CASCADE,
FOREGN KEY(CNO)
REFERENCES COURSE
ON DELETE RESTRICT);
另外,那个 ON DELETE RESTRICT 约束不能加上去,不知道Oracle有没有实现这个功能,不过删了以后还是不能建SC表
(SNO CHAR(7) NOT NULL,
CLNO CHAR(7) NOT NULL,
SNAME VARCHAR(8) NOT NULL,
SEX CHAR(2) NOT NULL,
BDATE DATE NOT NULL,
HEIGHT DEC(5,2) DEFAULT 000.00,
PRIMARY KEY(SNO),
FOREIGN KEY(CLNO)
REFERENCES CLAZZ
ON DELETE RESTRICT);
COURSE表CREATE TABLE COURSE
(CNO CHAR(7) NOT NULL,
CNAME VARCHAR(8) NOT NULL,
LHOUR SMALLINT NOT NULL,
CREDIT DEC(1,0) NOT NULL,
SEMESTER CHAR(2) NOT NULL,
PRIMARY KEY(CNO));
选课关系表CRAETE TABLE SC
(SNO CHAR(7) NOT NULL,
CNO CHAR(7) NOT NULL,
GRADE DEC(4,1) DEFAULT NULL,
PRIMARY KEY(SNO, CNO),
FOREIGN KEY(SNO)
REFERENCES STUDENT
ON DELETE CASCADE,
FOREGN KEY(CNO)
REFERENCES COURSE
ON DELETE RESTRICT);
另外,那个 ON DELETE RESTRICT 约束不能加上去,不知道Oracle有没有实现这个功能,不过删了以后还是不能建SC表
1. 尽量不要用char类型,都用varchar2比较好
2. oracle9i中,没有ON DELETE RESTRICT这种语法,但是有ON DELETE cascade
REFERENCES CLAZZ
这时还没有class表,怎么建外键呀。
CREATE TABLE STUDENT
(SNO CHAR(7) NOT NULL,
CLNO CHAR(7) NOT NULL,
SNAME VARCHAR(8) NOT NULL,
SEX CHAR(2) NOT NULL,
BDATE DATE NOT NULL,
HEIGHT DEC(5,2) DEFAULT 000.00,
PRIMARY KEY(SNO),
FOREIGN KEY(CLNO)
REFERENCES CLAZZ-- 你建了clazz表否 没有是报错的
ON DELETE RESTRICT);还有先建主表 在从表
已经建的表:错误信息:
(SNO VARCHAR2(7) NOT NULL,
CLNO VARCHAR2(7) NOT NULL,
SNAME VARCHAR2(8) NOT NULL,
SEX VARCHAR2(2) NOT NULL,
BDATE DATE NOT NULL,
HEIGHT DEC(5,2) DEFAULT 000.00,
CONSTRAINT PK_STUDENT PRIMARY KEY (SNO));
--班级表
CREATE TABLE CLASS(CLNO VARCHAR2(7),CLASSNAME VARCHAR2(50), PRIMARY KEY (CLNO));
--学生表
CREATE TABLE STUDENT(SNO VARCHAR2(7) NOT NULL, CLNO VARCHAR2(7) NOT NULL,
SNAME VARCHAR2(8) NOT NULL, SEX VARCHAR2(2) NOT NULL, BDATE DATE NOT NULL,
HEIGHT DEC(5,2) DEFAULT 000.00,PRIMARY KEY(SNO), FOREIGN KEY(CLNO) REFERENCES CLASS(CLNO));
--课程表
CREATE TABLE COURSE(CNO VARCHAR2(7),COURSENAME VARCHAR2(50), PRIMARY KEY (CNO));
--选课表
CREATE TABLE SC
(SNO VARCHAR2(7) NOT NULL,
CNO VARCHAR2(7) NOT NULL,
GRADE NUMBER(4,1) DEFAULT NULL,
PRIMARY KEY(SNO, CNO),
FOREIGN KEY(SNO)
REFERENCES STUDENT
ON DELETE CASCADE,
FOREIGN KEY(CNO)
REFERENCES COURSE);
这个是数据库教材上的代码,与具体的DBMS没关,其实原理差不多,所以我想知道这跟Oracle的对应关系。