SHPSHWES_upd.sql
SET SQL DIALECT 3;
SET NAMES WIN1251;
CONNECT 'E:\DataBase\SHPSH\shpshwes.fdb' USER 'SYSDBA' PASSWORD 'masterkey';
ALTER TABLE BATCHES ADD IDBATCHEXT INTEGER;
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;