alter_tables.sql

ssd7数据库的联系一到练习三

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
  ADD CONSTRAINT AK_TITLE4_SIBN UNIQUE(ISBN,Name),
  ALTER isbn SET NOT NULL,
  ALTER name SET NOT NULL;
 
ALTER TABLE BOOK4
  ADD CONSTRAINT FK_BOOK4_MEMBER4 FOREIGN KEY(borrowerid) REFERENCES MEMBER4(libid),
  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),
  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),
  ADD CONSTRAINT FK_ORDERED4_PURCHASE_ORDER4 FOREIGN KEY(ponum) REFERENCES PURCHASE_ORDER4(ponum),
  ADD CONSTRAINT FK_ORDERED4_SUPPLIER4 FOREIGN KEY(supplier_id) REFERENCES SUPPLIER4(supplier_id);

ALTER TABLE MEMBER4
  ADD Join_date date,
  ADD Gender varchar(1);

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