Crear PRIMARY KEY, FOREING KEY e INDICES Presupuesto

De CidesaWiki

PRIMARY KEY

ALTER TABLE ONLY cpadidis ADD CONSTRAINT i_cpadidis PRIMARY KEY (refadi);
ALTER TABLE ONLY cpajuste ADD CONSTRAINT i_cpajuste PRIMARY KEY (refaju);
ALTER TABLE ONLY cpasiini ADD CONSTRAINT i_cpasiini PRIMARY KEY (codpre, perpre, anopre);
ALTER TABLE ONLY cpcausad ADD CONSTRAINT i_cpcausad PRIMARY KEY (refcau);
ALTER TABLE ONLY cpcompro ADD CONSTRAINT i_cpcompro PRIMARY KEY (refcom);
ALTER TABLE ONLY cpdefniv ADD CONSTRAINT i_cpdefniv PRIMARY KEY (codemp);
ALTER TABLE ONLY cpdeftit ADD CONSTRAINT i_cpdeftit PRIMARY KEY (codpre);
ALTER TABLE ONLY cpdocaju ADD CONSTRAINT i_cpdocaju PRIMARY KEY (tipaju);
ALTER TABLE ONLY cpdoccau ADD CONSTRAINT i_cpdoccau PRIMARY KEY (tipcau);
ALTER TABLE ONLY cpdoccom ADD CONSTRAINT i_cpdoccom PRIMARY KEY (tipcom);
ALTER TABLE ONLY cpdocpag ADD CONSTRAINT i_cpdocpag PRIMARY KEY (tippag);
ALTER TABLE ONLY cpdocprc ADD CONSTRAINT i_cpdocprc PRIMARY KEY (tipprc);
ALTER TABLE ONLY cpmovadi ADD CONSTRAINT i_cpmovadi PRIMARY KEY (refadi, codpre);
ALTER TABLE ONLY cpmovaju ADD CONSTRAINT i_cpmovaju PRIMARY KEY (refaju, codpre);
ALTER TABLE ONLY cpmovtra ADD CONSTRAINT i_cpmovtra PRIMARY KEY (reftra, codori, coddes);
ALTER TABLE ONLY cpniveles ADD CONSTRAINT i_cpniveles PRIMARY KEY (catpar, consec);
ALTER TABLE ONLY cppagos ADD CONSTRAINT i_cppagos PRIMARY KEY (refpag);
ALTER TABLE ONLY cpprecom ADD CONSTRAINT i_cpprecom PRIMARY KEY (refprc);
ALTER TABLE ONLY cprelapa ADD CONSTRAINT i_cprelapa PRIMARY KEY (refrel);
ALTER TABLE ONLY cptrasla ADD CONSTRAINT i_cptrasla PRIMARY KEY (reftra);
ALTER TABLE ONLY cpunimed ADD CONSTRAINT i_cpunimed PRIMARY KEY (coduni);

FOREING KEY

ALTER TABLE ONLY cpdeftit
    ADD CONSTRAINT fkcpdeftitcodcta FOREIGN KEY (codcta) REFERENCES contabb(codcta);
ALTER TABLE ONLY cpasiini
    ADD CONSTRAINT fkcpasiinicodpre FOREIGN KEY (codpre) REFERENCES cpdeftit(codpre);
ALTER TABLE ONLY cpdisfuefin
    ADD CONSTRAINT fkcpdisfuefincodpre FOREIGN KEY (codpre) REFERENCES cpdeftit(codpre);
ALTER TABLE ONLY cpdisniv
    ADD CONSTRAINT fkcpdisnivcodpre FOREIGN KEY (codpre) REFERENCES cpdeftit(codpre);
ALTER TABLE ONLY cpimpapa
    ADD CONSTRAINT fkcpimpapacodpre FOREIGN KEY (codpre) REFERENCES cpdeftit(codpre);
ALTER TABLE ONLY cpimpcau
    ADD CONSTRAINT fkcpimpcaucodpre FOREIGN KEY (codpre) REFERENCES cpdeftit(codpre);
ALTER TABLE ONLY cpimpcom
    ADD CONSTRAINT fkcpimpcomcodpre FOREIGN KEY (codpre) REFERENCES cpdeftit(codpre);
ALTER TABLE ONLY cpimppag
    ADD CONSTRAINT fkcpimppagcodpre FOREIGN KEY (codpre) REFERENCES cpdeftit(codpre);
