Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Database creation script
USE TRADER;
DROP TABLE IF EXISTS SALES;
DROP TABLE IF EXISTS PRODUCTS;
CREATE TABLE PRODUCTS (
ID BIGINT NOT NULL AUTO_INCREMENT,
NAME VARCHAR(255) NOT NULL,
PRIMARY KEY (ID)
) ENGINE = INNODB;
CREATE TABLE SALES (
ID BIGINT NOT NULL AUTO_INCREMENT,
BUYER_NAME VARCHAR(255) NOT NULL,
SELLER_NAME VARCHAR(255) NOT NULL,
PRODUCT_ID BIGINT NOT NULL,
PRICE DECIMAL(7,2) NOT NULL,
QUANTITY INT NOT NULL,
SALE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (ID),
FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCTS(ID)
) ENGINE = INNODB;
CREATE INDEX IDX_SALES_PRODUCT_ID ON SALES(PRODUCT_ID);
ALTER TABLE SALES ADD COLUMN SO_ID BIGINT;
ALTER TABLE SALES ADD COLUMN PO_ID BIGINT;
CREATE INDEX IDX_SALES_PO_ID ON SALES(PO_ID);
CREATE INDEX IDX_SALES_SO_ID ON SALES(SO_ID);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment