DDL Db2i

DDL Db2i

CREATE TABLE galmacen (
    almid integer generated by default as identity not null,
    codigo char(6) not null,
    nomalm varchar(40) not null,
    tercer char(12) not null,
    tipdir char(6) default '0' not null,
    empcode char(4) not null,
    agrupa char(4) not null,
    recint char(6) not null,
    delega char(6),
    depart char(6),
    codpre char(10),
    stkneg smallint default 0 not null,
    stkrup char(1) default 'N' not null,
    tipval char(1) not null,
    cosuni char(1) default 'N' not null,
    costol smallint default 0 not null,
    fecval date not null,
    aplven smallint default 0 not null,
    indlot smallint default 1 not null,
    disdel smallint default 0 not null,
    reflec integer,
    capdes decimal(11,3) default 0.000 not null,
    horini smallint default 0 not null,
    horfin smallint default 0 not null,
    tippro char(4),
    impre1 char(10),
    impre2 char(10),
    tipast char(4),
    fecalt date default CURRENT DATE not null,
    fecbaj date,
    fecult date not null,
    feccon date,
    inipot smallint,
    estado char(1) not null,
    errnum smallint default 0 not null,
    wkfnum smallint,
    auxchr1 char(1),
    auxchr2 char(1),
    auxchr3 char(1),
    auxchr4 char(1),
    auxchr5 char(1),
    auxfec1 date,
    auxfec2 date,
    auxnum1 smallint,
    auxnum2 smallint,
    auxnum3 smallint,
    user_created varchar(128) default user not null,
    date_created timestamp default CURRENT TIMESTAMP not null,
    user_updated varchar(128) default user not null,
    date_updated timestamp default CURRENT TIMESTAMP not null

   ,rowid rowid implicitly hidden
      , CONSTRAINT c_galmacen1
     CHECK (
    (tipval IN ('F', 'L', 'M', 'N'))
 )
      , CONSTRAINT c_galmacen2
     CHECK (
    (cosuni IN ('S', 'N'))
 )
      , CONSTRAINT c_galmacen3
     CHECK (
    (feccon IS NULL OR feccon <= fecval)
 )
      , CONSTRAINT c_galmacen4
     CHECK (
    (feccon IS NULL OR
    (feccon IS NOT NULL AND tipast IS NOT NULL))
 )
      , CONSTRAINT c_galmacen5
     CHECK (
    (fecbaj IS NULL OR fecalt <= fecbaj)
 )
      , CONSTRAINT c_galmacen6
     CHECK (
    (horini <= horfin)
 )
      , CONSTRAINT c_galmacen7
     CHECK (
    ((horini BETWEEN 0 AND 23) AND (horfin BETWEEN 0 AND 23))
 )
      , CONSTRAINT c_galmacen8
     CHECK (
    (costol = 0 OR (costol > 0 AND tipval = 'M'))
 )
      , CONSTRAINT c_galmacen9
     CHECK (
    errnum = 0 OR (errnum != 0 AND estado = 'B')
 )
      , CONSTRAINT c_galmacen10
     CHECK (
    wkfnum IS NULL OR (wkfnum IS NOT NULL AND errnum != 0)
 )
      , CONSTRAINT c_galmacen11
     CHECK (
      stkrup IN ('N', 'G', 'Z')
 )
);

ALTER TABLE galmacen
 ADD CONSTRAINT u_galmacen1 UNIQUE (almid);

-- Database Server on Postgres, DB2 will be created a index automatically with the same name of pk
ALTER TABLE galmacen
 ADD CONSTRAINT p_galmacen
 PRIMARY KEY (codigo);

CREATE  INDEX f_galmacen1 ON galmacen(tercer);
ALTER TABLE galmacen
 ADD CONSTRAINT f_galmacen1 FOREIGN KEY (tercer)
 REFERENCES ctercero(codigo);

CREATE  INDEX f_galmacen2 ON galmacen(tipast);
ALTER TABLE galmacen
 ADD CONSTRAINT f_galmacen2 FOREIGN KEY (tipast)
 REFERENCES gconasih(codigo);

CREATE  INDEX f_galmacen3 ON galmacen(agrupa);
ALTER TABLE galmacen
 ADD CONSTRAINT f_galmacen3 FOREIGN KEY (agrupa)
 REFERENCES gagrupac(codigo);

CREATE  INDEX f_galmacen4 ON galmacen(delega,depart);
ALTER TABLE galmacen
 ADD CONSTRAINT f_galmacen4 FOREIGN KEY (delega,depart)
 REFERENCES gdeparta(delega,depart);

CREATE  UNIQUE  INDEX f_galmacen5 ON galmacen(tercer,tipdir);
ALTER TABLE galmacen
 ADD CONSTRAINT f_galmacen5 FOREIGN KEY (tercer,tipdir)
 REFERENCES cterdire(codigo,tipdir);

CREATE  INDEX f_galmacen6 ON galmacen(recint);
ALTER TABLE galmacen
 ADD CONSTRAINT f_galmacen6 FOREIGN KEY (recint)
 REFERENCES galmreci(codigo);

CREATE  INDEX f_galmacen7 ON galmacen(empcode);
ALTER TABLE galmacen
 ADD CONSTRAINT f_galmacen7 FOREIGN KEY (empcode)
 REFERENCES cempresa(empcode);

CREATE  INDEX f_galmacen8 ON galmacen(tippro);
ALTER TABLE galmacen
 ADD CONSTRAINT f_galmacen8 FOREIGN KEY (tippro)
 REFERENCES gmovprod(codigo);