student_course.sql

来自「这是一本描述JDBC数据库的书籍」· SQL 代码 · 共 59 行

alter table stu_cou_tbl drop constraint FK_STU_COU_REFERENCE_STUDENT;

alter table stu_cou_tbl drop constraint FK_STU_COU_REFERENCE_COURSE;

drop table student_tbl cascade constraints;

drop table course_tbl cascade constraints;

drop table stu_cou_tbl cascade constraints;

drop sequence student_seq;

drop sequence course_seq;

create sequence student_seq increment by 1 start with 1;

create sequence course_seq increment by 1 start with 1;

create table course_tbl (
    id  number(9)   not null,
    no  varchar2(8) not null,
    name    varchar2(30)    not null,
    score   number(2),
    startdate   date,
    enddate     date,
    description varchar2(100),
    constraint PK_COURSE_TBL primary key (id)
);

create table stu_cou_tbl (
    studentId   number(9) not null,
    courseID    number(9) not null,
    constraint  PK_STU_COU_TBL primary key (studentId, courseId)
);

create table student_tbl (
    id  number(9)   not null,
    no  varchar2(8) not null,
    name    varchar2(30) not null,
    gender varchar2(6),
    age number(2),
    address varchar2(50),
    phone   varchar2(12),
    regdate date,
    updatedate  date,
    description varchar2(100),
    constraint  PK_STUDENT_TBL primary key (id)
);



alter   table   stu_cou_tbl add constraint FK_STU_COU_REFERENCE_STUDENT foreign key (studentId)
    references student_tbl (id);

alter   table   stu_cou_tbl add constraint FK_STU_COU_REFERENCE_COURSE foreign key (courseId)
    references course_tbl (id);