16.9 Create TRANS and its Constraints

16.9.1 Create Trans without constraints

CREATE TABLE TRANS (
    TransactionID        int                NOT NULL,
    DateAcquired         date               NOT NULL,
    AcquisitionPrice     decimal(8,2)       NOT NULL,
    AskingPrice          decimal(8,2)       NULL,
    DateSold             date               NULL,
    SalesPrice           numeric(8,2)       NULL,
    CustomerID           int                NULL,
    WorkID               int                NOT NULL
    );    

16.9.2 Constraints with ALTER TABLE

The four constraints in the TRANS table weren’t implemented in the CREATE TABLE statement above.

  • Primary Key
  • Two foreign keys
  • Check if SalesPrice is between 0 and 500,000
  • Check if DateAcquired is less than DateSold

We will add the constraints by ALTER TABLE statement.

ALTER TABLE statement changes table structure, properties, or constraints after it has been created.

16.9.3 Primary Key

ALTER TABLE trans 
  ADD CONSTRAINT TransPK PRIMARY KEY(TransactionID);

16.9.4 Two foreign keys

FK1. Trans.WorkID to WORK.WorkID

ALTER TABLE trans 
  ADD CONSTRAINT TransWorkFK FOREIGN KEY(WorkID) REFERENCES WORK(WorkID)
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;

FK2. Trans.CustomerID to Customer.CustomerID

Complete the following ALTER TABLE by filling in the placeholder ???:

ALTER TABLE ??? 
  ADD CONSTRAINT ???  FOREIGN KEY(???) REFERENCES ???(???)
    ON UPDATE ???
    ON DELETE ???;

16.9.5 Check if SalesPrice is between 0 and 500,000

ALTER TABLE Trans 
  ADD CONSTRAINT    SalesPriceRange         CHECK
                             ((SalesPrice > 0) AND (SalesPrice <=500000));

16.9.6 Check if DateAcquired is less than DateSold

Fix the following statement.

ALTER TABLE ADD CONSTRAINT
  ValidTransDate  CHECK (DateAcquired <= DateSold);