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;