--ABS-141976
CREATE SEQUENCE SEQ_RAILIMPORTDEFINITION
MINVALUE 1
MAXVALUE 9999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;

CREATE TABLE RAILIMPORTDEFINITION
( RAILIMPORTDEFINITION_ID NUMBER NOT NULL,
  FLAG_ID                 NUMBER NULL,
  CUSTOMER_ID             NUMBER NULL,
  DEPARTMENT_ID           NUMBER NULL,
  GARMENTDISPENSER_ID     NUMBER NULL,
  LOCKER_ID               NUMBER NULL,
  WEARER_ID               NUMBER NULL,
  PRODUCT_ID              NUMBER NULL,
  SIZE_ID                 NUMBER NULL,
  FINISHINGMETHOD_ID      NUMBER NULL,
  RAILIMPORTDEFINITION    VARCHAR2(12) NOT NULL,
  SYSTEMUSER_ID           NUMBER DEFAULT 1 NOT NULL,
  TIMESTAMP               DATE DEFAULT SYSDATE NOT NULL);
--
ALTER TABLE RAILIMPORTDEFINITION ADD CONSTRAINT PK_RAILIMPORTDEFINITION PRIMARY KEY (RAILIMPORTDEFINITION_ID);
ALTER TABLE RAILIMPORTDEFINITION ADD CONSTRAINT UK1_RAILIMPORTDEFINITION UNIQUE (CUSTOMER_ID, DEPARTMENT_ID, GARMENTDISPENSER_ID, LOCKER_ID, WEARER_ID, PRODUCT_ID, SIZE_ID, FINISHINGMETHOD_ID);
ALTER TABLE RAILIMPORTDEFINITION ADD CONSTRAINT UK2_RAILIMPORTDEFINITION UNIQUE (FLAG_ID);
--
CREATE INDEX INDX01_RAILIMPORTDEFINITION ON RAILIMPORTDEFINITION (CUSTOMER_ID);
CREATE INDEX INDX02_RAILIMPORTDEFINITION ON RAILIMPORTDEFINITION (DEPARTMENT_ID);
CREATE INDEX INDX03_RAILIMPORTDEFINITION ON RAILIMPORTDEFINITION (GARMENTDISPENSER_ID);
CREATE INDEX INDX04_RAILIMPORTDEFINITION ON RAILIMPORTDEFINITION (LOCKER_ID);
CREATE INDEX INDX05_RAILIMPORTDEFINITION ON RAILIMPORTDEFINITION (WEARER_ID);
CREATE INDEX INDX06_RAILIMPORTDEFINITION ON RAILIMPORTDEFINITION (PRODUCT_ID);
CREATE INDEX INDX07_RAILIMPORTDEFINITION ON RAILIMPORTDEFINITION (SIZE_ID);
CREATE INDEX INDX08_RAILIMPORTDEFINITION ON RAILIMPORTDEFINITION (FINISHINGMETHOD_ID);
--
ALTER TABLE RAILIMPORTDEFINITION ADD CONSTRAINT FK0_RAILIMPORTDEFINITION FOREIGN KEY (FLAG_ID) REFERENCES FLAG (FLAG_ID) ON DELETE CASCADE;
ALTER TABLE RAILIMPORTDEFINITION ADD CONSTRAINT FK1_RAILIMPORTDEFINITION FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;
ALTER TABLE RAILIMPORTDEFINITION ADD CONSTRAINT FK2_RAILIMPORTDEFINITION FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENT (DEPARTMENT_ID) ON DELETE CASCADE;
ALTER TABLE RAILIMPORTDEFINITION ADD CONSTRAINT FK3_RAILIMPORTDEFINITION FOREIGN KEY (GARMENTDISPENSER_ID) REFERENCES GARMENTDISPENSER (GARMENTDISPENSER_ID) ON DELETE CASCADE;
ALTER TABLE RAILIMPORTDEFINITION ADD CONSTRAINT FK4_RAILIMPORTDEFINITION FOREIGN KEY (LOCKER_ID) REFERENCES LOCKER (LOCKER_ID) ON DELETE CASCADE;
ALTER TABLE RAILIMPORTDEFINITION ADD CONSTRAINT FK5_RAILIMPORTDEFINITION FOREIGN KEY (WEARER_ID) REFERENCES WEARER (WEARER_ID) ON DELETE CASCADE;
ALTER TABLE RAILIMPORTDEFINITION ADD CONSTRAINT FK6_RAILIMPORTDEFINITION FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT (PRODUCT_ID) ON DELETE CASCADE;
ALTER TABLE RAILIMPORTDEFINITION ADD CONSTRAINT FK7_RAILIMPORTDEFINITION FOREIGN KEY (SIZE_ID) REFERENCES SIZEDEFINITION (SIZEDEFINITION_ID) ON DELETE CASCADE;
ALTER TABLE RAILIMPORTDEFINITION ADD CONSTRAINT FK8_RAILIMPORTDEFINITION FOREIGN KEY (FINISHINGMETHOD_ID) REFERENCES FINISHINGMETHOD (FINISHINGMETHOD_ID) ON DELETE CASCADE;
--
ALTER TABLE RAILIMPORTDEFINITION ADD CONSTRAINT CHK1_RAILIMPORTDEFINITION CHECK ((FLAG_ID IS NOT NULL AND CUSTOMER_ID IS NULL) OR (FLAG_ID IS NULL AND CUSTOMER_ID IS NOT NULL));
