✎ SHPSHWES_upd.sql [ПромАвтоматика]

Эта страница только для чтения. Вы можете посмотреть исходный текст, но не можете его изменить. Сообщите администратору, если считаете, что это неправильно.

====== SHPSHWES_upd.sql ======
<code sql>

SET SQL DIALECT 3;

SET NAMES WIN1251;

CONNECT 'E:\DataBase\SHPSH\shpshwes.fdb' USER 'SYSDBA' PASSWORD 'masterkey';




ALTER TABLE BATCHOPER ADD USES_BATCHEXT INTEGER DEFAULT 0;




DESCRIBE FIELD USES_BATCHEXT TABLE BATCHOPER
'Defines how the field BATCHES.IDBATCHEXT should be used:
0 - either could be NULL or not
1 - must be NOT NULL
2 - must be NULL
';




CREATE TABLE BATCHSTATUS (
    IDBATCHSTATUS  INTEGER NOT NULL,
    NAME           VARCHAR(32)
);


ALTER TABLE BATCHSTATUS ADD CONSTRAINT PK_BATCHSTATUS PRIMARY KEY (IDBATCHSTATUS);






CREATE TABLE BATCHOPERSTATUS (
    IDBATCHOPER    INTEGER NOT NULL,
    IDBATCHSTATUS  INTEGER NOT NULL
);

ALTER TABLE BATCHOPERSTATUS ADD CONSTRAINT PK_BATCHOPERSTATUS PRIMARY KEY (IDBATCHOPER, IDBATCHSTATUS);

ALTER TABLE BATCHOPERSTATUS ADD CONSTRAINT FK_BATCHOPERSTATUS_1 FOREIGN KEY (IDBATCHOPER) 
    REFERENCES BATCHOPER (IDBATCHOPER) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE BATCHOPERSTATUS ADD CONSTRAINT FK_BATCHOPERSTATUS_2 FOREIGN KEY (IDBATCHSTATUS) 
    REFERENCES BATCHSTATUS (IDBATCHSTATUS) ON DELETE CASCADE ON UPDATE CASCADE;





CREATE TABLE BATCHEXT (
    IDBATCHEXT     INTEGER NOT NULL,
    IDBATCHSTATUS  INTEGER NOT NULL,
    NBREXT         INTEGER,
    DTBEXT         TIMESTAMP
);

ALTER TABLE BATCHEXT ADD CONSTRAINT PK_BATCHEXT PRIMARY KEY (IDBATCHEXT);

ALTER TABLE BATCHEXT ADD CONSTRAINT FK_BATCHEXT_1 FOREIGN KEY (IDBATCHSTATUS) 
    REFERENCES BATCHSTATUS (IDBATCHSTATUS) ON DELETE CASCADE ON UPDATE CASCADE;





CREATE TABLE BATCHEXTPROD (
    IDBATCHEXT  INTEGER NOT NULL,
    IDPROD      INTEGER NOT NULL,
    IS_MAIN     INTEGER
);

ALTER TABLE BATCHEXTPROD ADD CONSTRAINT PK_BATCHEXTPROD PRIMARY KEY (IDBATCHEXT, IDPROD);

ALTER TABLE BATCHEXTPROD ADD CONSTRAINT FK_BATCHEXTPROD_1 FOREIGN KEY (IDBATCHEXT) 
      REFERENCES BATCHEXT (IDBATCHEXT) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE BATCHEXTPROD ADD CONSTRAINT FK_BATCHEXTPROD_2 FOREIGN KEY (IDPROD) 
      REFERENCES PRODUCT (IDPROD) ON DELETE CASCADE ON UPDATE CASCADE;


</code>