-- public.dossier definition

-- Drop table

-- DROP TABLE dossier;

CREATE TABLE dossier (
	id_dossier serial4 NOT NULL,
	co_type_dossier varchar(2) NOT NULL,
	departement varchar(3) NOT NULL,
	n_commune int4 NOT NULL,
	annee int4 NOT NULL,
	hn_dossier varchar(1) NOT NULL,
	bn_dossier varchar(4) NOT NULL,
	n_maj varchar(3) NULL,
	nom_dossier varchar(25) NOT NULL,
	id_etape_courante int4 NULL,
	id_dossier_modifie int4 NULL,
	motif_dossier_modifie varchar(1) NULL,
	observation_objet_modification text NULL,
	depot_portail varchar(1) NULL DEFAULT 'N'::character varying,
	id_protection int4 NOT NULL DEFAULT 1,
	x float8 NULL,
	y float8 NULL,
	n_archive varchar(10) NULL,
	prioritaire varchar(1) NULL DEFAULT 'N'::character varying,
	id_objet_demande int4 NULL,
	precision_objet_demande varchar(200) NULL,
	co_autorite_competente varchar(1) NULL,
	opposition_cnil varchar(1) NULL DEFAULT 'N'::character varying,
	n_voirie_terrain varchar(20) NULL,
	adresse_terrain varchar(60) NULL,
	complement_adresse_terrain varchar(60) NULL,
	rivoli_terrain varchar(4) NULL,
	code_postal_terrain varchar(5) NULL,
	boite_postale_terrain varchar(5) NULL,
	cedex_terrain varchar(2) NULL,
	lieu_dit_terrain varchar(30) NULL,
	id_secteur int4 NULL,
	canton_terrain varchar(50) NULL,
	quartier_terrain varchar(100) NULL,
	hors_agglomeration varchar(1) NULL DEFAULT 'N'::character varying,
	pr_routier_debut float8 NULL,
	pr_routier_fin float8 NULL,
	superficie_terrain float8 NULL,
	superficie_constructible float8 NULL,
	sf_construction_existante float8 NULL,
	sf_terrain_avant_division float8 NULL,
	co_programme_amenagement varchar(10) NULL,
	id_lotissement int4 NULL,
	n_lot text NULL,
	shon_lot float8 NULL,
	sf_constructible_lot float8 NULL,
	grande_propriete varchar(1) NULL DEFAULT 'N'::character varying,
	date_acquisition date NULL,
	mutation varchar(1) NULL DEFAULT 'N'::character varying,
	parcelle_lotie varchar(1) NULL DEFAULT 'N'::character varying,
	n_pc varchar(20) NULL,
	n_arrete_pc varchar(20) NULL,
	date_arrete_pc date NULL,
	beneficiaire_pc varchar(30) NULL,
	n_cu varchar(20) NULL,
	date_cu date NULL,
	beneficiaire_cu varchar(30) NULL,
	perimetre_remembrement varchar(1) NULL DEFAULT 'N'::character varying,
	objet_remembrement varchar(1) NULL DEFAULT 'N'::character varying,
	date_remembrement date NULL,
	division_batie varchar(1) NULL DEFAULT 'N'::character varying,
	id_preemption int4 NULL,
	date_demande date NULL,
	date_depot date NOT NULL,
	date_saisie date NULL,
	date_modification date NULL,
	date_dossier_complete date NULL,
	date_dossier_incomplet date NULL,
	date_affichage_papier date NULL,
	date_envoi_service_instructeur date NULL,
	date_recept_serv_instructeur date NULL,
	date_notification_delais date NULL,
	date_notification_delais_acr date NULL,
	date_prolongation date NULL,
	date_limite_instruction date NULL,
	date_transfert date NULL,
	date_demande_conformite date NULL,
	date_envoi_conformite date NULL,
	id_situation_projet int4 NULL,
	distance_construction float8 NULL,
	batiment_existant varchar(1) NULL DEFAULT '?'::character varying,
	batiment_demoli varchar(1) NULL DEFAULT '?'::character varying,
	installation_nuisible varchar(1) NULL DEFAULT '?'::character varying,
	observation_situation text NULL,
	id_desserte_voirie int4 NULL,
	date_voirie date NULL,
	largeur_voirie float8 NULL,
	revetement_voirie varchar(20) NULL,
	adaptation_voirie varchar(1) NULL DEFAULT '?'::character varying,
	alignement_voirie varchar(1) NULL DEFAULT '?'::character varying,
	cession_voirie varchar(1) NULL DEFAULT '?'::character varying,
	observation_voirie text NULL,
	id_desserte_eau int4 NULL,
	date_eau date NULL,
	diametre_eau varchar(10) NULL,
	adaptation_eau varchar(1) NULL DEFAULT '?'::character varying,
	observation_eau text NULL,
	id_desserte_gaz int4 NULL,
	date_gaz date NULL,
	adaptation_gaz varchar(1) NULL DEFAULT '?'::character varying,
	observation_gaz text NULL,
	id_desserte_electricite int4 NULL,
	date_electricite date NULL,
	adaptation_electricite varchar(1) NULL DEFAULT '?'::character varying,
	type_electricite varchar(1) NULL DEFAULT '?'::character varying,
	observation_electricite text NULL,
	id_desserte_telephone int4 NULL,
	date_telephone date NULL,
	adaptation_telephone varchar(1) NULL DEFAULT '?'::character varying,
	type_telephone varchar(1) NULL DEFAULT '?'::character varying,
	observation_telephone text NULL,
	id_desserte_assainissement int4 NULL,
	date_assainissement date NULL,
	type_assainissement varchar(1) NULL DEFAULT '?'::character varying,
	adaptation_eau_pluviale varchar(1) NULL DEFAULT '?'::character varying,
	adaptation_eau_usee varchar(1) NULL DEFAULT '?'::character varying,
	epuration_assainissement varchar(1) NULL DEFAULT '?'::character varying,
	observation_assainissement text NULL,
	diametre_assainissement float8 NULL,
	securite_incendie varchar(1) NULL DEFAULT '?'::character varying,
	observation_securite_incendie text NULL,
	scolarite_collectif varchar(1) NULL DEFAULT '?'::character varying,
	ramassage_collectif varchar(1) NULL DEFAULT '?'::character varying,
	ordure_collectif varchar(1) NULL DEFAULT '?'::character varying,
	observation_collectif text NULL,
	stationnement_suffisant varchar(1) NULL DEFAULT '?'::character varying,
	maintien_arbre varchar(1) NULL DEFAULT '?'::character varying,
	plantation_nouvelle varchar(1) NULL DEFAULT '?'::character varying,
	conformite_aspect varchar(1) NULL DEFAULT '?'::character varying,
	observation_construction text NULL,
	observation_decision text NULL,
	delai_base int4 NOT NULL DEFAULT 0,
	delai_base_j int4 NOT NULL DEFAULT 0,
	delai_complementaire int4 NOT NULL DEFAULT 0,
	delai_complementaire_j int4 NOT NULL DEFAULT 0,
	delai_mineur int4 NOT NULL DEFAULT 0,
	delai_conformite int4 NOT NULL DEFAULT 3,
	delai_prolongation_exception int4 NOT NULL DEFAULT 0,
	date_envoi_contradictoire date NULL,
	date_reception_contradictoire date NULL,
	observation_contradictoire text NULL,
	id_decision_conformite int4 NULL,
	observations_conformite text NULL,
	date_conformite date NULL,
	date_mise_demeure date NULL,
	date_regularisation date NULL,
	date_limite_regularisation date NULL,
	observation_dossier text NULL,
	date_export_sitadel date NULL,
	date_export_winads date NULL,
	date_export_wilo date NULL,
	date_export_gestauran date NULL,
	date_envoi_facture_dossier date NULL,
	date_envoi_facture_recolement date NULL,
	id_mode_depot int4 NULL,
	note_complexite int4 NULL,
	description_projet text NULL,
	description_projet_instructeur text NULL,
	instruction_engagee varchar(1) NULL DEFAULT 'N'::character varying,
	cerfa_signe varchar(1) NULL,
	accept_mode_demat varchar(1) NULL,
	criteres_valides varchar(1) NULL DEFAULT 'N'::character varying,
	date_recours_abf date NULL,
	date_rep_recours_abf date NULL,
	co_decision_recours_abf varchar(2) NULL,
	date_evocation_ministre date NULL,
	date_rep_evocation_ministre date NULL,
	co_decision_evocation_ministre varchar(2) NULL,
	date_recours_cnac date NULL,
	date_rep_recours_cnac date NULL,
	co_decision_recours_cnac varchar(2) NULL,
	date_prolongation_prefet date NULL,
	date_rep_prolongation_prefet date NULL,
	co_decision_prolong_prefet varchar(2) NULL,
	observations_prolongation text NULL,
	id_controleur int4 NULL,
	date_suspension_delais date NULL,
	date_reprise_delais date NULL,
	id_archive int4 NULL,
	numero_etude varchar(254) NULL,
	id_repertoire_ged varchar(200) NULL,
	dossier_cartographie bool NULL DEFAULT false,
	observation_instruction_tech text NULL,
	numero_dossier_externe varchar(128) NULL,
	suivi_numerique int4 NULL DEFAULT 0,
	pwd_suivi_numerique varchar(36) NULL,
	search_adr varchar(400) NULL,
	id_pole_ads int4 NULL,
	date_anonymisation date NULL,
	architecte_non_obligatoire bool NOT NULL DEFAULT false,
	concessionnaire_voirie varchar(200) NULL,
	concessionnaire_eau varchar(200) NULL,
	concessionnaire_gaz varchar(200) NULL,
	concessionnaire_electricite varchar(200) NULL,
	concessionnaire_telephone varchar(200) NULL,
	concessionnaire_assainissement varchar(200) NULL,
	terrain_certificat_urbanisme bool NULL,
	terrain_lotissement bool NULL,
	terrain_zac bool NULL,
	terrain_afu bool NULL,
	terrain_pup bool NULL,
	terrain_pup_convention varchar(200) NULL,
	terrain_oin bool NULL,
	terrain_observation varchar(400) NULL,
	id_platau varchar(36) NULL,
	id_projet int4 NULL,
	gfi_libelle_reprise varchar(255) NULL,
	gfi_reprise_reference varchar(255) NULL,
	date_affichage_numerique date NULL,
	date_envoi_controle_legalite date NULL,
	date_limite_fourniture_pieces date NULL,
	id_secteur_administratif int4 NULL,
	is_archive_platau bool NOT NULL DEFAULT false,
	CONSTRAINT dossier_accept_mode_demat_check CHECK (((accept_mode_demat IS NULL) OR ((accept_mode_demat)::text = ANY (ARRAY[('O'::character varying)::text, ('N'::character varying)::text])))),
	CONSTRAINT dossier_adaptation_eau_check CHECK (((adaptation_eau IS NULL) OR ((adaptation_eau)::text = ANY (ARRAY[('B'::character varying)::text, ('I'::character varying)::text, ('M'::character varying)::text, ('?'::character varying)::text])))),
	CONSTRAINT dossier_adaptation_eau_pluviale_check CHECK (((adaptation_eau_pluviale IS NULL) OR ((adaptation_eau_pluviale)::text = ANY (ARRAY[('B'::character varying)::text, ('I'::character varying)::text, ('M'::character varying)::text, ('?'::character varying)::text])))),
	CONSTRAINT dossier_adaptation_eau_usee_check CHECK (((adaptation_eau_usee IS NULL) OR ((adaptation_eau_usee)::text = ANY (ARRAY[('B'::character varying)::text, ('I'::character varying)::text, ('M'::character varying)::text, ('?'::character varying)::text])))),
	CONSTRAINT dossier_adaptation_electricite_check CHECK (((adaptation_electricite IS NULL) OR ((adaptation_electricite)::text = ANY (ARRAY[('B'::character varying)::text, ('I'::character varying)::text, ('M'::character varying)::text, ('?'::character varying)::text])))),
	CONSTRAINT dossier_adaptation_gaz_check CHECK (((adaptation_gaz IS NULL) OR ((adaptation_gaz)::text = ANY (ARRAY[('B'::character varying)::text, ('I'::character varying)::text, ('M'::character varying)::text, ('?'::character varying)::text])))),
	CONSTRAINT dossier_adaptation_telephone_check CHECK (((adaptation_telephone IS NULL) OR ((adaptation_telephone)::text = ANY (ARRAY[('B'::character varying)::text, ('I'::character varying)::text, ('M'::character varying)::text, ('?'::character varying)::text])))),
	CONSTRAINT dossier_adaptation_voirie_check CHECK (((adaptation_voirie IS NULL) OR ((adaptation_voirie)::text = ANY (ARRAY[('B'::character varying)::text, ('I'::character varying)::text, ('M'::character varying)::text, ('?'::character varying)::text])))),
	CONSTRAINT dossier_alignement_voirie_check CHECK (((alignement_voirie IS NULL) OR ((alignement_voirie)::text = ANY (ARRAY[('O'::character varying)::text, ('N'::character varying)::text, ('?'::character varying)::text])))),
	CONSTRAINT dossier_batiment_demoli_check CHECK (((batiment_demoli IS NULL) OR ((batiment_demoli)::text = ANY (ARRAY[('O'::character varying)::text, ('N'::character varying)::text, ('?'::character varying)::text])))),
	CONSTRAINT dossier_batiment_existant_check CHECK (((batiment_existant IS NULL) OR ((batiment_existant)::text = ANY (ARRAY[('O'::character varying)::text, ('N'::character varying)::text, ('?'::character varying)::text])))),
	CONSTRAINT dossier_cerfa_signe_check CHECK (((cerfa_signe IS NULL) OR ((cerfa_signe)::text = ANY (ARRAY[('O'::character varying)::text, ('N'::character varying)::text])))),
	CONSTRAINT dossier_cession_voirie_check CHECK (((cession_voirie IS NULL) OR ((cession_voirie)::text = ANY (ARRAY[('O'::character varying)::text, ('N'::character varying)::text, ('?'::character varying)::text])))),
	CONSTRAINT dossier_conformite_aspect_check CHECK (((conformite_aspect IS NULL) OR ((conformite_aspect)::text = ANY (ARRAY[('O'::character varying)::text, ('N'::character varying)::text, ('?'::character varying)::text])))),
	CONSTRAINT dossier_depot_portail_check CHECK (((depot_portail IS NULL) OR ((depot_portail)::text = ANY (ARRAY[('O'::character varying)::text, ('N'::character varying)::text, ('?'::character varying)::text])))),
	CONSTRAINT dossier_division_batie_check CHECK (((division_batie IS NULL) OR ((division_batie)::text = ANY (ARRAY[('O'::character varying)::text, ('N'::character varying)::text, ('?'::character varying)::text])))),
	CONSTRAINT dossier_epuration_assainissement_check CHECK (((epuration_assainissement IS NULL) OR ((epuration_assainissement)::text = ANY (ARRAY[('O'::character varying)::text, ('N'::character varying)::text, ('?'::character varying)::text])))),
	CONSTRAINT dossier_grande_propriete_check CHECK (((grande_propriete IS NULL) OR ((grande_propriete)::text = ANY (ARRAY[('O'::character varying)::text, ('N'::character varying)::text, ('?'::character varying)::text])))),
	CONSTRAINT dossier_hn_dossier_check CHECK (((hn_dossier)::text = upper((hn_dossier)::text))),
	CONSTRAINT dossier_installation_nuisible_check CHECK (((installation_nuisible IS NULL) OR ((installation_nuisible)::text = ANY (ARRAY[('O'::character varying)::text, ('N'::character varying)::text, ('?'::character varying)::text])))),
	CONSTRAINT dossier_instruction_engagee_check CHECK ((((instruction_engagee)::text = 'O'::text) OR ((instruction_engagee)::text = 'N'::text))),
	CONSTRAINT dossier_maintien_arbre_check CHECK (((maintien_arbre IS NULL) OR ((maintien_arbre)::text = ANY (ARRAY[('O'::character varying)::text, ('N'::character varying)::text, ('?'::character varying)::text])))),
	CONSTRAINT dossier_mutation_check CHECK (((mutation IS NULL) OR ((mutation)::text = ANY (ARRAY[('O'::character varying)::text, ('N'::character varying)::text, ('?'::character varying)::text])))),
	CONSTRAINT dossier_note_complexite_check CHECK (((note_complexite IS NULL) OR ((note_complexite >= 1) AND (note_complexite <= 5)))),
	CONSTRAINT dossier_objet_remembrement_check CHECK (((objet_remembrement IS NULL) OR ((objet_remembrement)::text = ANY (ARRAY[('O'::character varying)::text, ('N'::character varying)::text, ('?'::character varying)::text])))),
	CONSTRAINT dossier_opposition_cnil_check CHECK (((opposition_cnil IS NULL) OR ((opposition_cnil)::text = ANY (ARRAY[('O'::character varying)::text, ('N'::character varying)::text, ('?'::character varying)::text])))),
	CONSTRAINT dossier_ordure_collectif_check CHECK (((ordure_collectif IS NULL) OR ((ordure_collectif)::text = ANY (ARRAY[('O'::character varying)::text, ('N'::character varying)::text, ('?'::character varying)::text])))),
	CONSTRAINT dossier_parcelle_lotie_check CHECK (((parcelle_lotie IS NULL) OR ((parcelle_lotie)::text = ANY (ARRAY[('O'::character varying)::text, ('N'::character varying)::text, ('?'::character varying)::text])))),
	CONSTRAINT dossier_perimetre_remembrement_check CHECK (((perimetre_remembrement IS NULL) OR ((perimetre_remembrement)::text = ANY (ARRAY[('O'::character varying)::text, ('N'::character varying)::text, ('?'::character varying)::text])))),
	CONSTRAINT dossier_pk PRIMARY KEY (id_dossier),
	CONSTRAINT dossier_plantation_nouvelle_check CHECK (((plantation_nouvelle IS NULL) OR ((plantation_nouvelle)::text = ANY (ARRAY[('O'::character varying)::text, ('N'::character varying)::text, ('?'::character varying)::text])))),
	CONSTRAINT dossier_prioritaire_check CHECK (((prioritaire IS NULL) OR ((prioritaire)::text = ANY (ARRAY[('O'::character varying)::text, ('N'::character varying)::text, ('?'::character varying)::text])))),
	CONSTRAINT dossier_ramassage_collectif_check CHECK (((ramassage_collectif IS NULL) OR ((ramassage_collectif)::text = ANY (ARRAY[('O'::character varying)::text, ('N'::character varying)::text, ('?'::character varying)::text])))),
	CONSTRAINT dossier_scolarite_collectif_check CHECK (((scolarite_collectif IS NULL) OR ((scolarite_collectif)::text = ANY (ARRAY[('O'::character varying)::text, ('N'::character varying)::text, ('?'::character varying)::text])))),
	CONSTRAINT dossier_securite_incendie_check CHECK (((securite_incendie IS NULL) OR ((securite_incendie)::text = ANY (ARRAY[('O'::character varying)::text, ('N'::character varying)::text, ('?'::character varying)::text])))),
	CONSTRAINT dossier_stationnement_suffisant_check CHECK (((stationnement_suffisant IS NULL) OR ((stationnement_suffisant)::text = ANY (ARRAY[('O'::character varying)::text, ('N'::character varying)::text, ('?'::character varying)::text])))),
	CONSTRAINT dossier_suivi_numerique_check CHECK ((suivi_numerique = ANY (ARRAY[0, 1]))),
	CONSTRAINT dossier_type_assainissement_check CHECK (((type_assainissement IS NULL) OR ((type_assainissement)::text = ANY (ARRAY[('S'::character varying)::text, ('U'::character varying)::text, ('?'::character varying)::text])))),
	CONSTRAINT dossier_type_electricite_check CHECK (((type_electricite IS NULL) OR ((type_electricite)::text = ANY (ARRAY[('A'::character varying)::text, ('E'::character varying)::text, ('?'::character varying)::text])))),
	CONSTRAINT dossier_type_telephone_check CHECK (((type_telephone IS NULL) OR ((type_telephone)::text = ANY (ARRAY[('A'::character varying)::text, ('E'::character varying)::text, ('?'::character varying)::text])))),
	CONSTRAINT dossier_unique UNIQUE (nom_dossier)
);
CREATE INDEX autorite_competente_fk1 ON public.dossier USING btree (co_autorite_competente);
CREATE INDEX commune_fk1 ON public.dossier USING btree (n_commune);
CREATE INDEX decision_abf_fk ON public.dossier USING btree (co_decision_recours_abf);
CREATE INDEX decision_cnac_fk ON public.dossier USING btree (co_decision_recours_cnac);
CREATE INDEX decision_conformite_fk1 ON public.dossier USING btree (id_decision_conformite);
CREATE INDEX decision_ministre_fk ON public.dossier USING btree (co_decision_evocation_ministre);
CREATE INDEX decision_prefet_fk ON public.dossier USING btree (co_decision_prolong_prefet);
CREATE INDEX desserte_reseau_fk1 ON public.dossier USING btree (id_desserte_assainissement);
CREATE INDEX desserte_reseau_fk2 ON public.dossier USING btree (id_desserte_eau);
CREATE INDEX desserte_reseau_fk3 ON public.dossier USING btree (id_desserte_gaz);
CREATE INDEX desserte_reseau_fk4 ON public.dossier USING btree (id_desserte_electricite);
CREATE INDEX desserte_reseau_fk5 ON public.dossier USING btree (id_desserte_telephone);
CREATE INDEX desserte_reseau_fk6 ON public.dossier USING btree (id_desserte_voirie);
CREATE INDEX dossier_fk1 ON public.dossier USING btree (id_dossier_modifie);
CREATE INDEX etape_dossier_fk1 ON public.dossier USING btree (id_etape_courante);
CREATE INDEX idx_dossier_adresse_terrain ON public.dossier USING btree (adresse_terrain);
CREATE INDEX idx_dossier_annee ON public.dossier USING btree (annee);
CREATE INDEX idx_dossier_date_depot ON public.dossier USING btree (date_depot);
CREATE INDEX idx_dossier_date_limite_instr ON public.dossier USING btree (date_limite_instruction);
CREATE INDEX idx_dossier_id_platau ON public.dossier USING btree (id_platau) INCLUDE (id_dossier);
CREATE INDEX idx_dossier_lo_adr_terrain ON public.dossier USING btree (lower((adresse_terrain)::text));
CREATE INDEX idx_dossier_up_bn ON public.dossier USING btree (upper((bn_dossier)::text));
CREATE INDEX idx_dossier_up_hn ON public.dossier USING btree (upper((hn_dossier)::text));
CREATE INDEX idx_search_adr_dossier ON public.dossier USING btree (search_adr);
CREATE INDEX intervenant_mairie_fk15 ON public.dossier USING btree (id_controleur);
CREATE INDEX lotissement_fk1 ON public.dossier USING btree (id_lotissement);
CREATE INDEX mode_depot_fk1 ON public.dossier USING btree (id_mode_depot);
CREATE INDEX objet_demande_fk1 ON public.dossier USING btree (id_objet_demande);
CREATE INDEX pole_ads_fk2 ON public.dossier USING btree (id_pole_ads);
CREATE INDEX preemption_fk1 ON public.dossier USING btree (id_preemption);
CREATE INDEX programme_amenagement_fk1 ON public.dossier USING btree (co_programme_amenagement);
CREATE INDEX projet_fk1 ON public.dossier USING btree (id_projet);
CREATE INDEX protection_fk1 ON public.dossier USING btree (id_protection);
CREATE INDEX secteur_fk2 ON public.dossier USING btree (id_secteur);
CREATE INDEX secteur_fk4 ON public.dossier USING btree (id_secteur_administratif);
CREATE INDEX situation_projet_fk1 ON public.dossier USING btree (id_situation_projet);
CREATE INDEX type_dossier_fk2 ON public.dossier USING btree (co_type_dossier);