ALTER TABLE ONLY cpimpprc
    ADD CONSTRAINT fkcpimpprccodpre FOREIGN KEY (codpre) REFERENCES cpdeftit(codpre);
ALTER TABLE ONLY cpimprel
    ADD CONSTRAINT fkcpimprelcodpre FOREIGN KEY (codpre) REFERENCES cpdeftit(codpre);
ALTER TABLE ONLY cpmovadi
    ADD CONSTRAINT fkcpmovadicodpre FOREIGN KEY (codpre) REFERENCES cpdeftit(codpre);
ALTER TABLE ONLY cpmovaju
    ADD CONSTRAINT fkcpmovajucodpre FOREIGN KEY (codpre) REFERENCES cpdeftit(codpre);
ALTER TABLE ONLY cpmovfuefin
    ADD CONSTRAINT fkcpmovfuefincodpre FOREIGN KEY (codpre) REFERENCES cpdeftit(codpre);
ALTER TABLE ONLY cpsolmovadi
    ADD CONSTRAINT fkcpsolmovadicodpre FOREIGN KEY (codpre) REFERENCES cpdeftit(codpre);
ALTER TABLE ONLY cpsolmovtra
    ADD CONSTRAINT fkcpsolmovtraadicodori FOREIGN KEY (codori) REFERENCES cpdeftit(codpre);
ALTER TABLE ONLY cpsolmovtra
    ADD CONSTRAINT fkcpsolmovtraadicoddes FOREIGN KEY (coddes) REFERENCES cpdeftit(codpre);
ALTER TABLE ONLY cpmovtra
    ADD CONSTRAINT fkcpmovtraadicodori FOREIGN KEY (codori) REFERENCES cpdeftit(codpre);
ALTER TABLE ONLY cpmovtra
    ADD CONSTRAINT fkcpmovtraadicoddes FOREIGN KEY (coddes) REFERENCES cpdeftit(codpre);
ALTER TABLE ONLY cpmovadi
    ADD CONSTRAINT fkcpmovadirefadi FOREIGN KEY (refadi) REFERENCES cpadidis(refadi);
ALTER TABLE ONLY cpsolmovadi
    ADD CONSTRAINT fkcpsolmovadirefadi FOREIGN KEY (refadi) REFERENCES cpsoladidis(refadi);
ALTER TABLE ONLY cpmovtra
    ADD CONSTRAINT fkcpmovtrareftra FOREIGN KEY (reftra) REFERENCES cptrasla(reftra);
ALTER TABLE ONLY cpsolmovtra
    ADD CONSTRAINT fkcpsolmovtrareftra FOREIGN KEY (reftra) REFERENCES cpsoltrasla(reftra);
ALTER TABLE ONLY cpimpprc
    ADD CONSTRAINT fkcpimpprcrefprc FOREIGN KEY (refprc) REFERENCES cpprecom(refprc);
ALTER TABLE ONLY cpimpcom
    ADD CONSTRAINT fkcpimpcomrefcom FOREIGN KEY (refcom) REFERENCES cpcompro(refcom);
ALTER TABLE ONLY cpimpcau
    ADD CONSTRAINT fkcpimpcaurefcau FOREIGN KEY (refcau) REFERENCES cpcausad(refcau);
ALTER TABLE ONLY cpimppag
    ADD CONSTRAINT fkcpimppagrefpag FOREIGN KEY (refpag) REFERENCES cppagos(refpag);
ALTER TABLE ONLY cpmovaju
    ADD CONSTRAINT fkcpmovajurefaju FOREIGN KEY (refaju) REFERENCES cpajuste(refaju);
ALTER TABLE ONLY cpapafon
    ADD CONSTRAINT fkcpapafoncedrif FOREIGN KEY (cedrif) REFERENCES opbenefi(cedrif);
ALTER TABLE ONLY cpcausad
    ADD CONSTRAINT fkcpcausadcedrif FOREIGN KEY (cedrif) REFERENCES opbenefi(cedrif);
ALTER TABLE ONLY cpcompro
    ADD CONSTRAINT fkcpcomprocedrif FOREIGN KEY (cedrif) REFERENCES opbenefi(cedrif);
ALTER TABLE ONLY cppagos
    ADD CONSTRAINT fkcppagoscedrif FOREIGN KEY (cedrif) REFERENCES opbenefi(cedrif);
