DatabaseCreation: ddl.sql

CREATE TABLE USERS (
  user_id       BIGSERIAL NOT NULL, 
  email         varchar(255) NOT NULL UNIQUE, 
  password_hash varchar(255) NOT NULL, 
  status        varchar(255) NOT NULL, 
  created_at    timestamp with time zone NOT NULL, 
  PRIMARY KEY (user_id), 
  CONSTRAINT ck_users_email_format 
    CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'));
CREATE TABLE USER_ROLES (
  user_id     int8 NOT NULL, 
  role_id     int8 NOT NULL, 
  assigned_at date NOT NULL, 
  PRIMARY KEY (user_id, 
  role_id));
CREATE TABLE ROLES (
  role_id     BIGSERIAL NOT NULL, 
  role_name   varchar(255) NOT NULL UNIQUE, 
  description varchar(255), 
  PRIMARY KEY (role_id));
CREATE TABLE USER_PROFILES (
  profile_id          BIGSERIAL NOT NULL, 
  user_id             int8 NOT NULL UNIQUE, 
  first_name          varchar(255) NOT NULL, 
  last_name           varchar(255) NOT NULL, 
  phone_number        varchar(255), 
  profile_picture_url varchar(255), 
  PRIMARY KEY (profile_id), 
  CONSTRAINT ck_user_profiles_phone 
    CHECK (phone_number IS NULL OR phone_number ~ '^\+?[0-9]{7,15}$'));
CREATE TABLE ADDRESSES (
  address_id     BIGSERIAL NOT NULL, 
  user_id        int8 NOT NULL, 
  address_type   varchar(255), 
  street_address varchar(255) NOT NULL, 
  city           varchar(255) NOT NULL, 
  country        varchar(255) NOT NULL, 
  is_default     bool NOT NULL, 
  PRIMARY KEY (address_id));
CREATE TABLE AUTH_LOGS (
  log_id          BIGSERIAL NOT NULL, 
  user_id         int8 NOT NULL, 
  login_timestamp timestamp with time zone NOT NULL, 
  ip_address      varchar(45), 
  device_info     varchar(255), 
  status          varchar(255) NOT NULL, 
  PRIMARY KEY (log_id));
CREATE TABLE USER_SESSIONS (
  session_id    BIGSERIAL NOT NULL, 
  user_id       int8 NOT NULL, 
  session_token varchar(255) NOT NULL UNIQUE, 
  login_time    timestamp with time zone NOT NULL, 
  expiry_time   timestamp with time zone NOT NULL, 
  PRIMARY KEY (session_id));
CREATE TABLE USER_NOTIFICATIONS (
  notification_id BIGSERIAL NOT NULL, 
  user_id         int8 NOT NULL, 
  type            varchar(255) NOT NULL, 
  message         varchar(255) NOT NULL, 
  sent_at         timestamp with time zone NOT NULL, 
  PRIMARY KEY (notification_id));
CREATE TABLE CATEGORIES (
  category_id        BIGSERIAL NOT NULL, 
  category_name      varchar(255) NOT NULL, 
  parent_category_id int8, 
  PRIMARY KEY (category_id));
CREATE TABLE PRODUCTS (
  product_id  BIGSERIAL NOT NULL, 
  category_id int8 NOT NULL, 
  name        varchar(255) NOT NULL, 
  brand       varchar(255), 
  description text, 
  PRIMARY KEY (product_id));
CREATE TABLE PRODUCT_ATTRIBUTES (
  attribute_id   BIGSERIAL NOT NULL, 
  attribute_name varchar(255) NOT NULL, 
  PRIMARY KEY (attribute_id));
CREATE TABLE PRODUCT_ATTRIBUTE_VALUES (
  variant_id   int8 NOT NULL, 
  attribute_id int8 NOT NULL, 
  attr_value   varchar(255) NOT NULL, 
  PRIMARY KEY (variant_id, 
  attribute_id));
CREATE TABLE WAREHOUSES (
  warehouse_id   BIGSERIAL NOT NULL, 
  warehouse_name varchar(255) NOT NULL, 
  location       varchar(255), 
  user_id        int8 NOT NULL, 
  PRIMARY KEY (warehouse_id));
CREATE TABLE PRODUCT_PRICE_HISTORY (
  history_id  BIGSERIAL NOT NULL, 
  variant_id  int8 NOT NULL, 
  old_price   numeric(12, 2) NOT NULL, 
  new_price   numeric(12, 2) NOT NULL, 
  change_date timestamp with time zone NOT NULL, 
  PRIMARY KEY (history_id), 
  CONSTRAINT check_product_price_history_old_price 
    CHECK (old_price >= 0), 
  CONSTRAINT check_product_price_history_new_price 
    CHECK (new_price>=0));
CREATE TABLE SHOPPING_CART (
  cart_id    BIGSERIAL NOT NULL, 
  user_id    int8 NOT NULL, 
  variant_id int8 NOT NULL, 
  quantity   int4 NOT NULL, 
  added_at   timestamp with time zone NOT NULL, 
  PRIMARY KEY (cart_id), 
  CONSTRAINT chk_shopping_cart_quantity 
    CHECK (quantity>0));
CREATE TABLE ORDERS (
  order_id           BIGSERIAL NOT NULL, 
  user_id            int8 NOT NULL, 
  shipping_method_id int8 NOT NULL, 
  order_date         timestamp with time zone NOT NULL, 
  total_amount       numeric(12, 2) NOT NULL, 
  status             varchar(255) NOT NULL, 
  PRIMARY KEY (order_id), 
  CONSTRAINT check_order_total 
    CHECK (total_amount >= 0));
CREATE TABLE PAYMENTS (
  payment_id     BIGSERIAL NOT NULL, 
  order_id       int8 NOT NULL, 
  payment_method varchar(255) NOT NULL, 
  amount         numeric(12, 2) NOT NULL, 
  payment_status varchar(255) NOT NULL, 
  transaction_id varchar(255) UNIQUE, 
  payment_date   timestamp with time zone NOT NULL, 
  PRIMARY KEY (payment_id), 
  CONSTRAINT check_amount 
    CHECK (amount>=0));
CREATE TABLE SHIPMENTS (
  shipment_id       BIGSERIAL NOT NULL, 
  order_id          int8 NOT NULL, 
  address_id        int8 NOT NULL, 
  tracking_number   varchar(255) UNIQUE, 
  shipped_date      timestamp with time zone, 
  estimated_arrival date, 
  courier_id        int8, 
  PRIMARY KEY (shipment_id));
CREATE TABLE SHIPPING_METHODS (
  method_id      BIGSERIAL NOT NULL, 
  method_name    varchar(255) NOT NULL, 
  cost           numeric(12, 2) NOT NULL, 
  estimated_days int4 NOT NULL, 
  PRIMARY KEY (method_id), 
  CONSTRAINT chk_shipping_days 
    CHECK (estimated_days>=0), 
  CONSTRAINT chk_shipping_cost 
    CHECK (cost>=0));
CREATE TABLE ORDER_STATUS_HISTORY (
  history_id  BIGSERIAL NOT NULL, 
  order_id    int8 NOT NULL, 
  old_status  varchar(255) NOT NULL, 
  new_status  varchar(255) NOT NULL, 
  change_date timestamp with time zone NOT NULL, 
  PRIMARY KEY (history_id));
CREATE TABLE SHIPMENT_ITEMS (
  shipment_id     int8 NOT NULL, 
  variant_id      int8 NOT NULL, 
  warehouse_id    int8 NOT NULL, 
  quantity_picked int4 NOT NULL, 
  PRIMARY KEY (shipment_id, 
  variant_id, 
  warehouse_id), 
  CONSTRAINT ck_shipment_items_quantity 
    CHECK (quantity_picked>0));
CREATE TABLE REVIEWS (
  review_id    BIGSERIAL NOT NULL, 
  user_id      int8 NOT NULL, 
  product_id   int8 NOT NULL, 
  rating       int4 NOT NULL, 
  comment_text text, 
  created_at   timestamp with time zone NOT NULL, 
  PRIMARY KEY (review_id), 
  CONSTRAINT ck_reviews_rating 
    CHECK (rating >= 1 AND rating <= 5));
COMMENT ON CONSTRAINT ck_reviews_rating ON REVIEWS IS 'спречува некој да внесе 0 или 6 ѕвездички';
CREATE TABLE REVIEW_HELPFULNESS (
  user_id   int8 NOT NULL, 
  review_id int8 NOT NULL, 
  vote_type varchar(255) NOT NULL, 
  voted_at  timestamp with time zone NOT NULL, 
  PRIMARY KEY (user_id, 
  review_id));
CREATE TABLE COUPONS (
  coupon_id       BIGSERIAL NOT NULL, 
  code            varchar(255) NOT NULL UNIQUE, 
  discount_value  numeric(12, 2) NOT NULL, 
  discount_type   varchar(255) NOT NULL, 
  valid_from      timestamp with time zone NOT NULL, 
  valid_to        timestamp with time zone NOT NULL, 
  min_order_value numeric(10, 2), 
  PRIMARY KEY (coupon_id), 
  CONSTRAINT check_coupons_discount 
    CHECK (discount_value > 0));
CREATE TABLE ORDER_DISCOUNTS (
  order_id     int8 NOT NULL, 
  coupon_id    int8 NOT NULL, 
  applied_at   timestamp with time zone NOT NULL, 
  amount_saved numeric(12, 2) NOT NULL, 
  PRIMARY KEY (order_id, 
  coupon_id), 
  CONSTRAINT chk_discount_amount 
    CHECK (amount_saved>=0));
CREATE TABLE SUPPORT_TICKETS (
  ticket_id    BIGSERIAL NOT NULL, 
  user_id      int8 NOT NULL, 
  subject      varchar(255) NOT NULL, 
  message_body text NOT NULL, 
  status       varchar(255) NOT NULL, 
  priority     varchar(255) NOT NULL, 
  PRIMARY KEY (ticket_id));
CREATE TABLE LOYALTY_POINTS (
  loyalty_id     BIGSERIAL NOT NULL, 
  user_id        int8 NOT NULL UNIQUE, 
  current_points int4 NOT NULL, 
  total_earned   int4 NOT NULL, 
  last_updated   timestamp with time zone NOT NULL, 
  PRIMARY KEY (loyalty_id), 
  CONSTRAINT check_loyalty_total 
    CHECK (total_earned >= 0), 
  CONSTRAINT check_loyalty_current 
    CHECK (current_points >= 0));
CREATE TABLE WISHLISTS (
  wishlist_id BIGSERIAL NOT NULL, 
  user_id     int8 NOT NULL UNIQUE, 
  created_at  timestamp with time zone NOT NULL, 
  PRIMARY KEY (wishlist_id));
CREATE TABLE WISHLIST_ITEMS (
  wishlist_id int8 NOT NULL, 
  variant_id  int8 NOT NULL, 
  added_at    timestamp with time zone NOT NULL, 
  PRIMARY KEY (wishlist_id, 
  variant_id));
CREATE TABLE PRODUCT_WAITLIST (
  waitlist_id BIGSERIAL NOT NULL, 
  user_id     int8 NOT NULL, 
  variant_id  int8 NOT NULL, 
  added_at    timestamp with time zone NOT NULL, 
  status      varchar(255) NOT NULL, 
  PRIMARY KEY (waitlist_id), 
  CONSTRAINT uq_product_waitlist_user_variant 
    UNIQUE (user_id, variant_id));
CREATE TABLE ORDER_ITEMS (
  order_item_id BIGSERIAL NOT NULL, 
  order_id      int8 NOT NULL, 
  variant_id    int8 NOT NULL, 
  quantity      int4 NOT NULL, 
  unit_price    numeric(12, 2) NOT NULL, 
  PRIMARY KEY (order_item_id), 
  CONSTRAINT check_order_items_price 
    CHECK (unit_price >= 0), 
  CONSTRAINT check_order_items_quantity 
    CHECK (quantity > 0));
CREATE TABLE MANUFACTURER (
  manufacturer_id BIGSERIAL NOT NULL, 
  name            varchar(100) NOT NULL, 
  contact_info    text, 
  PRIMARY KEY (manufacturer_id));
CREATE TABLE PRODUCT_INSTANCE (
  instance_id   BIGSERIAL NOT NULL, 
  variant_id    int8 NOT NULL, 
  serial_number varchar(255) NOT NULL UNIQUE, 
  status        varchar(20) NOT NULL, 
  warehouse_id  int8 NOT NULL, 
  PRIMARY KEY (instance_id));
CREATE TABLE INVENTORY_ITEM (
  inventory_id BIGSERIAL NOT NULL, 
  warehouse_id int8 NOT NULL, 
  variant_id   int8 NOT NULL, 
  quantity     int8 NOT NULL, 
  PRIMARY KEY (inventory_id), 
  CONSTRAINT chk_inventory_item_quantity 
    CHECK (quantity >= 0));
CREATE TABLE PRODUCT_VARIANTS (
  variant_id      BIGSERIAL NOT NULL, 
  product_id      int8 NOT NULL, 
  sku             varchar(100) NOT NULL UNIQUE, 
  price           numeric(12, 2) NOT NULL, 
  stock_total     int4 NOT NULL, 
  manufacturer_id int8 NOT NULL, 
  PRIMARY KEY (variant_id), 
  CONSTRAINT ck_product_variants_price 
    CHECK (price >= 0), 
  CONSTRAINT chk_variant_stock 
    CHECK (stock_total>=0));
CREATE TABLE WARRANTY (
  warranty_id      BIGSERIAL NOT NULL, 
  variant_id       int8 NOT NULL UNIQUE, 
  duration_months  int8 NOT NULL, 
  terms_conditions text, 
  PRIMARY KEY (warranty_id));
CREATE TABLE ORDER_ITEM_ALLOCATIONS (
  allocation_id BIGSERIAL NOT NULL, 
  order_item_id int8 NOT NULL, 
  instance_id   int8 NOT NULL UNIQUE, 
  allocated_at  timestamp with time zone NOT NULL, 
  PRIMARY KEY (allocation_id));
ALTER TABLE USER_ROLES ADD CONSTRAINT HAS_ROLE_ASSIGNMENT FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Cascade;
ALTER TABLE USER_ROLES ADD CONSTRAINT IS_ASSIGNED_TO_USER FOREIGN KEY (role_id) REFERENCES ROLES (role_id) ON UPDATE Cascade ON DELETE Restrict;
ALTER TABLE USER_PROFILES ADD CONSTRAINT HAS_PROFILE FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Cascade;
ALTER TABLE ADDRESSES ADD CONSTRAINT HAS_ADDRESS FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Cascade;
ALTER TABLE CATEGORIES ADD CONSTRAINT PARENT_OF FOREIGN KEY (parent_category_id) REFERENCES CATEGORIES (category_id) ON UPDATE Cascade ON DELETE Restrict;
ALTER TABLE ORDERS ADD CONSTRAINT USED_FOR_ORDER FOREIGN KEY (shipping_method_id) REFERENCES SHIPPING_METHODS (method_id) ON UPDATE Cascade ON DELETE Restrict;
ALTER TABLE PAYMENTS ADD CONSTRAINT HAS_PAYMENT_ATTEMPT FOREIGN KEY (order_id) REFERENCES ORDERS (order_id) ON UPDATE Cascade ON DELETE Restrict;
ALTER TABLE SHIPMENTS ADD CONSTRAINT GENERATES_SHIPMENTS FOREIGN KEY (order_id) REFERENCES ORDERS (order_id) ON UPDATE Cascade ON DELETE Restrict;
ALTER TABLE SHIPMENT_ITEMS ADD CONSTRAINT CONTAINS_SHIPMENT_ITEM FOREIGN KEY (shipment_id) REFERENCES SHIPMENTS (shipment_id) ON UPDATE Cascade ON DELETE Restrict;
ALTER TABLE ORDER_STATUS_HISTORY ADD CONSTRAINT HAS_STATUS_CHANGE FOREIGN KEY (order_id) REFERENCES ORDERS (order_id) ON UPDATE Cascade ON DELETE Restrict;
ALTER TABLE REVIEW_HELPFULNESS ADD CONSTRAINT MARKS_HELPFULL FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Cascade;
ALTER TABLE ORDER_DISCOUNTS ADD CONSTRAINT APPLIES_COUPON FOREIGN KEY (order_id) REFERENCES ORDERS (order_id) ON UPDATE Cascade ON DELETE Restrict;
ALTER TABLE ORDER_DISCOUNTS ADD CONSTRAINT IS_APPLIED_IN_ORDER FOREIGN KEY (coupon_id) REFERENCES COUPONS (coupon_id) ON UPDATE Cascade ON DELETE Restrict;
ALTER TABLE SUPPORT_TICKETS ADD CONSTRAINT CREATES_TICKET FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Cascade;
ALTER TABLE LOYALTY_POINTS ADD CONSTRAINT HAS_LOYALTY_POINTS FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Cascade;
ALTER TABLE PRODUCT_WAITLIST ADD CONSTRAINT JOINS_WAITLIST FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Cascade;
ALTER TABLE AUTH_LOGS ADD CONSTRAINT GENERATES_AUTH_LOGS FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Restrict;
ALTER TABLE USER_NOTIFICATIONS ADD CONSTRAINT RECIEVES_NOTIFICATIONS FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Cascade;
ALTER TABLE WISHLISTS ADD CONSTRAINT HAS_WISHLIST FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Cascade;
ALTER TABLE WISHLIST_ITEMS ADD CONSTRAINT CONTAINS_WISHLIST_ITEM FOREIGN KEY (wishlist_id) REFERENCES WISHLISTS (wishlist_id) ON UPDATE Cascade ON DELETE Cascade;
ALTER TABLE USER_SESSIONS ADD CONSTRAINT HAS_SESSION FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Restrict;
ALTER TABLE ORDERS ADD CONSTRAINT PLACES_ORDER FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Restrict;
ALTER TABLE PRODUCT_ATTRIBUTE_VALUES ADD CONSTRAINT DEFINES_VALUE FOREIGN KEY (attribute_id) REFERENCES PRODUCT_ATTRIBUTES (attribute_id) ON UPDATE Cascade ON DELETE Restrict;
ALTER TABLE SHOPPING_CART ADD CONSTRAINT ADDS_TO_CART FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Cascade;
ALTER TABLE SHIPMENTS ADD CONSTRAINT IS_SHIPPED_TO FOREIGN KEY (address_id) REFERENCES ADDRESSES (address_id) ON UPDATE Cascade ON DELETE Restrict;
ALTER TABLE ORDER_ITEMS ADD CONSTRAINT CONTAINS_ORDER_ITEM FOREIGN KEY (order_id) REFERENCES ORDERS (order_id) ON UPDATE Cascade ON DELETE Restrict;
ALTER TABLE REVIEW_HELPFULNESS ADD CONSTRAINT RECIEVES_HELPFULL_VOTE FOREIGN KEY (review_id) REFERENCES REVIEWS (review_id) ON UPDATE Cascade ON DELETE Cascade;
ALTER TABLE REVIEWS ADD CONSTRAINT HAS_REVIEW FOREIGN KEY (product_id) REFERENCES PRODUCTS (product_id) ON UPDATE Cascade ON DELETE Restrict;
ALTER TABLE PRODUCTS ADD CONSTRAINT CONTAINS_PRODUCT FOREIGN KEY (category_id) REFERENCES CATEGORIES (category_id) ON UPDATE Cascade ON DELETE Restrict;
ALTER TABLE REVIEWS ADD CONSTRAINT WRITES_REVIEW FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Restrict;
ALTER TABLE WAREHOUSES ADD CONSTRAINT OWNED_BY_SELLER FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Restrict;
ALTER TABLE INVENTORY_ITEM ADD CONSTRAINT MANAGES_STOCK FOREIGN KEY (warehouse_id) REFERENCES WAREHOUSES (warehouse_id) ON UPDATE Cascade ON DELETE Restrict;
ALTER TABLE PRODUCT_INSTANCE ADD CONSTRAINT LOCATED_IN FOREIGN KEY (warehouse_id) REFERENCES WAREHOUSES (warehouse_id) ON UPDATE Cascade ON DELETE Restrict;
ALTER TABLE SHOPPING_CART ADD CONSTRAINT APPEARS_IN_CART FOREIGN KEY (variant_id) REFERENCES PRODUCT_VARIANTS (variant_id) ON UPDATE Cascade ON DELETE Cascade;
ALTER TABLE WISHLIST_ITEMS ADD CONSTRAINT IS_IN_WISHLIST FOREIGN KEY (variant_id) REFERENCES PRODUCT_VARIANTS (variant_id) ON UPDATE Cascade ON DELETE Cascade;
ALTER TABLE PRODUCT_WAITLIST ADD CONSTRAINT HAS_WAITLIST_ENTRY FOREIGN KEY (variant_id) REFERENCES PRODUCT_VARIANTS (variant_id) ON UPDATE Cascade ON DELETE Cascade;
ALTER TABLE PRODUCT_ATTRIBUTE_VALUES ADD CONSTRAINT HAS_ATTRIBUTE_VALUES FOREIGN KEY (variant_id) REFERENCES PRODUCT_VARIANTS (variant_id) ON UPDATE Cascade ON DELETE Cascade;
ALTER TABLE ORDER_ITEMS ADD CONSTRAINT IS_ORDERED_AS_ITEM FOREIGN KEY (variant_id) REFERENCES PRODUCT_VARIANTS (variant_id) ON UPDATE Cascade ON DELETE Restrict;
ALTER TABLE INVENTORY_ITEM ADD CONSTRAINT STOCKED_AS FOREIGN KEY (variant_id) REFERENCES PRODUCT_VARIANTS (variant_id) ON UPDATE Cascade ON DELETE Restrict;
ALTER TABLE PRODUCT_VARIANTS ADD CONSTRAINT HAS_VARIANT FOREIGN KEY (product_id) REFERENCES PRODUCTS (product_id) ON UPDATE Cascade ON DELETE Restrict;
ALTER TABLE PRODUCT_VARIANTS ADD CONSTRAINT PRODUCED_BY FOREIGN KEY (manufacturer_id) REFERENCES MANUFACTURER (manufacturer_id) ON UPDATE Cascade ON DELETE Restrict;
ALTER TABLE PRODUCT_PRICE_HISTORY ADD CONSTRAINT HAS_PRICE_HISTORY FOREIGN KEY (variant_id) REFERENCES PRODUCT_VARIANTS (variant_id) ON UPDATE Cascade ON DELETE Restrict;
ALTER TABLE PRODUCT_INSTANCE ADD CONSTRAINT HAS_INSTANCE FOREIGN KEY (variant_id) REFERENCES PRODUCT_VARIANTS (variant_id) ON UPDATE Cascade ON DELETE Restrict;
ALTER TABLE WARRANTY ADD CONSTRAINT PROVIDES_WARANTY FOREIGN KEY (variant_id) REFERENCES PRODUCT_VARIANTS (variant_id) ON UPDATE Cascade ON DELETE Restrict;
ALTER TABLE SHIPMENT_ITEMS ADD CONSTRAINT PICKS_VARIANT_FOR_SHIPMENT FOREIGN KEY (variant_id) REFERENCES PRODUCT_VARIANTS (variant_id) ON UPDATE Cascade ON DELETE Restrict;
ALTER TABLE SHIPMENT_ITEMS ADD CONSTRAINT PICKED_FROM_WAREHOUSE FOREIGN KEY (warehouse_id) REFERENCES WAREHOUSES (warehouse_id) ON UPDATE Cascade ON DELETE Restrict;
ALTER TABLE ORDER_ITEM_ALLOCATIONS ADD CONSTRAINT ALLOCATES_INSTANCE FOREIGN KEY (order_item_id) REFERENCES ORDER_ITEMS (order_item_id) ON UPDATE Cascade ON DELETE Restrict;
ALTER TABLE ORDER_ITEM_ALLOCATIONS ADD CONSTRAINT ASSIGNS_INSTANCE FOREIGN KEY (instance_id) REFERENCES PRODUCT_INSTANCE (instance_id) ON UPDATE Cascade ON DELETE Restrict;