alter_tables.sql

自己做的一些练习答案

1.

CREATE TABLE PURCHASE_ORDER4
 (PoNum        char(10),
  Qty          integer,
  OrderDate    date,
  DueDate      date,
  ReceivedDate date,
  primary key (PoNum)
);

CREATE TABLE SUPPLIER4
 (Supplier_Id char(8),
  Name        char(14),
  Address     char(20),
  primary key (Supplier_Id)
);

CREATE TABLE READ_BY4
 (CallNumber char(8), 
  LibId      char(6), 
  TimesRead  integer,
  primary key (CallNumber, LibId)
);

CREATE TABLE ORDERED4
 (CallNumber  char(8), 
  PoNum       char(10),
  Supplier_Id char(10),
  primary key(CallNumber, PoNum, Supplier_Id)
);


2.

alter table title4 add constraint alterisbn unique(isbn);
alter table title4 alter column isbn set not null;
alter table title4 add constraint altername unique(name);
alter table title4 alter column name set not null ;

3.

alter table book4 add goreign key (BorrowerId) REFERENCES MEMBER4(LibId) ;
alter table book4 add goreign key (CallNumber) REFERENCES TITLE4 ;
alter table read_by4 add foreign key (CallNumber) REFERENCES TITLE4 ;
alter table read_by4 add foreign key (LibId) REFERENCES MEMBER4 ;
alter table ordered4 add foreign key (CallNumber) REFERENCES TITLE4 ;
alter table ordered4 add foreign key (PoNum) REFERENCES PURCHASE_ORDER4 ;
alter table ordered4 add foreign key (Supplier_Id) REFERENCES SUPPLIER4 ;

4.

alter table member4 add column Join_date date;
alter table member4 add column Gender char(1) check(Gender in('M','F')) default'M';

5.

alter table read_by4 add check(TimesRead>=0);