alter_tables.sql

来自「ssd7 exercise 3 ,绝对有用~欢迎下载」· SQL 代码 · 共 52 行

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  FOREIGN KEY (BorrowerId) REFERENCES MEMBER4(LibId) ;
ALTER TABLE BOOK4 ADD  FOREIGN 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'F';

5.
ALTER TABLE READ_BY4 ADD check(TimesRead>=0);