on sql Foreign key

Question

有人可以帮助我在SQL我使用Oracle SQL*plus

1. 创建如下表:

表Student:

• stdNo CHAR(5)这是主键
• lastname VARCHAR(25)必须不为空
• givennames VARCHAR(50)必须不为Null
• 部门CHAR (4)

Table Course:

• courseID CHAR(8)这是主键
• courseTitle VARCHAR(50)必须唯一且不为空
• 成本十进制(6,2)确保成本大于或等于零
• Credits INT确保Credits在0到200之间。默认值是2

表Semester:

• semesterID CHAR(5)这是主键
• semesterCode INT确保semesterCode在1和4之间
• 年度INT确保年度在2000至9999之间

Table Register:

• stdNo CHAR(5)在Student表中引用stdNo的外键，On update cascade On delete cascade
• courseID CHAR(8) Course表中指向courseID的外键，打开更新cascade打开删除cascade
• semesterID CHAR(5)在学期表中引用的外键，On update cascade On delete cascade
• 年级CHAR (2)

• 标记DECIMAL(4,2)标记应在0.00和100.00之间

• 主键(stdNo, courseID, semesterID)

这里在SQL(我使用Oracle SQL*plus) ..表STD，学期与我一起工作，但表COURSE我不知道如何将默认值设置为2，表REGISTER根本无法与我一起工作:(

CREATE TABLE STD 
(
    STDNO CHAR(5) PRIMARY KEY,
    LASTNAME VARCHAR(25) NOT NULL,
    GIVENNAME VARCHAR(50) NOT NULL,
    DEPT CHAR(4)
);
CREATE TABLE SEMESTER 
(
    SEMESTERID CHAR(5) PRIMARY KEY,
    SEMESTERCODE INT CHECK(SEMESTERCODE BETWEEN 1 AND 4),
    YEARS INT CHECK(YEARS BETWEEN 2000 AND 9999)
);
CREATE TABLE COURSE 
(
    COURSEID CHAR(8) PRIMARY KEY,
    COURSETITLE VARCHAR(50) NOT NULL UNIQUE,
    COST DECIMAL(6,2) CHECK(COST >= 0),
    CREDITS INT CHECK(CREDITS BETWEEN 0 AND 200)
);
CREATE TABLE REGISTER 
(
     STDNO CHAR(5)
          FOREIGN KEY REFERENCES STD(STDNO) 
             ON UPDATE CASCADE ON DELETE CASCADE,
     COURSEID CHAR(5) 
          FOREIGN KEY REFERENCES COURSE(COURSEID) 
             ON UPDATE CASCADE ON DELETE CASCADE,
     SEMESTERID CHAR(5)
          FOREIGN KEY REFERENCES SEMESTER(SEMESTERID) 
             ON UPDATE CASCADE ON DELETE CASCADE,
     GRADE CHAR(2),
     MARK DECIMAL(4,2) CHECK(MARK BETWEEN 0.00 AND 100.0),
     PRIMARYKEY(STDNO,COURSEID,SEMESTERID)
);

Answer 1

请在下面找到解决方案。

1. COURSE -请使用下面的脚本为CREDITS列设置默认值

   创建表课程（Courseid char(8)主键，课程名称varchar(50)不空唯一，Cost decimal (6,2) check (Cost>= 0)，信用值int default 2 check(信用值在0到200之间));

2. REGISTER - Oracle不允许带有ON UPDATE CASCADE的外键约束。所以你必须使用触发器。在下面找到您可以使用的脚本，

   创建表寄存器（STDNO CHAR (5),Courseid char(8)，——将数据类型更新为与主表相同SEMESTERID CHAR (5),年级CHAR (2),标记十进制(4,2)检查(标记在0.00和100.0之间)，约束register_pk主键(STDNO,COURSEID,SEMESTERID)，约束register_fk1外键(STDNO)引用STD(STDNO)在delete cascade上，约束register_fk2外键(COURSEID)引用课程(COURSEID)在delete cascade上，约束register_fk3外键(SEMESTERID)引用学期(SEMESTERID)On delete cascade

   );

   创建或替换触发器cascade_stdno_update更新后的标准，在标准对于每一行开始更新注册SET stdno =:NEW.stdnoWHERE stdno =:OLD.stdno;结束;/

   创建或替换触发器cascade_courseid_update课程id更新后对于每一行开始更新注册SET courseid =:NEW.courseidWHERE courseid =:OLD.courseid;结束;/

   创建或替换触发器cascade_semesterid_update学期更新后的学期对于每一行开始更新注册SET semesterid =:NEW.semesteridWHERE semesterid =:OLD.semesterid;结束;/