alter_tables.sql

SSD7 EXERCISE3 答案绝对正确 放心下吧

create table read_by4
(
 callnumber char(8) not null,
 libid char(6) not null,
 timesread int not null,
 constraint read_by4_key primary key(callnumber,libid)
);

create table supplier4
(
 supplier_id char(8) not null,
 name  char(7) not null,
 address char(10),
 constraint supplier4_key primary key(supplier_id )
);

create table ordered4
(
 callnumber char(8) not null,
 ponum char(8) not null,
 supplier_id char(8) not null,
 constraint ordered4_key primary key(callnumber,ponum,supplier_id)
);

create table purchase_order4
(
 ponum char(8) not null,
 qty   int not null,
 orderdate date,
 duedate   date,
 receiveddate date,
 constraint purchase_order4_key primary key(ponum)
);

alter table title4
add constraint UNIQUE_ISBN unique(ISBN);

alter table title4
alter ISBN set not null;

alter table title4
add constraint UNIQUE_Name unique(Name);

alter table title4
alter Name set not null;

alter table book4
 add constraint book4_fkey_borrowerid foreign key(borrowerid) references member4(libid);

alter table book4
add constraint book4_fkey_callnumber foreign key(callnumber) references title4(callnumber);

alter table read_by4
add constraint read_by4_fkey_callnumber foreign key(callnumber) references title4(callnumber);

alter table read_by4
add constraint read_by4_fkey_libid foreign key(libid) references member4(libid);

alter table ordered4
add constraint ordered4_fkey_callnumber foreign key(callnumber) references title4(callnumber);

alter table ordered4
add constraint ordered4_fkey_ponum foreign key(ponum) references purchase_order4(ponum);

alter table ordered4
add constraint ordered4_fkey_supplier_id foreign key(supplier_id) references supplier4(supplier_id);

alter table member4
add Join_date date;

alter table member4
add Gender char(1) not null;

alter table read_by4
add constraint CHECK_TimesRead check(timesread>=0);