remise à plat de toutes les clés étrangères

[cedric]

-- MISE A JOUR DE TOUTES LES CLES ETRANGERES !!!!!!!!
-- je ne lance pas sur nat
select ref.updatesql('{"azti","bgm","bresle","charente","fd80", "iav","inra","logrami","migado","migradour","mrm","saumonrhin","smatah"}',
'

ALTER TABLE ONLY tj_actionmarquage_act

DROP CONSTRAINT IF EXISTS c_fk_act_lot_identifiant;

ALTER TABLE ONLY tj_actionmarquage_act

ADD CONSTRAINT c_fk_act_lot_identifiant FOREIGN KEY (act_lot_identifiant, act_org_code) REFERENCES t_lot_lot(lot_identifiant, lot_org_code) ON UPDATE CASCADE;

ALTER TABLE ONLY tj_actionmarquage_act
DROP CONSTRAINT IF EXISTS c_fk_act_mqe_reference ;

ALTER TABLE ONLY tj_actionmarquage_act

ADD CONSTRAINT c_fk_act_mqe_reference FOREIGN KEY (act_mqe_reference, act_org_code) REFERENCES t_marque_mqe(mqe_reference, mqe_org_code) ON UPDATE CASCADE;

ALTER TABLE ONLY tj_actionmarquage_act

DROP CONSTRAINT IF EXISTS c_fk_act_org_code ;

ALTER TABLE ONLY tj_actionmarquage_act

ADD CONSTRAINT c_fk_act_org_code FOREIGN KEY (act_org_code) REFERENCES ref.ts_organisme_org(org_code) MATCH FULL ON UPDATE CASCADE;

---

ALTER TABLE ONLY t_bilanmigrationjournalier_bjo

DROP CONSTRAINT IF EXISTS c_fk_bjo_std_code;

ALTER TABLE ONLY t_bilanmigrationjournalier_bjo

ADD CONSTRAINT c_fk_bjo_std_code FOREIGN KEY (bjo_std_code) REFERENCES ref.tr_stadedeveloppement_std(std_code) ON UPDATE CASCADE;

ALTER TABLE ONLY t_bilanmigrationjournalier_bjo

DROP CONSTRAINT IF EXISTS c_fk_bjo_tax_code;

ALTER TABLE ONLY t_bilanmigrationjournalier_bjo

ADD CONSTRAINT c_fk_bjo_tax_code FOREIGN KEY (bjo_tax_code) REFERENCES ref.tr_taxon_tax(tax_code) ON UPDATE CASCADE;

/* contrainte manquante v0.4*/

ALTER TABLE t_bilanmigrationjournalier_bjo

DROP CONSTRAINT IF EXISTS c_fk_bjo_org_code;

ALTER TABLE t_bilanmigrationjournalier_bjo

ADD CONSTRAINT c_fk_bjo_org_code FOREIGN KEY (bjo_org_code)

REFERENCES ref.ts_organisme_org (org_code) MATCH FULL
ON UPDATE CASCADE ON DELETE NO ACTION;

---

ALTER TABLE ONLY t_bilanmigrationmensuel_bme

DROP CONSTRAINT IF EXISTS c_fk_bme_std_code;

ALTER TABLE ONLY t_bilanmigrationmensuel_bme

ADD CONSTRAINT c_fk_bme_std_code FOREIGN KEY (bme_std_code) REFERENCES ref.tr_stadedeveloppement_std(std_code) ON UPDATE CASCADE;

ALTER TABLE ONLY t_bilanmigrationmensuel_bme

DROP CONSTRAINT IF EXISTS c_fk_bme_tax_code;

ALTER TABLE ONLY t_bilanmigrationmensuel_bme

ADD CONSTRAINT c_fk_bme_tax_code FOREIGN KEY (bme_tax_code) REFERENCES ref.tr_taxon_tax(tax_code) ON UPDATE CASCADE;

---

ALTER TABLE ONLY tj_caracteristiquelot_car

DROP CONSTRAINT IF EXISTS c_fk_car_lot_identifiant;

ALTER TABLE ONLY tj_caracteristiquelot_car

ADD CONSTRAINT c_fk_car_lot_identifiant FOREIGN KEY (car_lot_identifiant, car_org_code) REFERENCES t_lot_lot(lot_identifiant, lot_org_code) ON UPDATE CASCADE;

ALTER TABLE ONLY tj_caracteristiquelot_car

DROP CONSTRAINT IF EXISTS c_fk_car_org_code;

ALTER TABLE ONLY tj_caracteristiquelot_car

ADD CONSTRAINT c_fk_car_org_code FOREIGN KEY (car_org_code) REFERENCES ref.ts_organisme_org(org_code) MATCH FULL ON UPDATE CASCADE;

ALTER TABLE ONLY tj_caracteristiquelot_car

DROP CONSTRAINT IF EXISTS c_fk_car_par_code;

ALTER TABLE ONLY tj_caracteristiquelot_car

ADD CONSTRAINT c_fk_car_par_code FOREIGN KEY (car_par_code) REFERENCES ref.tg_parametre_par(par_code) ON UPDATE CASCADE;

ALTER TABLE ONLY tj_caracteristiquelot_car

DROP CONSTRAINT IF EXISTS c_fk_car_val_identifiant;

ALTER TABLE ONLY tj_caracteristiquelot_car

ADD CONSTRAINT c_fk_car_val_identifiant FOREIGN KEY (car_val_identifiant) REFERENCES ref.tr_valeurparametrequalitatif_val(val_identifiant) ON UPDATE CASCADE;

---

ALTER TABLE ONLY tj_coefficientconversion_coe

DROP CONSTRAINT IF EXISTS c_fk_coe_org_code;

ALTER TABLE ONLY tj_coefficientconversion_coe

ADD CONSTRAINT c_fk_coe_org_code FOREIGN KEY (coe_org_code) REFERENCES ref.ts_organisme_org(org_code) ON UPDATE CASCADE;

ALTER TABLE ONLY tj_coefficientconversion_coe

DROP CONSTRAINT IF EXISTS c_fk_coe_qte_code;

ALTER TABLE ONLY tj_coefficientconversion_coe

ADD CONSTRAINT c_fk_coe_qte_code FOREIGN KEY (coe_qte_code) REFERENCES ref.tr_typequantitelot_qte(qte_code) ON UPDATE CASCADE;

ALTER TABLE ONLY tj_coefficientconversion_coe

DROP CONSTRAINT IF EXISTS c_fk_coe_std_code;

ALTER TABLE ONLY tj_coefficientconversion_coe

ADD CONSTRAINT c_fk_coe_std_code FOREIGN KEY (coe_std_code) REFERENCES ref.tr_stadedeveloppement_std(std_code) ON UPDATE CASCADE;

ALTER TABLE ONLY tj_coefficientconversion_coe

DROP CONSTRAINT IF EXISTS c_fk_coe_tax_code;

ALTER TABLE ONLY tj_coefficientconversion_coe

ADD CONSTRAINT c_fk_coe_tax_code FOREIGN KEY (coe_tax_code) REFERENCES ref.tr_taxon_tax(tax_code) ON UPDATE CASCADE;

---

ALTER TABLE ONLY tj_dfesttype_dft

DROP CONSTRAINT IF EXISTS c_fk_dft_df_identifiant;

ALTER TABLE ONLY tj_dfesttype_dft

ADD CONSTRAINT c_fk_dft_df_identifiant FOREIGN KEY (dft_df_identifiant, dft_org_code) REFERENCES tg_dispositif_dis(dis_identifiant, dis_org_code) ON UPDATE CASCADE;

ALTER TABLE ONLY tj_dfesttype_dft

DROP CONSTRAINT IF EXISTS c_fk_dft_org_code;

ALTER TABLE ONLY tj_dfesttype_dft

ADD CONSTRAINT c_fk_dft_org_code FOREIGN KEY (dft_org_code) REFERENCES ref.ts_organisme_org(org_code) MATCH FULL ON UPDATE CASCADE;

---

-- ? contrainte unique manquante?

--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

ALTER TABLE ONLY t_dispositiffranchissement_dif

DROP CONSTRAINT IF EXISTS c_uk_dif_dis_identifiant CASCADE;

ALTER TABLE ONLY t_dispositiffranchissement_dif

ADD CONSTRAINT c_uk_dif_dis_identifiant unique (dif_dis_identifiant,dif_org_code);

--(manque une contrainte unique dans la table t_dispositifcomptage_dic

ALTER TABLE t_dispositifcomptage_dic DROP CONSTRAINT IF EXISTS c_uk_dic_dis_identifiant CASCADE;

ALTER TABLE t_dispositifcomptage_dic ADD CONSTRAINT c_uk_dic_dis_identifiant unique (dic_dis_identifiant,dic_org_code);
-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

ALTER TABLE ONLY t_dispositifcomptage_dic

DROP CONSTRAINT IF EXISTS c_fk_dic_dif_identifiant ;

ALTER TABLE ONLY t_dispositifcomptage_dic

ADD CONSTRAINT c_fk_dic_dif_identifiant FOREIGN KEY (dic_dif_identifiant, dic_org_code) REFERENCES t_dispositiffranchissement_dif(dif_dis_identifiant, dif_org_code) ON UPDATE CASCADE;

ALTER TABLE ONLY t_dispositifcomptage_dic

DROP CONSTRAINT IF EXISTS c_fk_dic_dis_identifiant;

ALTER TABLE ONLY t_dispositifcomptage_dic

ADD CONSTRAINT c_fk_dic_dis_identifiant FOREIGN KEY (dic_dis_identifiant, dic_org_code) REFERENCES tg_dispositif_dis(dis_identifiant, dis_org_code) ON UPDATE CASCADE;

ALTER TABLE ONLY t_dispositifcomptage_dic

DROP CONSTRAINT IF EXISTS c_fk_dic_org_code;

ALTER TABLE ONLY t_dispositifcomptage_dic

ADD CONSTRAINT c_fk_dic_org_code FOREIGN KEY (dic_org_code) REFERENCES ref.ts_organisme_org(org_code) MATCH FULL ON UPDATE CASCADE;

ALTER TABLE ONLY t_dispositifcomptage_dic

DROP CONSTRAINT IF EXISTS c_fk_dic_tdc_code;

ALTER TABLE ONLY t_dispositifcomptage_dic

ADD CONSTRAINT c_fk_dic_tdc_code FOREIGN KEY (dic_tdc_code) REFERENCES ref.tr_typedc_tdc(tdc_code) ON UPDATE CASCADE;

---

ALTER TABLE ONLY t_dispositiffranchissement_dif

DROP CONSTRAINT IF EXISTS c_fk_dif_dis_identifiant;

ALTER TABLE ONLY t_dispositiffranchissement_dif

ADD CONSTRAINT c_fk_dif_dis_identifiant FOREIGN KEY (dif_dis_identifiant, dif_org_code) REFERENCES tg_dispositif_dis(dis_identifiant, dis_org_code) ON UPDATE CASCADE;

ALTER TABLE ONLY t_dispositiffranchissement_dif

DROP CONSTRAINT IF EXISTS c_fk_dif_org_code;

ALTER TABLE ONLY t_dispositiffranchissement_dif

ADD CONSTRAINT c_fk_dif_org_code FOREIGN KEY (dif_org_code) REFERENCES ref.ts_organisme_org(org_code) MATCH FULL ON UPDATE CASCADE;

ALTER TABLE ONLY t_dispositiffranchissement_dif

DROP CONSTRAINT IF EXISTS c_fk_dif_ouv_identifiant;

ALTER TABLE ONLY t_dispositiffranchissement_dif

ADD CONSTRAINT c_fk_dif_ouv_identifiant FOREIGN KEY (dif_ouv_identifiant, dif_org_code) REFERENCES t_ouvrage_ouv(ouv_identifiant, ouv_org_code) ON UPDATE CASCADE;

---

ALTER TABLE ONLY tj_dfestdestinea_dtx

DROP CONSTRAINT IF EXISTS c_fk_dtx_dif_identifiant;

ALTER TABLE ONLY tj_dfestdestinea_dtx

ADD CONSTRAINT c_fk_dtx_dif_identifiant FOREIGN KEY (dtx_dif_identifiant, dtx_org_code) REFERENCES tg_dispositif_dis(dis_identifiant, dis_org_code) ON UPDATE CASCADE;

ALTER TABLE ONLY tj_dfestdestinea_dtx

DROP CONSTRAINT IF EXISTS c_fk_dtx_org_code;

ALTER TABLE ONLY tj_dfestdestinea_dtx

ADD CONSTRAINT c_fk_dtx_org_code FOREIGN KEY (dtx_org_code) REFERENCES ref.ts_organisme_org(org_code) MATCH FULL ON UPDATE CASCADE;

ALTER TABLE ONLY tj_dfestdestinea_dtx

DROP CONSTRAINT IF EXISTS c_fk_dtx_tax_code;

ALTER TABLE ONLY tj_dfestdestinea_dtx

ADD CONSTRAINT c_fk_dtx_tax_code FOREIGN KEY (dtx_tax_code) REFERENCES ref.tr_taxon_tax(tax_code) ON UPDATE CASCADE;

---

-- valeur manquante (iav)

ALTER TABLE tj_dfesttype_dft

DROP CONSTRAINT IF EXISTS c_fk_dft_tdf_code;

ALTER TABLE tj_dfesttype_dft

ADD CONSTRAINT c_fk_dft_tdf_code FOREIGN KEY (dft_tdf_code)

REFERENCES ref.tr_typedf_tdf (tdf_code) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION;

---

ALTER TABLE ONLY tj_conditionenvironnementale_env

DROP CONSTRAINT IF EXISTS c_fk_env_org_code;

ALTER TABLE ONLY tj_conditionenvironnementale_env

ADD CONSTRAINT c_fk_env_org_code FOREIGN KEY (env_org_code) REFERENCES ref.ts_organisme_org(org_code) MATCH FULL ON UPDATE CASCADE;

ALTER TABLE ONLY tj_conditionenvironnementale_env

DROP CONSTRAINT IF EXISTS c_fk_env_stm_identifiant;

ALTER TABLE ONLY tj_conditionenvironnementale_env

ADD CONSTRAINT c_fk_env_stm_identifiant FOREIGN KEY (env_stm_identifiant, env_org_code) REFERENCES tj_stationmesure_stm(stm_identifiant, stm_org_code) ON UPDATE CASCADE;

ALTER TABLE ONLY tj_conditionenvironnementale_env

DROP CONSTRAINT IF EXISTS c_fk_env_val_identifiant;

ALTER TABLE ONLY tj_conditionenvironnementale_env

ADD CONSTRAINT c_fk_env_val_identifiant FOREIGN KEY (env_val_identifiant) REFERENCES ref.tr_valeurparametrequalitatif_val(val_identifiant) ON UPDATE CASCADE;

---

ALTER TABLE ONLY t_lot_lot

DROP CONSTRAINT IF EXISTS c_fk_lot_dev_code;

ALTER TABLE ONLY t_lot_lot

ADD CONSTRAINT c_fk_lot_dev_code FOREIGN KEY (lot_dev_code) REFERENCES ref.tr_devenirlot_dev(dev_code) ON UPDATE CASCADE;

ALTER TABLE ONLY t_lot_lot

DROP CONSTRAINT IF EXISTS c_fk_lot_lot_identifiant;

ALTER TABLE ONLY t_lot_lot

ADD CONSTRAINT c_fk_lot_lot_identifiant FOREIGN KEY (lot_lot_identifiant, lot_org_code) REFERENCES t_lot_lot(lot_identifiant, lot_org_code) ON UPDATE CASCADE;

ALTER TABLE ONLY t_lot_lot

DROP CONSTRAINT IF EXISTS c_fk_lot_org_code;

ALTER TABLE ONLY t_lot_lot

ADD CONSTRAINT c_fk_lot_org_code FOREIGN KEY (lot_org_code) REFERENCES ref.ts_organisme_org(org_code) MATCH FULL ON UPDATE CASCADE;

ALTER TABLE ONLY t_lot_lot

DROP CONSTRAINT IF EXISTS c_fk_lot_qte_code;

ALTER TABLE ONLY t_lot_lot

ADD CONSTRAINT c_fk_lot_qte_code FOREIGN KEY (lot_qte_code) REFERENCES ref.tr_typequantitelot_qte(qte_code) ON UPDATE CASCADE;

ALTER TABLE ONLY t_lot_lot

DROP CONSTRAINT IF EXISTS c_fk_lot_std_code;

ALTER TABLE ONLY t_lot_lot

ADD CONSTRAINT c_fk_lot_std_code FOREIGN KEY (lot_std_code) REFERENCES ref.tr_stadedeveloppement_std(std_code) ON UPDATE CASCADE;

ALTER TABLE ONLY t_lot_lot

DROP CONSTRAINT IF EXISTS c_fk_lot_tax_code;

ALTER TABLE ONLY t_lot_lot

ADD CONSTRAINT c_fk_lot_tax_code FOREIGN KEY (lot_tax_code) REFERENCES ref.tr_taxon_tax(tax_code) ON UPDATE CASCADE;

---

ALTER TABLE ONLY t_marque_mqe

DROP CONSTRAINT IF EXISTS c_fk_mqe_loc_code;

ALTER TABLE ONLY t_marque_mqe

ADD CONSTRAINT c_fk_mqe_loc_code FOREIGN KEY (mqe_loc_code) REFERENCES ref.tr_localisationanatomique_loc(loc_code) ON UPDATE CASCADE;

ALTER TABLE ONLY t_marque_mqe

DROP CONSTRAINT IF EXISTS c_fk_mqe_nmq_code;

ALTER TABLE ONLY t_marque_mqe

ADD CONSTRAINT c_fk_mqe_nmq_code FOREIGN KEY (mqe_nmq_code) REFERENCES ref.tr_naturemarque_nmq(nmq_code) ON UPDATE CASCADE;

ALTER TABLE ONLY t_marque_mqe

DROP CONSTRAINT IF EXISTS c_fk_mqe_omq_reference;

ALTER TABLE ONLY t_marque_mqe

ADD CONSTRAINT c_fk_mqe_omq_reference FOREIGN KEY (mqe_omq_reference, mqe_org_code) REFERENCES t_operationmarquage_omq(omq_reference, omq_org_code) ON UPDATE CASCADE;

ALTER TABLE ONLY t_marque_mqe

DROP CONSTRAINT IF EXISTS c_fk_mqe_org_code;

ALTER TABLE ONLY t_marque_mqe

ADD CONSTRAINT c_fk_mqe_org_code FOREIGN KEY (mqe_org_code) REFERENCES ref.ts_organisme_org(org_code) MATCH FULL ON UPDATE CASCADE;

---

ALTER TABLE ONLY t_operationmarquage_omq

DROP CONSTRAINT IF EXISTS c_fk_omq_org_code;

ALTER TABLE ONLY t_operationmarquage_omq

ADD CONSTRAINT c_fk_omq_org_code FOREIGN KEY (omq_org_code) REFERENCES ref.ts_organisme_org(org_code) ON UPDATE CASCADE;

---

ALTER TABLE ONLY t_operation_ope

DROP CONSTRAINT IF EXISTS c_fk_ope_dic_identifiant;

ALTER TABLE ONLY t_operation_ope

ADD CONSTRAINT c_fk_ope_dic_identifiant FOREIGN KEY (ope_dic_identifiant, ope_org_code) REFERENCES tg_dispositif_dis(dis_identifiant, dis_org_code) ON UPDATE CASCADE;

ALTER TABLE ONLY t_operation_ope

DROP CONSTRAINT IF EXISTS c_fk_ope_org_code;

ALTER TABLE ONLY t_operation_ope

ADD CONSTRAINT c_fk_ope_org_code FOREIGN KEY (ope_org_code) REFERENCES ref.ts_organisme_org(org_code) MATCH FULL ON UPDATE CASCADE;

---

ALTER TABLE ONLY t_ouvrage_ouv

DROP CONSTRAINT IF EXISTS c_fk_ouv_nov_code;

ALTER TABLE ONLY t_ouvrage_ouv

ADD CONSTRAINT c_fk_ouv_nov_code FOREIGN KEY (ouv_nov_code) REFERENCES ref.tr_natureouvrage_nov(nov_code) ON UPDATE CASCADE;

ALTER TABLE ONLY t_ouvrage_ouv

DROP CONSTRAINT IF EXISTS c_fk_ouv_org_code;

ALTER TABLE ONLY t_ouvrage_ouv

ADD CONSTRAINT c_fk_ouv_org_code FOREIGN KEY (ouv_org_code) REFERENCES ref.ts_organisme_org(org_code) MATCH FULL ON UPDATE CASCADE;

-- contrainte manquante au schéma IAV

ALTER TABLE t_ouvrage_ouv

DROP CONSTRAINT IF EXISTS c_fk_ouv_sta_code;

ALTER TABLE t_ouvrage_ouv

ADD CONSTRAINT c_fk_ouv_sta_code FOREIGN KEY (ouv_sta_code, ouv_org_code)

REFERENCES t_station_sta (sta_code, sta_org_code) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION;

---

ALTER TABLE ONLY tj_pathologieconstatee_pco

DROP CONSTRAINT IF EXISTS c_fk_pco_loc_code;

ALTER TABLE ONLY tj_pathologieconstatee_pco

ADD CONSTRAINT c_fk_pco_loc_code FOREIGN KEY (pco_loc_code) REFERENCES ref.tr_localisationanatomique_loc(loc_code) ON UPDATE CASCADE;

ALTER TABLE ONLY tj_pathologieconstatee_pco

DROP CONSTRAINT IF EXISTS c_fk_pco_lot_identifiant;

ALTER TABLE ONLY tj_pathologieconstatee_pco

ADD CONSTRAINT c_fk_pco_lot_identifiant FOREIGN KEY (pco_lot_identifiant, pco_org_code) REFERENCES t_lot_lot(lot_identifiant, lot_org_code) ON UPDATE CASCADE;

ALTER TABLE ONLY tj_pathologieconstatee_pco

DROP CONSTRAINT IF EXISTS c_fk_pco_org_code ;

ALTER TABLE ONLY tj_pathologieconstatee_pco

ADD CONSTRAINT c_fk_pco_org_code FOREIGN KEY (pco_org_code) REFERENCES ref.ts_organisme_org(org_code) MATCH FULL ON UPDATE CASCADE;

ALTER TABLE ONLY tj_pathologieconstatee_pco

DROP CONSTRAINT IF EXISTS c_fk_pco_pat_code ;

ALTER TABLE ONLY tj_pathologieconstatee_pco

ADD CONSTRAINT c_fk_pco_pat_code FOREIGN KEY (pco_pat_code) REFERENCES ref.tr_pathologie_pat(pat_code) ON UPDATE CASCADE;

/*
--v0.4 insertion dune contrainte vers une nouvelle table.

alter table tj_pathologieconstatee_pco DROP CONSTRAINT IF EXISTS c_fk_pco_imp_code ;

alter table tj_pathologieconstatee_pco ADD CONSTRAINT c_fk_pco_imp_code FOREIGN KEY (pco_imp_code)

REFERENCES ref.tr_importancepatho_imp (imp_code) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION;

*/

---

ALTER TABLE ONLY t_periodefonctdispositif_per

DROP CONSTRAINT IF EXISTS c_fk_per_dis_identifiant;

ALTER TABLE ONLY t_periodefonctdispositif_per

ADD CONSTRAINT c_fk_per_dis_identifiant FOREIGN KEY (per_dis_identifiant, per_org_code) REFERENCES tg_dispositif_dis(dis_identifiant, dis_org_code) ON UPDATE CASCADE;

ALTER TABLE ONLY t_periodefonctdispositif_per

DROP CONSTRAINT IF EXISTS c_fk_per_org_code ;

ALTER TABLE ONLY t_periodefonctdispositif_per

ADD CONSTRAINT c_fk_per_org_code FOREIGN KEY (per_org_code) REFERENCES ref.ts_organisme_org(org_code) MATCH FULL ON UPDATE CASCADE;

ALTER TABLE ONLY t_periodefonctdispositif_per

DROP CONSTRAINT IF EXISTS c_fk_per_tar_code;

ALTER TABLE ONLY t_periodefonctdispositif_per

ADD CONSTRAINT c_fk_per_tar_code FOREIGN KEY (per_tar_code) REFERENCES ref.tr_typearretdisp_tar(tar_code) ON UPDATE CASCADE;

---

ALTER TABLE ONLY tj_prelevementlot_prl

DROP CONSTRAINT IF EXISTS c_fk_prl_loc_code;

ALTER TABLE ONLY tj_prelevementlot_prl

ADD CONSTRAINT c_fk_prl_loc_code FOREIGN KEY (prl_loc_code) REFERENCES ref.tr_localisationanatomique_loc(loc_code) ON UPDATE CASCADE;

ALTER TABLE ONLY tj_prelevementlot_prl

DROP CONSTRAINT IF EXISTS c_fk_prl_lot_identifiant;

ALTER TABLE ONLY tj_prelevementlot_prl

ADD CONSTRAINT c_fk_prl_lot_identifiant FOREIGN KEY (prl_lot_identifiant, prl_org_code) REFERENCES t_lot_lot(lot_identifiant, lot_org_code) ON UPDATE CASCADE;

ALTER TABLE ONLY tj_prelevementlot_prl

DROP CONSTRAINT IF EXISTS c_fk_prl_typeprelevement;

ALTER TABLE ONLY tj_prelevementlot_prl

ADD CONSTRAINT c_fk_prl_typeprelevement FOREIGN KEY (prl_pre_typeprelevement) REFERENCES ref.tr_prelevement_pre(pre_typeprelevement) ON UPDATE CASCADE;

---

ALTER TABLE ONLY t_station_sta

DROP CONSTRAINT IF EXISTS c_fk_sta_org_code;

ALTER TABLE ONLY t_station_sta

ADD CONSTRAINT c_fk_sta_org_code FOREIGN KEY (sta_org_code) REFERENCES ref.ts_organisme_org(org_code) MATCH FULL ON UPDATE CASCADE;

---

ALTER TABLE ONLY tg_dispositif_dis

DROP CONSTRAINT IF EXISTS c_fk_sta_org_code;

ALTER TABLE ONLY tg_dispositif_dis

ADD CONSTRAINT c_fk_sta_org_code FOREIGN KEY (dis_org_code) REFERENCES ref.ts_organisme_org(org_code) MATCH FULL ON UPDATE CASCADE;

---

ALTER TABLE ONLY tj_stationmesure_stm

DROP CONSTRAINT IF EXISTS c_fk_stm_org_code;

ALTER TABLE ONLY tj_stationmesure_stm

ADD CONSTRAINT c_fk_stm_org_code FOREIGN KEY (stm_org_code) REFERENCES ref.ts_organisme_org(org_code) MATCH FULL ON UPDATE CASCADE;

ALTER TABLE ONLY tj_stationmesure_stm

DROP CONSTRAINT IF EXISTS c_fk_stm_par_code;

ALTER TABLE ONLY tj_stationmesure_stm

ADD CONSTRAINT c_fk_stm_par_code FOREIGN KEY (stm_par_code) REFERENCES ref.tg_parametre_par(par_code) ON UPDATE CASCADE;

ALTER TABLE ONLY tj_stationmesure_stm

DROP CONSTRAINT IF EXISTS c_fk_stm_sta_code;

ALTER TABLE ONLY tj_stationmesure_stm

ADD CONSTRAINT c_fk_stm_sta_code FOREIGN KEY (stm_sta_code, stm_org_code) REFERENCES t_station_sta(sta_code, sta_org_code) ON UPDATE CASCADE;

---

ALTER TABLE ONLY tj_tauxechappement_txe

DROP CONSTRAINT IF EXISTS c_fk_txe_ech_code;

ALTER TABLE ONLY tj_tauxechappement_txe

ADD CONSTRAINT c_fk_txe_ech_code FOREIGN KEY (txe_ech_code) REFERENCES ref.tr_niveauechappement_ech(ech_code) ON UPDATE CASCADE;

ALTER TABLE ONLY tj_tauxechappement_txe

DROP CONSTRAINT IF EXISTS c_fk_txe_org_code;

ALTER TABLE ONLY tj_tauxechappement_txe

ADD CONSTRAINT c_fk_txe_org_code FOREIGN KEY (txe_org_code) REFERENCES ref.ts_organisme_org(org_code) MATCH FULL ON UPDATE CASCADE;

/*
--changement v0.4 ci dessous la clé étrangère se rattache à une station
-- ATTENTION NECESSSITE V0.4

ALTER TABLE ONLY tj_tauxechappement_txe

DROP CONSTRAINT IF EXISTS c_fk_txe_sta_identifiant;

ALTER TABLE ONLY tj_tauxechappement_txe

ADD CONSTRAINT c_fk_txe_sta_identifiant FOREIGN KEY (txe_sta_identifiant, txe_org_code) REFERENCES t_station_sta(sta_identifiant, ouv_org_code) ON UPDATE CASCADE;

*/

ALTER TABLE ONLY tj_tauxechappement_txe

DROP CONSTRAINT IF EXISTS c_fk_txe_std_code;

ALTER TABLE ONLY tj_tauxechappement_txe

ADD CONSTRAINT c_fk_txe_std_code FOREIGN KEY (txe_std_code) REFERENCES ref.tr_stadedeveloppement_std(std_code) ON UPDATE CASCADE;

ALTER TABLE ONLY tj_tauxechappement_txe

DROP CONSTRAINT IF EXISTS c_fk_txe_tax_code;

ALTER TABLE ONLY tj_tauxechappement_txe

ADD CONSTRAINT c_fk_txe_tax_code FOREIGN KEY (txe_tax_code) REFERENCES ref.tr_taxon_tax(tax_code) ON UPDATE CASCADE;

---

-- clé manquante org

ALTER TABLE ONLY ts_taxonvideo_txv

DROP CONSTRAINT IF EXISTS c_fk_txv_org_code;

ALTER TABLE ONLY ts_taxonvideo_txv

ADD CONSTRAINT c_fk_txv_org_code FOREIGN KEY (txv_org_code) REFERENCES ref.ts_organisme_org(org_code) MATCH FULL ON UPDATE CASCADE;

ALTER TABLE ONLY ts_taxonvideo_txv

DROP CONSTRAINT IF EXISTS c_fk_txv_tax_code ;

ALTER TABLE ONLY ts_taxonvideo_txv

ADD CONSTRAINT c_fk_txv_tax_code FOREIGN KEY (txv_tax_code) REFERENCES ref.tr_taxon_tax(tax_code) ON UPDATE CASCADE;

ALTER TABLE ONLY ts_taxonvideo_txv

DROP CONSTRAINT IF EXISTS c_fk_std_code;

ALTER TABLE ONLY ts_taxonvideo_txv

ADD CONSTRAINT c_fk_std_code FOREIGN KEY (txv_std_code) REFERENCES ref.tr_stadedeveloppement_std(std_code) ON UPDATE CASCADE;

---

ALTER TABLE ONLY ts_taillevideo_tav

DROP CONSTRAINT IF EXISTS c_fk_tav_dic_identifiant;

ALTER TABLE ONLY ts_taillevideo_tav

ADD CONSTRAINT c_fk_tav_dic_identifiant FOREIGN KEY (tav_dic_identifiant, tav_org_code) REFERENCES t_dispositifcomptage_dic(dic_dis_identifiant, dic_org_code) ON UPDATE CASCADE;

-- clé manquante org

ALTER TABLE ONLY ts_taillevideo_tav

DROP CONSTRAINT IF EXISTS c_fk_tav_org_code;

ALTER TABLE ONLY ts_taillevideo_tav

ADD CONSTRAINT c_fk_tav_org_code FOREIGN KEY (tav_org_code) REFERENCES ref.ts_organisme_org(org_code) MATCH FULL ON UPDATE CASCADE;

');