DDL SqlServer

DDL SqlServer

CREATE TABLE galmacen (
almid bigint identity(1,1) 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 datetime 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 char(4),
impre1 char(10),
impre2 char(10),
tipast char(4),
fecalt datetime default GETDATE() not null,
fecbaj datetime,
fecult datetime not null,
feccon datetime,
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 datetime,
auxfec2 datetime,
auxnum1 smallint,
auxnum2 smallint,
auxnum3 smallint,
user_created char(20) default user not null,
date_created datetime default GETDATE() not null,
user_updated char(20) default user not null,
date_updated datetime default GETDATE() 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')
)
);

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