Create Customer and References tables

an anonymous user · December 06, 2021 · SQL
-- create a table customer
CREATE TABLE Customer (
    customerID serial,
    firstname char(20) NOT NULL,
    lastname char(20) NOT NULL,
    cusStreetNum int(10) NOT NULL,
    cusStreetName varchar(20) NOT NULL,
    cusCity varchar(30) NOT NULL,
    cusState char(30) NOT NULL,
    cusZipcode int(15) NOT NULL,
    cusPhoneNo int(15) NOT NULL,
    email varchar(255) NOT NULL,
    PRIMARY KEY (customerID)
);

-- create a table order
CREATE TABLE `order` (
    orderID serial NOT NULL,
    customerID serial NOT NULL,
    paymentID serial NOT NULL,
    orderDATE date NOT NULL,
    paid int(500) NOT NULL,
    paymentDate date NOT NULL,
    shippingDate date NOT NULL,
    PRIMARY KEY (orderID),
    FOREIGN KEY (customerID)
        REFERENCES Customer(customerID)
        ON DELETE CASCADE
    FOREIGN KEY (paymentID)
        REFERENCES Payment(paymentID)
        ON DELETE CASCADE
);

-- create a table payment
CREATE TABLE Payment (
    paymentID serial,
    paymentType varchar(40) NOT NULL,
    PRIMARY KEY (paymentID),
    FOREIGN KEY (customerID)
        REFERENCES order(customerID)
        ON DELETE CASCADE
);

-- create a table category
CREATE TABLE Category (
    categoryID serial,
    categoryName varchar(100) NOT NULL,
    PRIMARY KEY (categoryID),
);



-- create a table merchandise
CREATE TABLE Merchandise (
    merchandiseID integer NOT NULL,
    merchandiseName varchar(100) NOT NULL,
    categoryID integer NOT NULL,
    unitPrice integer NOT NULL,
    size varchar(100) NOT NULL,
    color varchar(100) NOT NULL,
    merchandiseAvailability integer NOT NULL,
    employeeID serial
    PRIMARY KEY (merchandiseID),
    FOREIGN KEY (categoryID)
        REFERENCES Customer(customerID),
        ON DELETE CASCADE
    FOREIGN KEY (employeeID)
        REFERENCES category(categoryID)
        ON DELETE CASCADE
);

CREATE TABLE employee (
  employeeID serial,
  title varchar(100) NOT NULL,
  empFirst char(100) NOT NULL,
  empLast char(100) NOT NULL,
  empDOB date NOT NULL,
  empStreetNum int(50) NOT NULL,
  empStreetNam varchar(100) NOT NULL,
  empCity varchar(100) NOT NULL,
  empState varchar(100) NOT NULL,
  empZip int(12) NOT NULL,
  empStatus varchar(100) NOT NULL,
  dateHired date NOT NULL,
  empPhoneNo int(15)
  PRIMARY KEY (employeeID),
);

/*
 one-to-many: Employee can fulfill many orderdetail
*/

CREATE TABLE orderDetail (
  orderDetailsID serial,
  merchandiseID integer NOT NULL,
  orderID varchar(255) NOT NULL,
  total int(255),
  PRIMARY KEY (orderDetailsID),
  FOREIGN KEY (merchandiseID)
      REFERENCES Merchandise(merchandiseID)
      ON DELETE CASCADE
  FOREIGN KEY (orderID)
      REFERENCES Order(orderID)
      ON DELETE CASCADE
);

Comments

Please sign up or log in to contribute to the discussion.