ALTER TABLE ONLY cpprecom
    ADD CONSTRAINT fkcpprecomcedrif FOREIGN KEY (cedrif) REFERENCES opbenefi(cedrif);
ALTER TABLE ONLY cprelapa
    ADD CONSTRAINT fkcprelapacedrif FOREIGN KEY (cedrif) REFERENCES opbenefi(cedrif);
ALTER TABLE ONLY cpajuste
    ADD CONSTRAINT fkcpajustetipaju FOREIGN KEY (tipaju) REFERENCES cpdocaju(tipaju);
ALTER TABLE ONLY cpapafon
    ADD CONSTRAINT pkcpapafon PRIMARY KEY (refapa);
ALTER TABLE ONLY cpartley
    ADD CONSTRAINT pkcpartley PRIMARY KEY (codart);
ALTER TABLE ONLY cpcausad
    ADD CONSTRAINT fkcpcausadtipcau FOREIGN KEY (tipcau) REFERENCES cpdoccau(tipcau);
ALTER TABLE ONLY cpcompro
    ADD CONSTRAINT fkcpcomprotipcom FOREIGN KEY (tipcom) REFERENCES cpdoccom(tipcom);
ALTER TABLE ONLY cpdisfuefin
    ADD CONSTRAINT pkcpdisfuefin PRIMARY KEY (correl);
ALTER TABLE ONLY cpdisfuefinacu
    ADD CONSTRAINT pkcpdisfuefinacu PRIMARY KEY (correl);
ALTER TABLE ONLY cpdisfuefinacu
    ADD CONSTRAINT fkcpdisfuefinacucodpre FOREIGN KEY (codpre) REFERENCES cpdeftit(codpre);
ALTER TABLE ONLY cpimpapa
    ADD CONSTRAINT fkcpimpaparefapa FOREIGN KEY (refapa) REFERENCES cpapafon(refapa);
ALTER TABLE ONLY cpimprel
    ADD CONSTRAINT fkcpimprelrefrel FOREIGN KEY (refrel) REFERENCES cprelapa(refrel);
ALTER TABLE ONLY cpmovadifin
    ADD CONSTRAINT pkcpmovadifin PRIMARY KEY (refadi, codfin);
ALTER TABLE ONLY cpmovadifin
    ADD CONSTRAINT fkcpmovadifinrefadi FOREIGN KEY (refadi) REFERENCES cpadidis(refadi);
ALTER TABLE ONLY cpmovfuefin
    ADD CONSTRAINT pkcpmovfuefin PRIMARY KEY (correl);
ALTER TABLE ONLY cppagos
    ADD CONSTRAINT fkcppagostippag FOREIGN KEY (tippag) REFERENCES cpdocpag(tippag);
ALTER TABLE ONLY cppereje
    ADD CONSTRAINT pkcppereje PRIMARY KEY (fecini, feccie, pereje);
ALTER TABLE ONLY cpprecom
    ADD CONSTRAINT fkcpprecomtipprc FOREIGN KEY (tipprc) REFERENCES cpdocprc(tipprc);

INDICES

CREATE INDEX i01cpdisniv ON cpdisniv USING btree (codpre);
CREATE INDEX i01cpimpcau ON cpimpcau USING btree (refcau);
CREATE INDEX i01cpimpcom ON cpimpcom USING btree (refcom);
CREATE INDEX i01cpimppag ON cpimppag USING btree (refpag);
CREATE INDEX i01cpmovadi ON cpmovadi USING btree (refadi);
CREATE INDEX i01cpmovtra ON cpmovtra USING btree (reftra);
CREATE INDEX i01cptrasla ON cptrasla USING btree (reftra, fectra);
CREATE INDEX i02cpasiini ON cpasiini USING btree (codpre);
CREATE INDEX i02cpimpcau ON cpimpcau USING btree (refcau, refere, refprc, codpre);
CREATE INDEX i02cpimpcom ON cpimpcom USING btree (refcom, refere, codpre);
CREATE INDEX i02cpimppag ON cpimppag USING btree (codpre);
CREATE INDEX i03cpasiini ON cpasiini USING btree (codpre, perpre);
CREATE INDEX i03cpimpcau ON cpimpcau USING btree (codpre);
CREATE INDEX i03cpimpcom ON cpimpcom USING btree (codpre);

--Pablosilva 19:54 27 nov 2008 (VET)