alter_tables.sql

ssd7的练习3

CREATE TABLE PURCHASE_ORDER4
 (PoNum          char(8) NOT NULL,
  Qty            integer,
  OrderDate      date,
  DueDate        date,
  ReceivedDate   date,
  CONSTRAINT PK_PURCHASE_ORDER4 PRIMARY KEY (PoNum)
);

CREATE TABLE SUPPLIER4
 (Supplier_Id    char(8) NOT NULL,
  Name           varchar(20),
  Address        varchar(40),
  CONSTRAINT PK_SUPPLIER4 PRIMARY KEY (Supplier_Id)
);

CREATE TABLE READ_BY4
 (CallNumber     char(8)     NOT NULL,
  LibId          char(6)     NOT NULL,
  TimesRead      integer,
  CONSTRAINT PK_READ_BY4 PRIMARY KEY (CallNumber,LibId)
);

CREATE TABLE ORDERED4
 (CallNumber     char(8)     NOT NULL,
  PoNum          char(8)     NOT NULL,
  Supplier_Id    char(8)     NOT NULL,
  CONSTRAINT PK_ORDERED4 PRIMARY KEY (CallNumber,PoNum,Supplier_Id)
);

ALTER TABLE TITLE4
  ALTER isbn SET NOT NULL;

ALTER TABLE TITLE4
  ALTER name SET NOT NULL;

ALTER TABLE TITLE4
  ADD CONSTRAINT AK_TITLE4_SIBN UNIQUE(isbn);

ALTER TABLE TITLE4
  ADD CONSTRAINT AK_TITLE4_NAME UNIQUE(name);

ALTER TABLE BOOK4
  ADD CONSTRAINT FK_BOOK4_MEMBER4 FOREIGN KEY(borrowerid) REFERENCES MEMBER4(libid);

ALTER TABLE BOOK4
  ADD CONSTRAINT FK_BOOK4_TITLE4 FOREIGN KEY(callnumber) REFERENCES TITLE4(callnumber);

ALTER TABLE READ_BY4
  ADD CONSTRAINT FK_READ_BY4_TITLE4 FOREIGN KEY(callnumber) REFERENCES TITLE4(callnumber);

ALTER TABLE READ_BY4
  ADD CONSTRAINT FK_READ_BY4_MEMBER4 FOREIGN KEY(LibId) REFERENCES MEMBER4(LibId);

ALTER TABLE ORDERED4
  ADD CONSTRAINT FK_ORDERED4_TITLE4 FOREIGN KEY(CallNumber) REFERENCES TITLE4(CallNumber);

ALTER TABLE ORDERED4
  ADD CONSTRAINT FK_ORDERED4_PURCHASE_ORDER4 FOREIGN KEY(PoNum) REFERENCES PURCHASE_ORDER4(PoNum);

ALTER TABLE ORDERED4
  ADD CONSTRAINT FK_ORDERED4_SUPPLIER4 FOREIGN KEY(Supplier_Id) REFERENCES SUPPLIER4(Supplier_Id);

ALTER TABLE MEMBER4
  ADD Join_date date;

ALTER TABLE MEMBER4
  ADD Gender char(1);

ALTER TABLE READ_BY4
  ADD CONSTRAINT CK_READ_BY4
    CHECK (TimesRead>=0);