CP363 : SQL Table Creation Examples

CREATE TABLE member (
  member_id SMALLINT NOT NULL PRIMARY KEY
  GENERATED ALWAYS AS IDENTITY ( START WITH 1, INCREMENT BY 1 ),
  Last_Name VARCHAR(25) NOT NULL,
  First_Name VARCHAR(25) NOT NULL,
  Gender CHAR(1),
  Address VARCHAR( 500 ) NOT NULL,
  Postal_Code CHAR(7) NOT NULL,
  CONSTRAINT GenderChk CHECK( Gender IN ( 'M', 'F' ) )
)

• member_id is assigned by the system when a tuple is added to the table. (It adds 1 to the largest previous value of member_id).
• All fields except Gender must be entered (it allows NULL).
• If not NULL, Gender must have one of the values 'M' or 'F'.
• NOT NULL must always be used with an attributed that is part of a PRIMARY KEY

CREATE TABLE pub (
  pub_ID INT NOT NULL PRIMARY KEY
  GENERATED ALWAYS AS IDENTITY ( START WITH 1, INCREMENT BY 1 ),
  member_id SMALLINT,
  Title VARCHAR(40) NOT NULL,
  Journal VARCHAR(40),
  Publisher VARCHAR(50) NOT NULL,
  Price DECIMAL( 5, 2 ),
  FOREIGN KEY ( member_id )
    REFERENCES member ( member_id )
    ON DELETE CASCADE
)

• member_id refers to the member_id field of the member table. No new record may be atted to the pub table that does not have a member_id from the member table.
• The ON DELETE CASCADE clause tells the DBMS that when a member is deleted, all of that member's publications should be deleted as well.
• The DECIMAL type allows money data to be stored with more accuracy than if REAL was used.

CREATE TABLE Borrowed (
  pub_ID INTEGER NOT NULL,
  Date_Borrowed DATE NOT NULL DEFAULT CURRENT DATE,
  Date_Returned DATE,
  PRIMARY KEY ( pub_ID, Date_Borrowed ),
  CHECK( Date_Returned >= Date_Borrowed ),
  FOREIGN KEY ( pub_ID )
    REFERENCES pub ( pub_ID )
)

• Date_Borrowed is given the current date when a record is added.
• Date_Returned allows NULL values when created.
• If Date_Returned is NULL when CHECKed, the CHECK clause returns an UNKNOWN result, which is fine. If Date_Returned is non-NULL, then it must be greater than or equal to Date_Borrowed.
• The primary key has more than one attribute and is defined after the attribute definitions.
• The CHECK constraint is anonymous.
• The foreign key reference lacks the ON DELETE CASCADE clause, meaning that an attempt to delete a matching tuple in the pub table will fail so long as a matching pub_ID exists in the Borrowed table.