-- Table Triggers

create trigger ai_detail_dossier after
insert
    on
    public.dossier for each row execute function ai_detail_dossier();
create trigger bi_calc_password before
insert
    on
    public.dossier for each row execute function bi_calc_password();
create trigger biu_calc_nom_dossier before
insert
    or
update
    on
    public.dossier for each row execute function biu_calc_nom_dossier();
create trigger biu_search_adr_dossier before
insert
    or
update
    on
    public.dossier for each row execute function biu_search_adr_dossier();


-- public.dossier foreign keys

ALTER TABLE public.dossier ADD CONSTRAINT autorite_competente_fk1 FOREIGN KEY (co_autorite_competente) REFERENCES autorite_competente(co_autorite_competente);
ALTER TABLE public.dossier ADD CONSTRAINT commune_fk1 FOREIGN KEY (n_commune) REFERENCES commune(n_commune);
ALTER TABLE public.dossier ADD CONSTRAINT decision_abf_fk FOREIGN KEY (co_decision_recours_abf) REFERENCES decision(co_decision);
ALTER TABLE public.dossier ADD CONSTRAINT decision_cnac_fk FOREIGN KEY (co_decision_recours_cnac) REFERENCES decision(co_decision);
ALTER TABLE public.dossier ADD CONSTRAINT decision_conformite_fk1 FOREIGN KEY (id_decision_conformite) REFERENCES decision_conformite(id_decision_conformite);
ALTER TABLE public.dossier ADD CONSTRAINT decision_ministre_fk FOREIGN KEY (co_decision_evocation_ministre) REFERENCES decision(co_decision);
ALTER TABLE public.dossier ADD CONSTRAINT decision_prefet_fk FOREIGN KEY (co_decision_prolong_prefet) REFERENCES decision(co_decision);
ALTER TABLE public.dossier ADD CONSTRAINT desserte_reseau_fk1 FOREIGN KEY (id_desserte_assainissement) REFERENCES desserte_reseau(id_desserte_reseau);
ALTER TABLE public.dossier ADD CONSTRAINT desserte_reseau_fk2 FOREIGN KEY (id_desserte_eau) REFERENCES desserte_reseau(id_desserte_reseau);
ALTER TABLE public.dossier ADD CONSTRAINT desserte_reseau_fk3 FOREIGN KEY (id_desserte_gaz) REFERENCES desserte_reseau(id_desserte_reseau);
ALTER TABLE public.dossier ADD CONSTRAINT desserte_reseau_fk4 FOREIGN KEY (id_desserte_electricite) REFERENCES desserte_reseau(id_desserte_reseau);
ALTER TABLE public.dossier ADD CONSTRAINT desserte_reseau_fk5 FOREIGN KEY (id_desserte_telephone) REFERENCES desserte_reseau(id_desserte_reseau);
ALTER TABLE public.dossier ADD CONSTRAINT desserte_reseau_fk6 FOREIGN KEY (id_desserte_voirie) REFERENCES desserte_reseau(id_desserte_reseau);
ALTER TABLE public.dossier ADD CONSTRAINT dossier_fk1 FOREIGN KEY (id_dossier_modifie) REFERENCES dossier(id_dossier);
ALTER TABLE public.dossier ADD CONSTRAINT etape_dossier_fk1 FOREIGN KEY (id_etape_courante) REFERENCES etape_dossier(id_etape_dossier);
ALTER TABLE public.dossier ADD CONSTRAINT intervenant_mairie_fk15 FOREIGN KEY (id_controleur) REFERENCES intervenant_mairie(id_intervenant_mairie);
ALTER TABLE public.dossier ADD CONSTRAINT lotissement_fk1 FOREIGN KEY (id_lotissement) REFERENCES lotissement(id_lotissement);
ALTER TABLE public.dossier ADD CONSTRAINT mode_depot_fk1 FOREIGN KEY (id_mode_depot) REFERENCES mode_depot(id_mode_depot);
ALTER TABLE public.dossier ADD CONSTRAINT objet_demande_fk1 FOREIGN KEY (id_objet_demande) REFERENCES objet_demande(id_objet_demande);
ALTER TABLE public.dossier ADD CONSTRAINT pole_ads_fk2 FOREIGN KEY (id_pole_ads) REFERENCES pole_ads(id_pole_ads);
ALTER TABLE public.dossier ADD CONSTRAINT preemption_fk1 FOREIGN KEY (id_preemption) REFERENCES preemption(id_preemption);
ALTER TABLE public.dossier ADD CONSTRAINT programme_amenagement_fk1 FOREIGN KEY (co_programme_amenagement) REFERENCES programme_amenagement(co_programme_amenagement);
ALTER TABLE public.dossier ADD CONSTRAINT projet_fk1 FOREIGN KEY (id_projet) REFERENCES projet(id_projet);
ALTER TABLE public.dossier ADD CONSTRAINT protection_fk1 FOREIGN KEY (id_protection) REFERENCES protection(id_protection);
ALTER TABLE public.dossier ADD CONSTRAINT secteur_fk2 FOREIGN KEY (id_secteur) REFERENCES secteur(id_secteur);
ALTER TABLE public.dossier ADD CONSTRAINT secteur_fk4 FOREIGN KEY (id_secteur_administratif) REFERENCES secteur(id_secteur);
ALTER TABLE public.dossier ADD CONSTRAINT situation_projet_fk1 FOREIGN KEY (id_situation_projet) REFERENCES situation_projet(id_situation_projet);
ALTER TABLE public.dossier ADD CONSTRAINT type_dossier_fk2 FOREIGN KEY (co_type_dossier) REFERENCES type_dossier(co_type_dossier);