SHPSHWES_upd.sql

<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;

</sql>