DDL Postgres

DDL Postgres

CREATE TABLE galmacen (
    almid serial not null,
    codigo varchar(6) not null,
    nomalm varchar(40) not null,
    tercer varchar(12) not null,
    tipdir varchar(6) default '0' not null,
    empcode varchar(4) not null,
    agrupa varchar(4) not null,
    recint varchar(6) not null,
    delega varchar(6),
    depart varchar(6),
    codpre varchar(10),
    stkneg smallint default 0 not null,
    stkrup varchar(1) default 'N' not null,
    tipval varchar(1) not null,
    cosuni varchar(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 int,
    capdes decimal(11,3) default 0.000 not null,
    horini smallint default 0 not null,
    horfin smallint default 0 not null,
    tippro varchar(4),
    impre1 varchar(10),
    impre2 varchar(10),
    tipast varchar(4),
    fecalt date default CURRENT_DATE not null,
    fecbaj date,
    fecult date not null,
    feccon date,
    inipot smallint,
    estado varchar(1) not null,
    errnum smallint default 0 not null,
    wkfnum smallint,
    auxchr1 varchar(1),
    auxchr2 varchar(1),
    auxchr3 varchar(1),
    auxchr4 varchar(1),
    auxchr5 varchar(1),
    auxfec1 date,
    auxfec2 date,
    auxnum1 smallint,
    auxnum2 smallint,
    auxnum3 smallint,
    user_created varchar(20) default user not null,
    date_created timestamp default CURRENT_TIMESTAMP not null,
    user_updated varchar(20) default user not null,
    date_updated timestamp default CURRENT_TIMESTAMP not null
      , 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')
 )
) WITH OIDS;

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