inheritance.sql

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

alter table student_tbl drop constraint FK_STUDENT_PERSON_TBL;
alter table employee_tbl drop constraint FK_EMPLOYEE_PERSON_TBL;
alter table resume_tbl drop constraint FK_RESUME_EMPLOYEE_TBL;

alter table student_tbl drop constraint PK_STUDENT_TBL;
alter table employee_tbl drop constraint PK_EMPLOYEE_TBL;
alter table resume_tbl drop constraint PK_RESUME_TBL;
alter table person_tbl drop constraint PK_PERSON_TBL;

drop table person_tbl cascade constraint;
drop table student_tbl cascade constraint;
drop table employee_tbl cascade constraint;
drop table resume_tbl cascade constraint;

create table person_tbl (
    id  varchar2(20) not null,
    firstname varchar2(20) not null,
    lastname varchar2(20) not null,
    address  varchar2(200),
    constraint PK_PERSON_TBL primary key (id)
);

create table student_tbl (
    id varchar2(20) not null,
    salutation varchar2(20) not null,
    person_id varchar2(20) not null,
    constraint PK_STUDENT_TBL primary key (id)
);

create table employee_tbl (
    id varchar2(20) not null,
    SSN varchar2(20) not null,
    person_id   varchar2(20) not null,
    constraint PK_EMPLOYEE_TBL primary key (id)
);

create table resume_tbl (
    id  varchar2(20) not null,
    startdate   date not null,
    enddate     date,
    description varchar2(200) not null,
    employee_id varchar2(20),
    constraint PK_RESUME_TBL primary key (id)
);

alter table student_tbl add constraint FK_STUDENT_PERSON_TBL foreign key (person_id) referenceS person_tbl (id);
alter table employee_tbl add constraint FK_EMPLOYEE_PERSON_TBL foreign key (person_id) referenceS person_tbl (id);
alter table resume_tbl add constraint FK_RESUME_EMPLOYEE_TBL foreign key (employee_id) references employee_tbl (id);
commit;