data/pgsql/init_metier.sql

--------------------------------------------------------------------------------
-- Instructions de base de l'applicatif 
--
-- Génération de ce fichier : 
-- pg_dump -s -O -n openads -T openads.om_* openads > /tmp/init_metier.sql
-- REMPLACER : START WITH * by START WITH 1
-- SUPPRIMER : CONSTRAINT *geom*
-- REMPLACER : public.geometry by text sur les champs geom
--
-- @package openfoncier
-- @version SVN : $Id$
--------------------------------------------------------------------------------

--
-- PostgreSQL database dump
--

--
-- Name: action; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE action (
    action character varying(20) NOT NULL,
    libelle character varying(60) NOT NULL,
    regle_etat character varying(60),
    regle_delai character varying(60),
    regle_accord_tacite character varying(60),
    regle_avis character varying(60),
    regle_date_limite character varying(60),
    regle_date_notification_delai character varying(60),
    regle_date_complet character varying(60),
    regle_date_validite character varying(60),
    regle_date_decision character varying(60),
    regle_date_chantier character varying(60),
    regle_date_achevement character varying(60),
    regle_date_conformite character varying(60),
    regle_date_rejet character varying(60),
    regle_date_dernier_depot character varying(60),
    regle_date_limite_incompletude character varying(60),
    regle_delai_incompletude character varying(60)
);


--
-- Name: affectation_automatique; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE affectation_automatique (
    affectation_automatique integer NOT NULL,
    arrondissement integer,
    quartier integer,
    section character varying(2),
    instructeur integer NOT NULL,
    dossier_autorisation_type_detaille integer
);


--
-- Name: affectation_automatique_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE affectation_automatique_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: affectation_automatique_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE affectation_automatique_seq OWNED BY affectation_automatique.affectation_automatique;


--
-- Name: architecte; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE architecte (
    architecte integer NOT NULL,
    nom character varying(40) NOT NULL,
    prenom character varying(40) DEFAULT ''::character varying NOT NULL,
    adresse1 character varying(40) DEFAULT ''::character varying NOT NULL,
    adresse2 character varying(40) DEFAULT ''::character varying NOT NULL,
    cp character varying(5) DEFAULT ''::character varying NOT NULL,
    ville character varying(40) DEFAULT ''::character varying NOT NULL,
    pays character varying(40) DEFAULT ''::character varying NOT NULL,
    inscription character varying(20) DEFAULT ''::character varying NOT NULL,
    telephone character varying(14) DEFAULT ''::character varying NOT NULL,
    fax character varying(14) DEFAULT ''::character varying NOT NULL,
    email character varying(40) DEFAULT ''::character varying NOT NULL,
    note text
);


--
-- Name: architecte_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE architecte_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: architecte_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE architecte_seq OWNED BY architecte.architecte;


--
-- Name: arrondissement; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE arrondissement (
    arrondissement integer NOT NULL,
    libelle character varying(3) NOT NULL,
    code_postal character varying(5) NOT NULL
);


--
-- Name: arrondissement_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE arrondissement_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: arrondissement_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE arrondissement_seq OWNED BY arrondissement.arrondissement;


--
-- Name: autorite_competente; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE autorite_competente (
    autorite_competente integer NOT NULL,
    code character varying(20),
    libelle character varying(100),
    description text
);


--
-- Name: autorite_competente_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE autorite_competente_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: autorite_competente_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE autorite_competente_seq OWNED BY autorite_competente.autorite_competente;


--
-- Name: avis_consultation; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE avis_consultation (
    libelle character varying(30) NOT NULL,
    abrege character varying(10),
    om_validite_debut date,
    om_validite_fin date,
    avis_consultation integer NOT NULL
);


--
-- Name: avis_consultation_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE avis_consultation_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: avis_consultation_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE avis_consultation_seq OWNED BY avis_consultation.avis_consultation;


--
-- Name: avis_decision; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE avis_decision (
    libelle character varying(30) NOT NULL,
    typeavis character(1) DEFAULT ''::bpchar NOT NULL,
    sitadel character(1) DEFAULT ''::bpchar NOT NULL,
    sitadel_motif character(1) DEFAULT ''::bpchar NOT NULL,
    avis_decision integer NOT NULL
);


--
-- Name: avis_decision_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE avis_decision_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: avis_decision_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE avis_decision_seq OWNED BY avis_decision.avis_decision;


--
-- Name: bible; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE bible (
    bible integer NOT NULL,
    libelle character varying(60) NOT NULL,
    evenement integer,
    contenu text NOT NULL,
    complement integer,
    automatique character(3) DEFAULT ''::bpchar NOT NULL,
    dossier_autorisation_type integer
);


--
-- Name: bible_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE bible_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: bible_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE bible_seq OWNED BY bible.bible;


--
-- Name: blocnote; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE blocnote (
    blocnote integer NOT NULL,
    categorie character varying(20) NOT NULL,
    note text NOT NULL,
    dossier character varying(20)
);


--
-- Name: blocnote_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE blocnote_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: blocnote_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE blocnote_seq OWNED BY blocnote.blocnote;


--
-- Name: categorie_demandeur; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE categorie_demandeur (
    categorie_demandeur integer NOT NULL,
    libelle character varying(40) NOT NULL
);


--
-- Name: categorie_demandeur_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE categorie_demandeur_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: categorie_demandeur_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE categorie_demandeur_seq OWNED BY categorie_demandeur.categorie_demandeur;


--
-- Name: cerfa; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE cerfa (
    cerfa integer NOT NULL,
    libelle character varying(200),
    code character varying(20),
    om_validite_debut date,
    om_validite_fin date,
    avap_co_elt_pro boolean,
    avap_nouv_haut_surf boolean,
    avap_co_clot boolean,
    avap_aut_coup_aba_arb boolean,
    avap_ouv_infra boolean,
    avap_aut_inst_mob boolean,
    avap_aut_plant boolean,
    avap_aut_auv_elec boolean,
    tr_total boolean,
    tr_partiel boolean,
    tr_desc boolean,
    am_lotiss boolean,
    am_autre_div boolean,
    am_camping boolean,
    am_caravane boolean,
    am_carav_duree boolean,
    am_statio boolean,
    am_statio_cont boolean,
    am_affou_exhau boolean,
    am_affou_exhau_sup boolean,
    am_affou_prof boolean,
    am_exhau_haut boolean,
    am_coupe_abat boolean,
    am_prot_plu boolean,
    am_prot_muni boolean,
    am_mobil_voyage boolean,
    am_voyage_deb boolean,
    am_voyage_fin boolean,
    am_aire_voyage boolean,
    am_rememb_afu boolean,
    am_parc_resid_loi boolean,
    am_sport_moto boolean,
    am_sport_attrac boolean,
    am_sport_golf boolean,
    am_mob_art boolean,
    am_modif_voie_esp boolean,
    am_plant_voie_esp boolean,
    am_chem_ouv_esp boolean,
    am_agri_peche boolean,
    am_crea_voie boolean,
    am_modif_voie_exist boolean,
    am_crea_esp_sauv boolean,
    am_modif_amgt boolean,
    am_crea_esp_class boolean,
    am_projet_desc boolean,
    am_terr_surf boolean,
    am_tranche_desc boolean,
    am_lot_max_nb boolean,
    am_lot_max_shon boolean,
    am_lot_max_shob boolean,
    am_lot_cstr_cos boolean,
    am_lot_cstr_plan boolean,
    am_lot_cstr_vente boolean,
    am_lot_fin_diff boolean,
    am_lot_consign boolean,
    am_lot_gar_achev boolean,
    am_lot_vente_ant boolean,
    am_empl_nb boolean,
    am_tente_nb boolean,
    am_carav_nb boolean,
    am_mobil_nb boolean,
    am_pers_nb boolean,
    am_empl_hll_nb boolean,
    am_hll_shon boolean,
    am_periode_exploit boolean,
    am_exist_agrand boolean,
    am_exist_date boolean,
    am_exist_num boolean,
    am_exist_nb_avant boolean,
    am_exist_nb_apres boolean,
    am_coupe_bois boolean,
    am_coupe_parc boolean,
    am_coupe_align boolean,
    am_coupe_ess boolean,
    am_coupe_age boolean,
    am_coupe_dens boolean,
    am_coupe_qual boolean,
    am_coupe_trait boolean,
    am_coupe_autr boolean,
    co_archi_recours boolean,
    co_cstr_nouv boolean,
    co_cstr_exist boolean,
    co_modif_aspect boolean,
    co_modif_struct boolean,
    co_cloture boolean,
    co_ouvr_elec boolean,
    co_elec_tension boolean,
    co_ouvr_infra boolean,
    co_trx_imm boolean,
    co_div_terr boolean,
    co_projet_desc boolean,
    co_cstr_shob boolean,
    co_anx_pisc boolean,
    co_anx_gara boolean,
    co_anx_veran boolean,
    co_anx_abri boolean,
    co_anx_autr boolean,
    co_anx_autr_desc boolean,
    co_tot_log_nb boolean,
    co_tot_ind_nb boolean,
    co_tot_coll_nb boolean,
    co_mais_piece_nb boolean,
    co_mais_niv_nb boolean,
    co_fin_lls_nb boolean,
    co_fin_aa_nb boolean,
    co_fin_ptz_nb boolean,
    co_fin_autr_nb boolean,
    co_fin_autr_desc boolean,
    co_mais_contrat_ind boolean,
    co_uti_pers boolean,
    co_uti_vente boolean,
    co_uti_loc boolean,
    co_uti_princ boolean,
    co_uti_secon boolean,
    co_resid_agees boolean,
    co_resid_etud boolean,
    co_resid_tourism boolean,
    co_resid_hot_soc boolean,
    co_resid_soc boolean,
    co_resid_hand boolean,
    co_resid_autr boolean,
    co_resid_autr_desc boolean,
    co_foyer_chamb_nb boolean,
    co_log_1p_nb boolean,
    co_log_2p_nb boolean,
    co_log_3p_nb boolean,
    co_log_4p_nb boolean,
    co_log_5p_nb boolean,
    co_log_6p_nb boolean,
    co_bat_niv_nb boolean,
    co_trx_exten boolean,
    co_trx_surelev boolean,
    co_trx_nivsup boolean,
    co_trx_amgt boolean,
    co_demont_periode boolean,
    co_sp_transport boolean,
    co_sp_enseign boolean,
    co_sp_act_soc boolean,
    co_sp_ouvr_spe boolean,
    co_sp_sante boolean,
    co_sp_culture boolean,
    co_statio_avt_nb boolean,
    co_statio_apr_nb boolean,
    co_statio_avt_shob boolean,
    co_statio_apr_shob boolean,
    co_statio_avt_surf boolean,
    co_statio_apr_surf boolean,
    co_statio_adr boolean,
    co_statio_place_nb boolean,
    co_statio_tot_surf boolean,
    co_statio_tot_shob boolean,
    co_statio_comm_cin_surf boolean,
    tab_surface integer,
    dm_constr_dates boolean,
    dm_total boolean,
    dm_partiel boolean,
    dm_projet_desc boolean,
    dm_tot_log_nb boolean,
    tax_surf_tot boolean,
    tax_surf boolean,
    tax_surf_suppr_mod boolean,
    tax_dest_loc_tr boolean,
    tab_tax_su_princ integer,
    tab_tax_su_heber integer,
    tab_tax_su_secon integer,
    tab_tax_su_tot integer,
    tax_ext_pret boolean,
    tax_ext_desc boolean,
    tax_surf_tax_exist_cons boolean,
    tax_log_exist_nb boolean,
    tax_trx_presc_ppr boolean,
    tax_monu_hist boolean,
    tax_comm_nb boolean,
    tab_tax_su_non_habit_surf integer,
    tab_tax_am integer,
    vsd_surf_planch_smd boolean,
    vsd_unit_fonc_sup boolean,
    vsd_unit_fonc_constr_sup boolean,
    vsd_val_terr boolean,
    vsd_const_sxist_non_dem_surf boolean,
    vsd_rescr_fisc boolean,
    pld_val_terr boolean,
    pld_const_exist_dem boolean,
    pld_const_exist_dem_surf boolean,
    code_cnil boolean,
    co_archi_nom boolean,
    co_archi_prenom boolean,
    co_archi_adr_num boolean,
    co_archi_adr_voie boolean,
    co_archi_adr_lieu_dit boolean,
    co_archi_adr_localite boolean,
    co_archi_adr_cp boolean,
    co_archi_adr_bp boolean,
    co_archi_adr_cedex boolean,
    co_archi_no_incri boolean,
    co_archi_cg boolean,
    co_archi_tel1 boolean,
    co_archi_tel2 boolean,
    co_archi_mail boolean,
    terr_juri_titul boolean,
    terr_juri_lot boolean,
    terr_juri_zac boolean,
    terr_juri_afu boolean,
    terr_juri_pup boolean,
    terr_juri_oin boolean,
    terr_juri_desc boolean,
    terr_div_surf_etab boolean,
    terr_div_surf_av_div boolean,
    doc_date boolean,
    doc_tot_trav boolean,
    doc_tranche_trav boolean,
    doc_tranche_trav_desc boolean,
    doc_surf boolean,
    doc_nb_log boolean,
    doc_nb_log_indiv boolean,
    doc_nb_log_coll boolean,
    doc_nb_log_lls boolean,
    doc_nb_log_aa boolean,
    doc_nb_log_ptz boolean,
    doc_nb_log_autre boolean,
    daact_date boolean,
    daact_date_chgmt_dest boolean,
    daact_tot_trav boolean,
    daact_tranche_trav boolean,
    daact_tranche_trav_desc boolean,
    daact_surf boolean,
    daact_nb_log boolean,
    daact_nb_log_indiv boolean,
    daact_nb_log_coll boolean,
    daact_nb_log_lls boolean,
    daact_nb_log_aa boolean,
    daact_nb_log_ptz boolean,
    daact_nb_log_autre boolean,
    mod_desc boolean
);


--
-- Name: cerfa_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE cerfa_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: cerfa_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE cerfa_seq OWNED BY cerfa.cerfa;


--
-- Name: civilite; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE civilite (
    code character varying(20) NOT NULL,
    civilite integer NOT NULL,
    libelle character varying(100),
    om_validite_debut date,
    om_validite_fin date
);


--
-- Name: civilite_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE civilite_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: civilite_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE civilite_seq OWNED BY civilite.civilite;


--
-- Name: commission; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE commission (
    commission integer NOT NULL,
    code character varying(20),
    commission_type integer NOT NULL,
    libelle character varying(100),
    date_commission date NOT NULL,
    heure_commission character varying(5),
    lieu_adresse_ligne1 character varying(100),
    lieu_adresse_ligne2 character varying(100),
    lieu_salle character varying(100),
    listes_de_diffusion text,
    participants text,
    om_fichier_commission_ordre_jour character varying(64),
    om_final_commission_ordre_jour boolean,
    om_fichier_commission_compte_rendu character varying(64),
    om_final_commission_compte_rendu boolean
);


--
-- Name: commission_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE commission_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: commission_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE commission_seq OWNED BY commission.commission;


--
-- Name: commission_type; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE commission_type (
    commission_type integer NOT NULL,
    code character varying(10),
    libelle character varying(100),
    lieu_adresse_ligne1 character varying(100),
    lieu_adresse_ligne2 character varying(100),
    lieu_salle character varying(100),
    listes_de_diffusion text,
    participants text,
    corps_du_courriel text,
    om_validite_debut date,
    om_validite_fin date
);


--
-- Name: commission_type_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE commission_type_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: commission_type_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE commission_type_seq OWNED BY commission_type.commission_type;


--
-- Name: consultation; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE consultation (
    consultation integer NOT NULL,
    dossier character varying(20) NOT NULL,
    date_envoi date NOT NULL,
    date_retour date,
    date_limite date,
    service integer NOT NULL,
    avis_consultation integer,
    date_reception date,
    motivation text DEFAULT ''::text,
    fichier character varying(100),
    lu boolean,
    code_barres character varying(12),
    om_fichier_consultation character varying(64),
    om_final_consultation boolean
);


--
-- Name: consultation_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE consultation_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: consultation_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE consultation_seq OWNED BY consultation.consultation;


--
-- Name: demande; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE demande (
    demande integer NOT NULL,
    dossier_autorisation_type_detaille integer NOT NULL,
    demande_type integer NOT NULL,
    dossier_instruction character varying(20),
    dossier_autorisation character varying(20),
    date_demande date NOT NULL,
    terrain_references_cadastrales character varying(100),
    terrain_adresse_voie_numero integer,
    complement character varying(30),
    terrain_adresse_lieu_dit character varying(30),
    terrain_adresse_localite character varying(30),
    terrain_adresse_code_postal character varying(5),
    terrain_adresse_bp character varying(15),
    terrain_adresse_cedex character varying(15),
    terrain_superficie double precision,
    instruction_recepisse integer,
    arrondissement integer
);


--
-- Name: demande_nature; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE demande_nature (
    demande_nature integer NOT NULL,
    code character varying(20),
    libelle character varying(100),
    description text
);


--
-- Name: demande_nature_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE demande_nature_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: demande_nature_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE demande_nature_seq OWNED BY demande_nature.demande_nature;


--
-- Name: demande_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE demande_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: demande_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE demande_seq OWNED BY demande.demande;


--
-- Name: demande_type; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE demande_type (
    demande_type integer NOT NULL,
    code character varying(20),
    libelle character varying(100),
    description text,
    demande_nature integer,
    groupe integer,
    dossier_instruction_type integer,
    dossier_autorisation_type_detaille integer,
    contraintes character varying(20),
    qualification boolean,
    evenement integer NOT NULL
);


--
-- Name: demande_type_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE demande_type_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: demande_type_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE demande_type_seq OWNED BY demande_type.demande_type;


--
-- Name: demandeur; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE demandeur (
    demandeur integer NOT NULL,
    type_demandeur character varying(40),
    qualite character varying(40),
    particulier_nom character varying(40),
    particulier_prenom character varying(40),
    particulier_date_naissance date,
    particulier_commune_naissance character varying(30),
    particulier_departement_naissance character varying(80),
    personne_morale_denomination character varying(40),
    personne_morale_raison_sociale character varying(40),
    personne_morale_siret character varying(15),
    personne_morale_categorie_juridique character varying(15),
    personne_morale_nom character varying(40),
    personne_morale_prenom character varying(40),
    numero character varying(5),
    voie character varying(40),
    complement character varying(39),
    lieu_dit character varying(39),
    localite character varying(30),
    code_postal character varying(5),
    bp character varying(5),
    cedex character varying(5),
    pays character varying(40),
    division_territoriale character varying(40),
    telephone_fixe character varying(14),
    telephone_mobile character varying(14),
    indicatif character varying(5),
    courriel character varying(40),
    notification boolean,
    frequent boolean,
    particulier_civilite integer,
    personne_morale_civilite integer
);


--
-- Name: demandeur_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE demandeur_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: demandeur_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE demandeur_seq OWNED BY demandeur.demandeur;


--
-- Name: destination; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE destination (
    destination character varying(2) NOT NULL,
    libelle character varying(80) NOT NULL
);


--
-- Name: destination_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE destination_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: destination_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE destination_seq OWNED BY destination.destination;


--
-- Name: direction; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE direction (
    direction integer NOT NULL,
    code character varying(20) NOT NULL,
    libelle character varying(100) NOT NULL,
    description text,
    chef character varying(100) NOT NULL,
    om_validite_debut date,
    om_validite_fin date
);


--
-- Name: direction_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE direction_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: direction_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE direction_seq OWNED BY direction.direction;


--
-- Name: division; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE division (
    division integer NOT NULL,
    code character varying(20) NOT NULL,
    libelle character varying(100) NOT NULL,
    description text,
    chef character varying(100) NOT NULL,
    direction integer NOT NULL,
    om_validite_debut date,
    om_validite_fin date
);


--
-- Name: division_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE division_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: division_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE division_seq OWNED BY division.division;


--
-- Name: document_numerise; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE document_numerise (
    document_numerise integer NOT NULL,
    uid character varying(64) NOT NULL,
    dossier character varying(20) NOT NULL,
    nom_fichier character varying(255) NOT NULL,
    date_creation date NOT NULL,
    document_numerise_type integer NOT NULL
);


--
-- Name: document_numerise_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE document_numerise_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: document_numerise_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE document_numerise_seq OWNED BY document_numerise.document_numerise;


--
-- Name: document_numerise_type; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE document_numerise_type (
    document_numerise_type integer NOT NULL,
    code character varying(6) NOT NULL,
    libelle character varying(255) NOT NULL,
    document_numerise_type_categorie integer NOT NULL
);


--
-- Name: document_numerise_type_categorie; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE document_numerise_type_categorie (
    document_numerise_type_categorie integer NOT NULL,
    libelle character varying(255) NOT NULL
);


--
-- Name: document_numerise_type_categorie_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE document_numerise_type_categorie_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: document_numerise_type_categorie_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE document_numerise_type_categorie_seq OWNED BY document_numerise_type_categorie.document_numerise_type_categorie;


--
-- Name: document_numerise_type_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE document_numerise_type_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: document_numerise_type_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE document_numerise_type_seq OWNED BY document_numerise_type.document_numerise_type;


--
-- Name: donnees_techniques; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE donnees_techniques (
    donnees_techniques integer NOT NULL,
    dossier_instruction character varying(20),
    lot integer,
    avap_co_elt_pro boolean,
    avap_nouv_haut_surf boolean,
    avap_co_clot boolean,
    avap_aut_coup_aba_arb boolean,
    avap_ouv_infra boolean,
    avap_aut_inst_mob boolean,
    avap_aut_plant boolean,
    avap_aut_auv_elec boolean,
    tr_total boolean,
    tr_partiel boolean,
    tr_desc text,
    am_lotiss boolean,
    am_autre_div boolean,
    am_camping boolean,
    am_caravane boolean,
    am_carav_duree integer,
    am_statio boolean,
    am_statio_cont integer,
    am_affou_exhau boolean,
    am_affou_exhau_sup numeric,
    am_affou_prof numeric,
    am_exhau_haut numeric,
    am_coupe_abat boolean,
    am_prot_plu boolean,
    am_prot_muni boolean,
    am_mobil_voyage boolean,
    am_voyage_deb date,
    am_voyage_fin date,
    am_aire_voyage boolean,
    am_rememb_afu boolean,
    am_parc_resid_loi boolean,
    am_sport_moto boolean,
    am_sport_attrac boolean,
    am_sport_golf boolean,
    am_mob_art boolean,
    am_modif_voie_esp boolean,
    am_plant_voie_esp boolean,
    am_chem_ouv_esp boolean,
    am_agri_peche boolean,
    am_crea_voie boolean,
    am_modif_voie_exist boolean,
    am_crea_esp_sauv boolean,
    am_modif_amgt boolean,
    am_crea_esp_class boolean,
    am_projet_desc text,
    am_terr_surf numeric,
    am_tranche_desc text,
    am_lot_max_nb integer,
    am_lot_max_shon numeric,
    am_lot_max_shob numeric,
    am_lot_cstr_cos boolean,
    am_lot_cstr_plan boolean,
    am_lot_cstr_vente boolean,
    am_lot_fin_diff boolean,
    am_lot_consign boolean,
    am_lot_gar_achev boolean,
    am_lot_vente_ant boolean,
    am_empl_nb integer,
    am_tente_nb integer,
    am_carav_nb integer,
    am_mobil_nb integer,
    am_pers_nb integer,
    am_empl_hll_nb integer,
    am_hll_shon numeric,
    am_periode_exploit text,
    am_exist_agrand boolean,
    am_exist_date date,
    am_exist_num character varying(100),
    am_exist_nb_avant integer,
    am_exist_nb_apres integer,
    am_coupe_bois boolean,
    am_coupe_parc boolean,
    am_coupe_align boolean,
    am_coupe_ess character varying(100),
    am_coupe_age character varying(15),
    am_coupe_dens character varying(100),
    am_coupe_qual character varying(100),
    am_coupe_trait character varying(100),
    am_coupe_autr character varying(100),
    co_archi_recours boolean,
    co_cstr_nouv boolean,
    co_cstr_exist boolean,
    co_modif_aspect boolean,
    co_modif_struct boolean,
    co_cloture boolean,
    co_ouvr_elec boolean,
    co_elec_tension numeric,
    co_ouvr_infra boolean,
    co_trx_imm boolean,
    co_div_terr boolean,
    co_projet_desc text,
    co_cstr_shob numeric,
    co_anx_pisc boolean,
    co_anx_gara boolean,
    co_anx_veran boolean,
    co_anx_abri boolean,
    co_anx_autr boolean,
    co_anx_autr_desc text,
    co_tot_log_nb integer,
    co_tot_ind_nb integer,
    co_tot_coll_nb integer,
    co_mais_piece_nb integer,
    co_mais_niv_nb integer,
    co_fin_lls_nb integer,
    co_fin_aa_nb integer,
    co_fin_ptz_nb integer,
    co_fin_autr_nb integer,
    co_fin_autr_desc text,
    co_mais_contrat_ind boolean,
    co_uti_pers boolean,
    co_uti_vente boolean,
    co_uti_loc boolean,
    co_uti_princ boolean,
    co_uti_secon boolean,
    co_resid_agees boolean,
    co_resid_etud boolean,
    co_resid_tourism boolean,
    co_resid_hot_soc boolean,
    co_resid_soc boolean,
    co_resid_hand boolean,
    co_resid_autr boolean,
    co_resid_autr_desc text,
    co_foyer_chamb_nb integer,
    co_log_1p_nb integer,
    co_log_2p_nb integer,
    co_log_3p_nb integer,
    co_log_4p_nb integer,
    co_log_5p_nb integer,
    co_log_6p_nb integer,
    co_bat_niv_nb integer,
    co_trx_exten boolean,
    co_trx_surelev boolean,
    co_trx_nivsup boolean,
    co_trx_amgt boolean,
    co_demont_periode text,
    co_sp_transport boolean,
    co_sp_enseign boolean,
    co_sp_act_soc boolean,
    co_sp_ouvr_spe boolean,
    co_sp_sante boolean,
    co_sp_culture boolean,
    co_statio_avt_nb integer,
    co_statio_apr_nb integer,
    co_statio_avt_shob numeric,
    co_statio_apr_shob numeric,
    co_statio_avt_surf numeric,
    co_statio_apr_surf numeric,
    co_statio_adr text,
    co_statio_place_nb integer,
    co_statio_tot_surf numeric,
    co_statio_tot_shob numeric,
    co_statio_comm_cin_surf numeric,
    su_avt_shon1 numeric,
    su_avt_shon2 numeric,
    su_avt_shon3 numeric,
    su_avt_shon4 numeric,
    su_avt_shon5 numeric,
    su_avt_shon6 numeric,
    su_avt_shon7 numeric,
    su_avt_shon8 numeric,
    su_avt_shon9 numeric,
    su_cstr_shon1 numeric,
    su_cstr_shon2 numeric,
    su_cstr_shon3 numeric,
    su_cstr_shon4 numeric,
    su_cstr_shon5 numeric,
    su_cstr_shon6 numeric,
    su_cstr_shon7 numeric,
    su_cstr_shon8 numeric,
    su_cstr_shon9 numeric,
    su_trsf_shon1 numeric,
    su_trsf_shon2 numeric,
    su_trsf_shon3 numeric,
    su_trsf_shon4 numeric,
    su_trsf_shon5 numeric,
    su_trsf_shon6 numeric,
    su_trsf_shon7 numeric,
    su_trsf_shon8 numeric,
    su_trsf_shon9 numeric,
    su_chge_shon1 numeric,
    su_chge_shon2 numeric,
    su_chge_shon3 numeric,
    su_chge_shon4 numeric,
    su_chge_shon5 numeric,
    su_chge_shon6 numeric,
    su_chge_shon7 numeric,
    su_chge_shon8 numeric,
    su_chge_shon9 numeric,
    su_demo_shon1 numeric,
    su_demo_shon2 numeric,
    su_demo_shon3 numeric,
    su_demo_shon4 numeric,
    su_demo_shon5 numeric,
    su_demo_shon6 numeric,
    su_demo_shon7 numeric,
    su_demo_shon8 numeric,
    su_demo_shon9 numeric,
    su_sup_shon1 numeric,
    su_sup_shon2 numeric,
    su_sup_shon3 numeric,
    su_sup_shon4 numeric,
    su_sup_shon5 numeric,
    su_sup_shon6 numeric,
    su_sup_shon7 numeric,
    su_sup_shon8 numeric,
    su_sup_shon9 numeric,
    su_tot_shon1 numeric,
    su_tot_shon2 numeric,
    su_tot_shon3 numeric,
    su_tot_shon4 numeric,
    su_tot_shon5 numeric,
    su_tot_shon6 numeric,
    su_tot_shon7 numeric,
    su_tot_shon8 numeric,
    su_tot_shon9 numeric,
    su_avt_shon_tot numeric,
    su_cstr_shon_tot numeric,
    su_trsf_shon_tot numeric,
    su_chge_shon_tot numeric,
    su_demo_shon_tot numeric,
    su_sup_shon_tot numeric,
    su_tot_shon_tot numeric,
    dm_constr_dates text,
    dm_total boolean,
    dm_partiel boolean,
    dm_projet_desc text,
    dm_tot_log_nb integer,
    tax_surf_tot numeric,
    tax_surf numeric,
    tax_surf_suppr_mod numeric,
    tax_dest_loc_tr text,
    tax_su_princ_log_nb1 numeric,
    tax_su_princ_log_nb2 numeric,
    tax_su_princ_log_nb3 numeric,
    tax_su_princ_log_nb4 numeric,
    tax_su_princ_log_nb_tot1 numeric,
    tax_su_princ_log_nb_tot2 numeric,
    tax_su_princ_log_nb_tot3 numeric,
    tax_su_princ_log_nb_tot4 numeric,
    tax_su_princ_surf1 numeric,
    tax_su_princ_surf2 numeric,
    tax_su_princ_surf3 numeric,
    tax_su_princ_surf4 numeric,
    tax_su_princ_surf_sup1 numeric,
    tax_su_princ_surf_sup2 numeric,
    tax_su_princ_surf_sup3 numeric,
    tax_su_princ_surf_sup4 numeric,
    tax_su_heber_log_nb1 integer,
    tax_su_heber_log_nb2 integer,
    tax_su_heber_log_nb3 integer,
    tax_su_heber_log_nb_tot1 integer,
    tax_su_heber_log_nb_tot2 integer,
    tax_su_heber_log_nb_tot3 integer,
    tax_su_heber_surf1 numeric,
    tax_su_heber_surf2 numeric,
    tax_su_heber_surf3 numeric,
    tax_su_heber_surf_sup1 numeric,
    tax_su_heber_surf_sup2 numeric,
    tax_su_heber_surf_sup3 numeric,
    tax_su_secon_log_nb integer,
    tax_su_tot_log_nb integer,
    tax_su_secon_log_nb_tot integer,
    tax_su_tot_log_nb_tot integer,
    tax_su_secon_surf numeric,
    tax_su_tot_surf numeric,
    tax_su_secon_surf_sup numeric,
    tax_su_tot_surf_sup numeric,
    tax_ext_pret boolean,
    tax_ext_desc text,
    tax_surf_tax_exist_cons numeric,
    tax_log_exist_nb integer,
    tax_am_statio_ext integer,
    tax_sup_bass_pisc numeric,
    tax_empl_ten_carav_mobil_nb integer,
    tax_empl_hll_nb integer,
    tax_eol_haut_nb integer,
    tax_pann_volt_sup numeric,
    tax_am_statio_ext_sup integer,
    tax_sup_bass_pisc_sup numeric,
    tax_empl_ten_carav_mobil_nb_sup integer,
    tax_empl_hll_nb_sup integer,
    tax_eol_haut_nb_sup integer,
    tax_pann_volt_sup_sup numeric,
    tax_trx_presc_ppr boolean,
    tax_monu_hist boolean,
    tax_comm_nb integer,
    tax_su_non_habit_surf1 numeric,
    tax_su_non_habit_surf2 numeric,
    tax_su_non_habit_surf3 numeric,
    tax_su_non_habit_surf4 numeric,
    tax_su_non_habit_surf5 numeric,
    tax_su_non_habit_surf6 numeric,
    tax_su_non_habit_surf7 numeric,
    tax_su_non_habit_surf_sup1 numeric,
    tax_su_non_habit_surf_sup2 numeric,
    tax_su_non_habit_surf_sup3 numeric,
    tax_su_non_habit_surf_sup4 numeric,
    tax_su_non_habit_surf_sup5 numeric,
    tax_su_non_habit_surf_sup6 numeric,
    tax_su_non_habit_surf_sup7 numeric,
    vsd_surf_planch_smd boolean,
    vsd_unit_fonc_sup numeric,
    vsd_unit_fonc_constr_sup numeric,
    vsd_val_terr numeric,
    vsd_const_sxist_non_dem_surf numeric,
    vsd_rescr_fisc date,
    pld_val_terr numeric,
    pld_const_exist_dem boolean,
    pld_const_exist_dem_surf numeric,
    code_cnil boolean,
    co_archi_nom character varying(80),
    co_archi_prenom character varying(80),
    co_archi_adr_num character varying(10),
    co_archi_adr_voie character varying(80),
    co_archi_adr_lieu_dit character varying(80),
    co_archi_adr_localite character varying(80),
    co_archi_adr_cp character varying(5),
    co_archi_adr_bp character varying(3),
    co_archi_adr_cedex character varying(2),
    co_archi_no_incri character varying(40),
    co_archi_cg character varying(80),
    co_archi_tel1 character varying(10),
    co_archi_tel2 character varying(10),
    co_archi_mail character varying(150),
    terr_juri_titul character varying(20),
    terr_juri_lot character varying(20),
    terr_juri_zac character varying(20),
    terr_juri_afu character varying(20),
    terr_juri_pup character varying(20),
    terr_juri_oin character varying(20),
    terr_juri_desc text,
    terr_div_surf_etab numeric,
    terr_div_surf_av_div numeric,
    doc_date date,
    doc_tot_trav boolean,
    doc_tranche_trav boolean,
    doc_tranche_trav_desc text,
    doc_surf numeric,
    doc_nb_log integer,
    doc_nb_log_indiv integer,
    doc_nb_log_coll integer,
    doc_nb_log_lls integer,
    doc_nb_log_aa integer,
    doc_nb_log_ptz integer,
    doc_nb_log_autre integer,
    daact_date date,
    daact_date_chgmt_dest date,
    daact_tot_trav boolean,
    daact_tranche_trav boolean,
    daact_tranche_trav_desc text,
    daact_surf numeric,
    daact_nb_log integer,
    daact_nb_log_indiv integer,
    daact_nb_log_coll integer,
    daact_nb_log_lls integer,
    daact_nb_log_aa integer,
    daact_nb_log_ptz integer,
    daact_nb_log_autre integer,
    mod_desc text
);


--
-- Name: donnees_techniques_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE donnees_techniques_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: donnees_techniques_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE donnees_techniques_seq OWNED BY donnees_techniques.donnees_techniques;


SET default_with_oids = true;

--
-- Name: dossier; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE dossier (
    dossier character varying(20) NOT NULL,
    annee character(2) DEFAULT ''::bpchar NOT NULL,
    etat character varying(20),
    instructeur integer,
    date_demande date,
    date_depot date NOT NULL,
    date_complet date,
    date_rejet date,
    date_notification_delai date,
    delai integer DEFAULT 0 NOT NULL,
    date_limite date,
    accord_tacite character(3) DEFAULT ''::bpchar NOT NULL,
    date_decision date,
    date_validite date,
    date_chantier date,
    date_achevement date,
    date_conformite date,
    parcelle character varying(20),
    pos character varying(10),
    sig character varying(3) DEFAULT ''::character varying NOT NULL,
    batiment_nombre integer,
    logement_nombre integer,
    shon double precision,
    shon_calcul double precision,
    shob double precision,
    lot integer,
    hauteur double precision,
    piece_nombre integer,
    amenagement character varying(12) DEFAULT ''::character varying NOT NULL,
    parcelle_lot integer,
    parcelle_lot_lotissement character varying(60) DEFAULT ''::character varying NOT NULL,
    description text DEFAULT ''::text NOT NULL,
    temp1 character varying(100) DEFAULT ''::character varying NOT NULL,
    temp2 character varying(100) DEFAULT ''::character varying NOT NULL,
    temp3 character varying(100) DEFAULT ''::character varying NOT NULL,
    temp4 character varying(100) DEFAULT ''::character varying NOT NULL,
    temp5 character varying(100) DEFAULT ''::character varying NOT NULL,
    servitude text,
    erp boolean,
    avis_decision integer,
    enjeu_erp boolean,
    enjeu_urba boolean,
    division integer,
    autorite_competente integer,
    a_qualifier boolean,
    terrain_references_cadastrales character varying(100),
    terrain_adresse_voie_numero integer,
    complement character varying(30),
    terrain_adresse_lieu_dit character varying(30),
    terrain_adresse_localite character varying(30),
    terrain_adresse_code_postal character varying(5),
    terrain_adresse_bp character varying(15),
    terrain_adresse_cedex character varying(15),
    terrain_superficie double precision,
    dossier_autorisation character varying(20) NOT NULL,
    dossier_instruction_type integer NOT NULL,
    date_dernier_depot date NOT NULL,
    version integer,
    incompletude boolean DEFAULT false NOT NULL,
    evenement_suivant_tacite integer,
    evenement_suivant_tacite_incompletude integer,
    etat_pendant_incompletude character varying(20),
    date_limite_incompletude date,
    delai_incompletude integer,
    geom text,
    geom1 text
);


SET default_with_oids = false;

--
-- Name: dossier_autorisation; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE dossier_autorisation (
    dossier_autorisation character varying(20) NOT NULL,
    dossier_autorisation_type_detaille integer,
    exercice integer,
    insee integer,
    terrain_references_cadastrales character varying(100),
    terrain_adresse_voie_numero integer,
    complement character varying(30),
    terrain_adresse_lieu_dit character varying(30),
    terrain_adresse_localite character varying(30),
    terrain_adresse_code_postal character varying(5),
    terrain_adresse_bp character varying(15),
    terrain_adresse_cedex character varying(15),
    terrain_superficie double precision,
    arrondissement integer,
    depot_initial date,
    erp_numero_batiment integer,
    erp_ouvert boolean,
    erp_date_ouverture date,
    erp_arrete_decision boolean,
    erp_date_arrete_decision date,
    numero_version integer DEFAULT 0,
    etat_dossier_autorisation integer,
    date_depot date,
    date_decision date,
    date_validite date,
    date_chantier date,
    date_achevement date,
    avis_decision integer,
    etat_dernier_dossier_instruction_accepte integer
);


--
-- Name: dossier_autorisation_type; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE dossier_autorisation_type (
    dossier_autorisation_type integer NOT NULL,
    code character varying(20) NOT NULL,
    libelle character varying(100),
    description text,
    confidentiel boolean DEFAULT false,
    groupe integer
);


--
-- Name: dossier_autorisation_type_detaille; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE dossier_autorisation_type_detaille (
    dossier_autorisation_type_detaille integer NOT NULL,
    code character varying(20),
    libelle character varying(100),
    description text,
    dossier_autorisation_type integer NOT NULL
);


--
-- Name: dossier_autorisation_type_detaille_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE dossier_autorisation_type_detaille_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: dossier_autorisation_type_detaille_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE dossier_autorisation_type_detaille_seq OWNED BY dossier_autorisation_type_detaille.dossier_autorisation_type_detaille;


--
-- Name: dossier_autorisation_type_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE dossier_autorisation_type_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: dossier_autorisation_type_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE dossier_autorisation_type_seq OWNED BY dossier_autorisation_type.dossier_autorisation_type;


--
-- Name: dossier_az_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE dossier_az_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: dossier_commission; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE dossier_commission (
    dossier_commission integer NOT NULL,
    dossier character varying(20) NOT NULL,
    commission_type integer NOT NULL,
    date_souhaitee date NOT NULL,
    motivation text,
    commission integer,
    avis text,
    lu boolean DEFAULT false
);


--
-- Name: dossier_commission_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE dossier_commission_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: dossier_commission_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE dossier_commission_seq OWNED BY dossier_commission.dossier_commission;


--
-- Name: dossier_cu_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE dossier_cu_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: dossier_dat_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE dossier_dat_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: dossier_dp_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE dossier_dp_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: dossier_instruction_type; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE dossier_instruction_type (
    dossier_instruction_type integer NOT NULL,
    code character varying(20),
    libelle character varying(100),
    description text,
    dossier_autorisation_type_detaille integer NOT NULL,
    suffixe boolean DEFAULT false,
    cerfa integer,
    cerfa_lot integer,
    mouvement_sitadel character varying(20)
);


--
-- Name: dossier_instruction_type_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE dossier_instruction_type_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: dossier_instruction_type_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE dossier_instruction_type_seq OWNED BY dossier_instruction_type.dossier_instruction_type;


--
-- Name: dossier_message; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE dossier_message (
    dossier_message integer NOT NULL,
    dossier character varying(20),
    type character varying(60),
    emetteur character varying(40),
    date_emission timestamp without time zone NOT NULL,
    lu boolean DEFAULT false,
    contenu text
);


--
-- Name: dossier_message_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE dossier_message_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: dossier_message_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE dossier_message_seq OWNED BY dossier_message.dossier_message;


--
-- Name: dossier_pa_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE dossier_pa_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: dossier_pc_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE dossier_pc_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: dossier_pd_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE dossier_pd_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: etat; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE etat (
    etat character varying(20) NOT NULL,
    libelle character varying(50) NOT NULL,
    statut character varying(60)
);


--
-- Name: etat_dossier_autorisation; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE etat_dossier_autorisation (
    etat_dossier_autorisation integer NOT NULL,
    libelle character varying(100)
);


--
-- Name: etat_dossier_autorisation_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE etat_dossier_autorisation_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: etat_dossier_autorisation_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE etat_dossier_autorisation_seq OWNED BY etat_dossier_autorisation.etat_dossier_autorisation;


--
-- Name: evenement; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE evenement (
    evenement integer NOT NULL,
    libelle character varying(70) NOT NULL,
    action character varying(20),
    etat character varying(20),
    delai integer,
    accord_tacite character(3) DEFAULT ''::bpchar NOT NULL,
    delai_notification integer,
    lettretype character varying(60) DEFAULT ''::character varying NOT NULL,
    consultation character(3) DEFAULT ''::bpchar NOT NULL,
    avis_decision integer,
    restriction character varying(60),
    type character varying(100),
    evenement_retour_ar integer,
    evenement_suivant_tacite integer,
    evenement_retour_signature integer
);


--
-- Name: evenement_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE evenement_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: evenement_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE evenement_seq OWNED BY evenement.evenement;


--
-- Name: genre; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE genre (
    genre integer NOT NULL,
    code character varying(20),
    libelle character varying(100),
    description text
);


--
-- Name: genre_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE genre_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: genre_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE genre_seq OWNED BY genre.genre;


--
-- Name: groupe; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE groupe (
    groupe integer NOT NULL,
    code character varying(20),
    libelle character varying(100),
    description text,
    genre integer NOT NULL
);


--
-- Name: groupe_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE groupe_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: groupe_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE groupe_seq OWNED BY groupe.groupe;


--
-- Name: instructeur; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE instructeur (
    instructeur integer NOT NULL,
    nom character varying(100) NOT NULL,
    telephone character varying(14),
    division integer NOT NULL,
    om_utilisateur integer,
    om_validite_debut date,
    om_validite_fin date
);


--
-- Name: instructeur_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE instructeur_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: instructeur_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE instructeur_seq OWNED BY instructeur.instructeur;


--
-- Name: instruction; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE instruction (
    instruction integer NOT NULL,
    destinataire character varying(30) DEFAULT ''::character varying NOT NULL,
    date_evenement date NOT NULL,
    evenement integer NOT NULL,
    lettretype character varying(40) DEFAULT ''::character varying NOT NULL,
    complement text,
    complement2 text,
    dossier character varying(20),
    action character varying(20),
    delai integer,
    etat character varying(20),
    accord_tacite character(3) DEFAULT ''::bpchar NOT NULL,
    delai_notification integer DEFAULT 0 NOT NULL,
    archive_delai bigint DEFAULT (0)::bigint NOT NULL,
    archive_date_complet date,
    archive_date_rejet date,
    archive_date_limite date,
    archive_date_notification_delai date,
    archive_accord_tacite character(3) DEFAULT ''::bpchar NOT NULL,
    archive_etat character varying(20) DEFAULT ''::character varying NOT NULL,
    archive_date_decision date,
    archive_avis character varying(20) DEFAULT ''::character varying NOT NULL,
    archive_date_validite date,
    archive_date_achevement date,
    archive_date_chantier date,
    archive_date_conformite date,
    complement3 text,
    complement4 text,
    complement5 text,
    complement6 text,
    complement7 text,
    complement8 text,
    complement9 text,
    complement10 text,
    complement11 text,
    complement12 text,
    complement13 text,
    complement14 text,
    complement15 text,
    avis_decision integer,
    date_finalisation_courrier date,
    date_envoi_signature date,
    date_retour_signature date,
    date_envoi_rar date,
    date_retour_rar date,
    date_envoi_controle_legalite date,
    date_retour_controle_legalite date,
    signataire_arrete integer,
    numero_arrete character varying(100),
    archive_date_dernier_depot date,
    archive_incompletude boolean DEFAULT false NOT NULL,
    archive_evenement_suivant_tacite integer,
    archive_evenement_suivant_tacite_incompletude integer,
    archive_etat_pendant_incompletude character varying(20),
    archive_date_limite_incompletude date,
    archive_delai_incompletude integer,
    code_barres character varying(12),
    om_fichier_instruction character varying(64),
    om_final_instruction boolean,
    document_numerise integer
);


--
-- Name: instruction_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE instruction_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: instruction_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE instruction_seq OWNED BY instruction.instruction;


--
-- Name: lien_demande_demandeur; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE lien_demande_demandeur (
    lien_demande_demandeur integer NOT NULL,
    petitionnaire_principal boolean,
    demande integer,
    demandeur integer
);


--
-- Name: lien_demande_demandeur_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE lien_demande_demandeur_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: lien_demande_demandeur_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE lien_demande_demandeur_seq OWNED BY lien_demande_demandeur.lien_demande_demandeur;


--
-- Name: lien_demande_type_etat_dossier_autorisation; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE lien_demande_type_etat_dossier_autorisation (
    lien_demande_type_etat_dossier_autorisation integer NOT NULL,
    demande_type integer NOT NULL,
    etat_dossier_autorisation integer NOT NULL
);


--
-- Name: lien_demande_type_etat_dossier_autorisation_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE lien_demande_type_etat_dossier_autorisation_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: lien_demande_type_etat_dossier_autorisation_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE lien_demande_type_etat_dossier_autorisation_seq OWNED BY lien_demande_type_etat_dossier_autorisation.lien_demande_type_etat_dossier_autorisation;


--
-- Name: lien_dossier_autorisation_demandeur; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE lien_dossier_autorisation_demandeur (
    lien_dossier_autorisation_demandeur integer NOT NULL,
    petitionnaire_principal boolean,
    dossier_autorisation character varying(20),
    demandeur integer
);


--
-- Name: lien_dossier_autorisation_demandeur_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE lien_dossier_autorisation_demandeur_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: lien_dossier_autorisation_demandeur_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE lien_dossier_autorisation_demandeur_seq OWNED BY lien_dossier_autorisation_demandeur.lien_dossier_autorisation_demandeur;


--
-- Name: lien_dossier_demandeur; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE lien_dossier_demandeur (
    lien_dossier_demandeur integer NOT NULL,
    petitionnaire_principal boolean,
    dossier character varying(20),
    demandeur integer
);


--
-- Name: lien_dossier_demandeur_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE lien_dossier_demandeur_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: lien_dossier_demandeur_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE lien_dossier_demandeur_seq OWNED BY lien_dossier_demandeur.lien_dossier_demandeur;


--
-- Name: lien_dossier_instruction_type_evenement; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE lien_dossier_instruction_type_evenement (
    lien_dossier_instruction_type_evenement integer NOT NULL,
    dossier_instruction_type integer,
    evenement integer
);


--
-- Name: lien_dossier_instruction_type_evenement_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE lien_dossier_instruction_type_evenement_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: lien_dossier_instruction_type_evenement_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE lien_dossier_instruction_type_evenement_seq OWNED BY lien_dossier_instruction_type_evenement.lien_dossier_instruction_type_evenement;


--
-- Name: lien_dossier_lot; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE lien_dossier_lot (
    lien_dossier_lot integer NOT NULL,
    dossier character varying(20),
    lot integer
);


--
-- Name: lien_dossier_lot_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE lien_dossier_lot_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: lien_dossier_lot_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE lien_dossier_lot_seq OWNED BY lien_dossier_lot.lien_dossier_lot;


--
-- Name: lien_evenement_dossier_autorisation_type; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE lien_evenement_dossier_autorisation_type (
    lien_evenement_dossier_autorisation_type integer NOT NULL,
    evenement integer,
    dossier_autorisation_type integer
);


--
-- Name: lien_evenement_dossier_autorisation_type_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE lien_evenement_dossier_autorisation_type_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: lien_evenement_dossier_autorisation_type_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE lien_evenement_dossier_autorisation_type_seq OWNED BY lien_evenement_dossier_autorisation_type.lien_evenement_dossier_autorisation_type;


--
-- Name: lien_lot_demandeur; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE lien_lot_demandeur (
    lien_lot_demandeur integer NOT NULL,
    lot integer,
    demandeur integer,
    petitionnaire_principal boolean
);


--
-- Name: lien_lot_demandeur_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE lien_lot_demandeur_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: lien_lot_demandeur_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE lien_lot_demandeur_seq OWNED BY lien_lot_demandeur.lien_lot_demandeur;


--
-- Name: lien_service_om_utilisateur; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE lien_service_om_utilisateur (
    lien_service_om_utilisateur integer NOT NULL,
    om_utilisateur bigint,
    service integer
);


--
-- Name: lien_service_om_utilisateur_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE lien_service_om_utilisateur_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: lien_service_om_utilisateur_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE lien_service_om_utilisateur_seq OWNED BY lien_service_om_utilisateur.lien_service_om_utilisateur;


--
-- Name: lien_service_service_categorie; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE lien_service_service_categorie (
    lien_service_service_categorie integer NOT NULL,
    service_categorie integer,
    service integer
);


--
-- Name: lien_service_service_categorie_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE lien_service_service_categorie_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: lien_service_service_categorie_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE lien_service_service_categorie_seq OWNED BY lien_service_service_categorie.lien_service_service_categorie;


--
-- Name: lot; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE lot (
    lot integer NOT NULL,
    libelle character varying(80) NOT NULL,
    dossier_autorisation character varying(20)
);


--
-- Name: lot_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE lot_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: lot_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE lot_seq OWNED BY lot.lot;


--
-- Name: parametre; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE parametre (
    parametre character varying(20) NOT NULL,
    libelle character varying(50) NOT NULL,
    actif character(3),
    sitadel character(3),
    longueur_champ integer,
    type_champ character varying(20),
    valeur_autorisee character varying(50)
);


SET default_with_oids = true;

--
-- Name: parcelle; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE parcelle (
    parcelle character varying(20) NOT NULL,
    debut character varying(4) DEFAULT ''::character varying NOT NULL,
    rivoli character varying(4),
    proprietaire character varying(6),
    pos character varying(6),
    surface double precision,
    section character varying(7),
    commune character varying(5),
    geom text
);


SET default_with_oids = false;

--
-- Name: parcelle_lot; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE parcelle_lot (
    parcelle_lot integer NOT NULL,
    lotissement character varying(50) NOT NULL,
    numero character varying(16),
    surface numeric(10,3),
    geom text
);


--
-- Name: parcelle_lot_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE parcelle_lot_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: parcelle_lot_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE parcelle_lot_seq OWNED BY parcelle_lot.parcelle_lot;


--
-- Name: pos; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE pos (
    pos character varying(10) NOT NULL,
    libelle character varying(40) NOT NULL,
    geom text
);


--
-- Name: proprietaire; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE proprietaire (
    proprietaire character varying(6) NOT NULL,
    nom character varying(36) DEFAULT ''::character varying NOT NULL,
    prenom character varying(36) DEFAULT ''::character varying NOT NULL,
    adresse1 character varying(36) DEFAULT ''::character varying NOT NULL,
    adresse2 character varying(36) DEFAULT ''::character varying NOT NULL,
    cp character varying(5) DEFAULT ''::character varying NOT NULL,
    ville character varying(36) DEFAULT ''::character varying NOT NULL,
    civilite integer
);


--
-- Name: quartier; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE quartier (
    quartier integer NOT NULL,
    arrondissement integer NOT NULL,
    code_impots character varying(3) NOT NULL,
    libelle character varying(40) NOT NULL
);


--
-- Name: quartier_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE quartier_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: quartier_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE quartier_seq OWNED BY quartier.quartier;


--
-- Name: rapport_instruction; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE rapport_instruction (
    rapport_instruction integer NOT NULL,
    dossier_instruction character varying(20),
    analyse_reglementaire text,
    description_projet text,
    proposition_decision text,
    om_fichier_rapport_instruction character varying(64),
    om_final_rapport_instruction boolean
);


--
-- Name: rapport_instruction_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE rapport_instruction_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: rapport_instruction_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE rapport_instruction_seq OWNED BY rapport_instruction.rapport_instruction;


--
-- Name: regle; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE regle (
    regle integer NOT NULL,
    sens character varying(5) NOT NULL,
    ordre integer NOT NULL,
    controle character varying(20) DEFAULT ''::character varying NOT NULL,
    id integer DEFAULT 0 NOT NULL,
    champ character varying(30) NOT NULL,
    operateur character(2) NOT NULL,
    valeur double precision NOT NULL,
    message character varying(80) NOT NULL
);


--
-- Name: regle_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE regle_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: regle_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE regle_seq OWNED BY regle.regle;


--
-- Name: rivoli; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE rivoli (
    rivoli character varying(4) NOT NULL,
    libelle character varying(40) NOT NULL
);


--
-- Name: service; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE service (
    abrege character varying(5) NOT NULL,
    libelle character varying(70) NOT NULL,
    adresse character varying(40) DEFAULT ''::character varying NOT NULL,
    adresse2 character varying(39) DEFAULT ''::character varying NOT NULL,
    cp character varying(5) DEFAULT ''::character varying NOT NULL,
    ville character varying(30) DEFAULT ''::character varying NOT NULL,
    email character varying(50) DEFAULT ''::character varying NOT NULL,
    delai integer,
    service integer NOT NULL,
    consultation_papier boolean,
    notification_email boolean,
    om_validite_debut date,
    om_validite_fin date,
    type_consultation character varying(70) DEFAULT 'avec_avis_attendu'::character varying NOT NULL
);


--
-- Name: service_categorie; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE service_categorie (
    service_categorie integer NOT NULL,
    libelle character varying(70) DEFAULT ''::character varying NOT NULL
);


--
-- Name: service_categorie_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE service_categorie_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: service_categorie_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE service_categorie_seq OWNED BY service_categorie.service_categorie;


--
-- Name: service_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE service_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: service_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE service_seq OWNED BY service.service;


--
-- Name: servitude_ligne; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE servitude_ligne (
    servitude_ligne integer NOT NULL,
    libelle character varying(20) NOT NULL,
    observation character varying(80),
    perimetre integer,
    description text,
    geom text
);


--
-- Name: servitude_ligne_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE servitude_ligne_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: servitude_ligne_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE servitude_ligne_seq OWNED BY servitude_ligne.servitude_ligne;


--
-- Name: servitude_point; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE servitude_point (
    servitude_point integer NOT NULL,
    libelle character varying(20) NOT NULL,
    observation character varying(80),
    perimetre integer,
    description text,
    geom text
);


--
-- Name: servitude_point_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE servitude_point_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: servitude_point_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE servitude_point_seq OWNED BY servitude_point.servitude_point;


--
-- Name: servitude_surfacique; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE servitude_surfacique (
    servitude_surfacique integer NOT NULL,
    libelle character varying(20) NOT NULL,
    observation character varying(80),
    perimetre integer,
    description text,
    geom text
);


--
-- Name: servitude_surfacique_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE servitude_surfacique_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: servitude_surfacique_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE servitude_surfacique_seq OWNED BY servitude_surfacique.servitude_surfacique;


--
-- Name: signataire_arrete; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE signataire_arrete (
    signataire_arrete integer NOT NULL,
    civilite integer,
    nom character varying(80),
    prenom character varying(80),
    qualite character varying(80),
    signature text,
    defaut boolean,
    om_validite_debut date,
    om_validite_fin date
);


--
-- Name: signataire_arrete_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE signataire_arrete_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: signataire_arrete_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE signataire_arrete_seq OWNED BY signataire_arrete.signataire_arrete;


--
-- Name: terrain; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE terrain (
    terrain integer NOT NULL,
    dossier character varying(20),
    parcelle character varying(20),
    nom character varying(60) DEFAULT ''::character varying NOT NULL
);


--
-- Name: terrain_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE terrain_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: terrain_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE terrain_seq OWNED BY terrain.terrain;


--
-- Name: transition; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE transition (
    transition integer NOT NULL,
    etat character varying(20) NOT NULL,
    evenement integer NOT NULL
);


--
-- Name: transition_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE transition_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: transition_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE transition_seq OWNED BY transition.transition;


--
-- Name: travaux; Type: TABLE; Schema: openads; Owner: -; Tablespace: 
--

CREATE TABLE travaux (
    travaux integer NOT NULL,
    libelle character varying(80) NOT NULL,
    codelascot character(1) DEFAULT ''::bpchar NOT NULL,
    solde character(3) DEFAULT ''::bpchar NOT NULL,
    dossier_autorisation_type integer
);


--
-- Name: travaux_seq; Type: SEQUENCE; Schema: openads; Owner: -
--

CREATE SEQUENCE travaux_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: travaux_seq; Type: SEQUENCE OWNED BY; Schema: openads; Owner: -
--

ALTER SEQUENCE travaux_seq OWNED BY travaux.travaux;


--
-- Name: transition; Type: DEFAULT; Schema: openads; Owner: -
--

ALTER TABLE ONLY transition ALTER COLUMN transition SET DEFAULT nextval('transition_seq'::regclass);


--
-- Name: action_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY action
    ADD CONSTRAINT action_pkey PRIMARY KEY (action);


--
-- Name: affectation_automatique_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY affectation_automatique
    ADD CONSTRAINT affectation_automatique_pkey PRIMARY KEY (affectation_automatique);


--
-- Name: architecte_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY architecte
    ADD CONSTRAINT architecte_pkey PRIMARY KEY (architecte);


--
-- Name: arrondissement_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY arrondissement
    ADD CONSTRAINT arrondissement_pkey PRIMARY KEY (arrondissement);


--
-- Name: autorite_competente_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY autorite_competente
    ADD CONSTRAINT autorite_competente_pkey PRIMARY KEY (autorite_competente);


--
-- Name: avis_consultation_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY avis_consultation
    ADD CONSTRAINT avis_consultation_pkey PRIMARY KEY (avis_consultation);


--
-- Name: avis_decision_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY avis_decision
    ADD CONSTRAINT avis_decision_pkey PRIMARY KEY (avis_decision);


--
-- Name: bible_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY bible
    ADD CONSTRAINT bible_pkey PRIMARY KEY (bible);


--
-- Name: blocnote_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY blocnote
    ADD CONSTRAINT blocnote_pkey PRIMARY KEY (blocnote);


--
-- Name: categorie_demandeur_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY categorie_demandeur
    ADD CONSTRAINT categorie_demandeur_pkey PRIMARY KEY (categorie_demandeur);


--
-- Name: cerfa_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY cerfa
    ADD CONSTRAINT cerfa_pkey PRIMARY KEY (cerfa);


--
-- Name: civilite_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY civilite
    ADD CONSTRAINT civilite_pkey PRIMARY KEY (civilite);


--
-- Name: code_unique; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY dossier_autorisation_type
    ADD CONSTRAINT code_unique UNIQUE (code);


--
-- Name: commission_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY commission
    ADD CONSTRAINT commission_pkey PRIMARY KEY (commission);


--
-- Name: commission_type_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY commission_type
    ADD CONSTRAINT commission_type_pkey PRIMARY KEY (commission_type);


--
-- Name: consultation_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY consultation
    ADD CONSTRAINT consultation_pkey PRIMARY KEY (consultation);


--
-- Name: demande_nature_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY demande_nature
    ADD CONSTRAINT demande_nature_pkey PRIMARY KEY (demande_nature);


--
-- Name: demande_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY demande
    ADD CONSTRAINT demande_pkey PRIMARY KEY (demande);


--
-- Name: demande_type_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY demande_type
    ADD CONSTRAINT demande_type_pkey PRIMARY KEY (demande_type);


--
-- Name: demandeur_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY demandeur
    ADD CONSTRAINT demandeur_pkey PRIMARY KEY (demandeur);


--
-- Name: destination_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY destination
    ADD CONSTRAINT destination_pkey PRIMARY KEY (destination);


--
-- Name: direction_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY direction
    ADD CONSTRAINT direction_pkey PRIMARY KEY (direction);


--
-- Name: division_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY division
    ADD CONSTRAINT division_pkey PRIMARY KEY (division);


--
-- Name: document_numerise_dossier_nom_fichier_key; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY document_numerise
    ADD CONSTRAINT document_numerise_dossier_nom_fichier_key UNIQUE (dossier, nom_fichier);


--
-- Name: document_numerise_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY document_numerise
    ADD CONSTRAINT document_numerise_pkey PRIMARY KEY (document_numerise);


--
-- Name: document_numerise_type_categorie_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY document_numerise_type_categorie
    ADD CONSTRAINT document_numerise_type_categorie_pkey PRIMARY KEY (document_numerise_type_categorie);


--
-- Name: document_numerise_type_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY document_numerise_type
    ADD CONSTRAINT document_numerise_type_pkey PRIMARY KEY (document_numerise_type);


--
-- Name: document_numerise_uid_key; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY document_numerise
    ADD CONSTRAINT document_numerise_uid_key UNIQUE (uid);


--
-- Name: donnees_techniques_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY donnees_techniques
    ADD CONSTRAINT donnees_techniques_pkey PRIMARY KEY (donnees_techniques);


--
-- Name: dossier_autorisation_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY dossier_autorisation
    ADD CONSTRAINT dossier_autorisation_pkey PRIMARY KEY (dossier_autorisation);


--
-- Name: dossier_autorisation_type_detaille_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY dossier_autorisation_type_detaille
    ADD CONSTRAINT dossier_autorisation_type_detaille_pkey PRIMARY KEY (dossier_autorisation_type_detaille);


--
-- Name: dossier_autorisation_type_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY dossier_autorisation_type
    ADD CONSTRAINT dossier_autorisation_type_pkey PRIMARY KEY (dossier_autorisation_type);


--
-- Name: dossier_commission_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY dossier_commission
    ADD CONSTRAINT dossier_commission_pkey PRIMARY KEY (dossier_commission);


--
-- Name: dossier_instruction_type_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY dossier_instruction_type
    ADD CONSTRAINT dossier_instruction_type_pkey PRIMARY KEY (dossier_instruction_type);


--
-- Name: dossier_message_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY dossier_message
    ADD CONSTRAINT dossier_message_pkey PRIMARY KEY (dossier_message);


--
-- Name: dossier_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY dossier
    ADD CONSTRAINT dossier_pkey PRIMARY KEY (dossier);


--
-- Name: etat_dossier_autorisation_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY etat_dossier_autorisation
    ADD CONSTRAINT etat_dossier_autorisation_pkey PRIMARY KEY (etat_dossier_autorisation);


--
-- Name: etat_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY etat
    ADD CONSTRAINT etat_pkey PRIMARY KEY (etat);


--
-- Name: evenement_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY evenement
    ADD CONSTRAINT evenement_pkey PRIMARY KEY (evenement);


--
-- Name: genre_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY genre
    ADD CONSTRAINT genre_pkey PRIMARY KEY (genre);


--
-- Name: groupe_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY groupe
    ADD CONSTRAINT groupe_pkey PRIMARY KEY (groupe);


--
-- Name: instructeur_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY instructeur
    ADD CONSTRAINT instructeur_pkey PRIMARY KEY (instructeur);


--
-- Name: instruction_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY instruction
    ADD CONSTRAINT instruction_pkey PRIMARY KEY (instruction);


--
-- Name: lien_demande_demandeur_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY lien_demande_demandeur
    ADD CONSTRAINT lien_demande_demandeur_pkey PRIMARY KEY (lien_demande_demandeur);


--
-- Name: lien_demande_type_etat_dossier_autorisation_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY lien_demande_type_etat_dossier_autorisation
    ADD CONSTRAINT lien_demande_type_etat_dossier_autorisation_pkey PRIMARY KEY (lien_demande_type_etat_dossier_autorisation);


--
-- Name: lien_dossier_autorisation_demandeur_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY lien_dossier_autorisation_demandeur
    ADD CONSTRAINT lien_dossier_autorisation_demandeur_pkey PRIMARY KEY (lien_dossier_autorisation_demandeur);


--
-- Name: lien_dossier_demandeur_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY lien_dossier_demandeur
    ADD CONSTRAINT lien_dossier_demandeur_pkey PRIMARY KEY (lien_dossier_demandeur);


--
-- Name: lien_dossier_instruction_type_evenement_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY lien_dossier_instruction_type_evenement
    ADD CONSTRAINT lien_dossier_instruction_type_evenement_pkey PRIMARY KEY (lien_dossier_instruction_type_evenement);


--
-- Name: lien_dossier_lot_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY lien_dossier_lot
    ADD CONSTRAINT lien_dossier_lot_pkey PRIMARY KEY (lien_dossier_lot);


--
-- Name: lien_evenement_dossier_autorisation_type_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY lien_evenement_dossier_autorisation_type
    ADD CONSTRAINT lien_evenement_dossier_autorisation_type_pkey PRIMARY KEY (lien_evenement_dossier_autorisation_type);


--
-- Name: lien_lot_demandeur_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY lien_lot_demandeur
    ADD CONSTRAINT lien_lot_demandeur_pkey PRIMARY KEY (lien_lot_demandeur);


--
-- Name: lien_service_om_utilisateur_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY lien_service_om_utilisateur
    ADD CONSTRAINT lien_service_om_utilisateur_pkey PRIMARY KEY (lien_service_om_utilisateur);


--
-- Name: lien_service_service_categorie_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY lien_service_service_categorie
    ADD CONSTRAINT lien_service_service_categorie_pkey PRIMARY KEY (lien_service_service_categorie);


--
-- Name: lot_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY lot
    ADD CONSTRAINT lot_pkey PRIMARY KEY (lot);


--
-- Name: parametre_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY parametre
    ADD CONSTRAINT parametre_pkey PRIMARY KEY (parametre);


--
-- Name: parcelle_lot_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY parcelle_lot
    ADD CONSTRAINT parcelle_lot_pkey PRIMARY KEY (parcelle_lot);


--
-- Name: parcelle_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY parcelle
    ADD CONSTRAINT parcelle_pkey PRIMARY KEY (parcelle);


--
-- Name: pos_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY pos
    ADD CONSTRAINT pos_pkey PRIMARY KEY (pos);


--
-- Name: proprietaire_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY proprietaire
    ADD CONSTRAINT proprietaire_pkey PRIMARY KEY (proprietaire);


--
-- Name: quartier_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY quartier
    ADD CONSTRAINT quartier_pkey PRIMARY KEY (quartier);


--
-- Name: rapport_instruction_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY rapport_instruction
    ADD CONSTRAINT rapport_instruction_pkey PRIMARY KEY (rapport_instruction);


--
-- Name: regle_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY regle
    ADD CONSTRAINT regle_pkey PRIMARY KEY (regle);


--
-- Name: rivoli_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY rivoli
    ADD CONSTRAINT rivoli_pkey PRIMARY KEY (rivoli);


--
-- Name: service_categorie_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY service_categorie
    ADD CONSTRAINT service_categorie_pkey PRIMARY KEY (service_categorie);


--
-- Name: service_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY service
    ADD CONSTRAINT service_pkey PRIMARY KEY (service);


--
-- Name: servitude_ligne_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY servitude_ligne
    ADD CONSTRAINT servitude_ligne_pkey PRIMARY KEY (servitude_ligne);


--
-- Name: servitude_point_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY servitude_point
    ADD CONSTRAINT servitude_point_pkey PRIMARY KEY (servitude_point);


--
-- Name: servitude_surfacique_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY servitude_surfacique
    ADD CONSTRAINT servitude_surfacique_pkey PRIMARY KEY (servitude_surfacique);


--
-- Name: signataire_arrete_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY signataire_arrete
    ADD CONSTRAINT signataire_arrete_pkey PRIMARY KEY (signataire_arrete);


--
-- Name: terrain_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY terrain
    ADD CONSTRAINT terrain_pkey PRIMARY KEY (terrain);


--
-- Name: transition_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY transition
    ADD CONSTRAINT transition_pkey PRIMARY KEY (transition);


--
-- Name: travaux_pkey; Type: CONSTRAINT; Schema: openads; Owner: -; Tablespace: 
--

ALTER TABLE ONLY travaux
    ADD CONSTRAINT travaux_pkey PRIMARY KEY (travaux);


--
-- Name: lien_demande_type_etat_dossier_autorisation_uk; Type: INDEX; Schema: openads; Owner: -; Tablespace: 
--

CREATE UNIQUE INDEX lien_demande_type_etat_dossier_autorisation_uk ON lien_demande_type_etat_dossier_autorisation USING btree (demande_type, etat_dossier_autorisation);


--
-- Name: affectation_automatique_arrondissement_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY affectation_automatique
    ADD CONSTRAINT affectation_automatique_arrondissement_fkey FOREIGN KEY (arrondissement) REFERENCES arrondissement(arrondissement);


--
-- Name: affectation_automatique_dossier_autorisation_type_detaille_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY affectation_automatique
    ADD CONSTRAINT affectation_automatique_dossier_autorisation_type_detaille_fkey FOREIGN KEY (dossier_autorisation_type_detaille) REFERENCES dossier_autorisation_type_detaille(dossier_autorisation_type_detaille);


--
-- Name: affectation_automatique_instructeur_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY affectation_automatique
    ADD CONSTRAINT affectation_automatique_instructeur_fkey FOREIGN KEY (instructeur) REFERENCES instructeur(instructeur);


--
-- Name: affectation_automatique_quartier_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY affectation_automatique
    ADD CONSTRAINT affectation_automatique_quartier_fkey FOREIGN KEY (quartier) REFERENCES quartier(quartier);


--
-- Name: bible_dossier_autorisation_type_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY bible
    ADD CONSTRAINT bible_dossier_autorisation_type_fkey FOREIGN KEY (dossier_autorisation_type) REFERENCES dossier_autorisation_type(dossier_autorisation_type);


--
-- Name: bible_evenement_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY bible
    ADD CONSTRAINT bible_evenement_fkey FOREIGN KEY (evenement) REFERENCES evenement(evenement);


--
-- Name: blocnote_dossier_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY blocnote
    ADD CONSTRAINT blocnote_dossier_fkey FOREIGN KEY (dossier) REFERENCES dossier(dossier);


--
-- Name: commission_commission_type_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY commission
    ADD CONSTRAINT commission_commission_type_fkey FOREIGN KEY (commission_type) REFERENCES commission_type(commission_type);


--
-- Name: consultation_avis_consultation_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY consultation
    ADD CONSTRAINT consultation_avis_consultation_fkey FOREIGN KEY (avis_consultation) REFERENCES avis_consultation(avis_consultation);


--
-- Name: consultation_dossier_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY consultation
    ADD CONSTRAINT consultation_dossier_fkey FOREIGN KEY (dossier) REFERENCES dossier(dossier);


--
-- Name: consultation_service_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY consultation
    ADD CONSTRAINT consultation_service_fkey FOREIGN KEY (service) REFERENCES service(service);


--
-- Name: demande_arrondissement_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY demande
    ADD CONSTRAINT demande_arrondissement_fkey FOREIGN KEY (arrondissement) REFERENCES arrondissement(arrondissement);


--
-- Name: demande_demande_type_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY demande
    ADD CONSTRAINT demande_demande_type_fkey FOREIGN KEY (demande_type) REFERENCES demande_type(demande_type);


--
-- Name: demande_dossier_autorisation_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY demande
    ADD CONSTRAINT demande_dossier_autorisation_fkey FOREIGN KEY (dossier_autorisation) REFERENCES dossier_autorisation(dossier_autorisation);


--
-- Name: demande_dossier_autorisation_type_detaille_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY demande
    ADD CONSTRAINT demande_dossier_autorisation_type_detaille_fkey FOREIGN KEY (dossier_autorisation_type_detaille) REFERENCES dossier_autorisation_type_detaille(dossier_autorisation_type_detaille);


--
-- Name: demande_dossier_instruction_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY demande
    ADD CONSTRAINT demande_dossier_instruction_fkey FOREIGN KEY (dossier_instruction) REFERENCES dossier(dossier);


--
-- Name: demande_instruction_recepisse_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY demande
    ADD CONSTRAINT demande_instruction_recepisse_fkey FOREIGN KEY (instruction_recepisse) REFERENCES instruction(instruction);


--
-- Name: demande_type_demande_nature_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY demande_type
    ADD CONSTRAINT demande_type_demande_nature_fkey FOREIGN KEY (demande_nature) REFERENCES demande_nature(demande_nature);


--
-- Name: demande_type_dossier_autorisation_type_detaille_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY demande_type
    ADD CONSTRAINT demande_type_dossier_autorisation_type_detaille_fkey FOREIGN KEY (dossier_autorisation_type_detaille) REFERENCES dossier_autorisation_type_detaille(dossier_autorisation_type_detaille);


--
-- Name: demande_type_dossier_instruction_type_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY demande_type
    ADD CONSTRAINT demande_type_dossier_instruction_type_fkey FOREIGN KEY (dossier_instruction_type) REFERENCES dossier_instruction_type(dossier_instruction_type);


--
-- Name: demande_type_evenement_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY demande_type
    ADD CONSTRAINT demande_type_evenement_fkey FOREIGN KEY (evenement) REFERENCES evenement(evenement);


--
-- Name: demande_type_groupe_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY demande_type
    ADD CONSTRAINT demande_type_groupe_fkey FOREIGN KEY (groupe) REFERENCES groupe(groupe);


--
-- Name: demandeur_particulier_civilite_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY demandeur
    ADD CONSTRAINT demandeur_particulier_civilite_fkey FOREIGN KEY (particulier_civilite) REFERENCES civilite(civilite);


--
-- Name: demandeur_personne_morale_civilite_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY demandeur
    ADD CONSTRAINT demandeur_personne_morale_civilite_fkey FOREIGN KEY (personne_morale_civilite) REFERENCES civilite(civilite);


--
-- Name: division_direction_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY division
    ADD CONSTRAINT division_direction_fkey FOREIGN KEY (direction) REFERENCES direction(direction);


--
-- Name: document_numerise_document_numerise_type_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY document_numerise
    ADD CONSTRAINT document_numerise_document_numerise_type_fkey FOREIGN KEY (document_numerise_type) REFERENCES document_numerise_type(document_numerise_type) MATCH FULL;


--
-- Name: document_numerise_dossier_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY document_numerise
    ADD CONSTRAINT document_numerise_dossier_fkey FOREIGN KEY (dossier) REFERENCES dossier(dossier) MATCH FULL ON UPDATE CASCADE ON DELETE SET NULL;


--
-- Name: document_numerise_type_document_numerise_type_categorie_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY document_numerise_type
    ADD CONSTRAINT document_numerise_type_document_numerise_type_categorie_fkey FOREIGN KEY (document_numerise_type_categorie) REFERENCES document_numerise_type_categorie(document_numerise_type_categorie) MATCH FULL;


--
-- Name: donnees_techniques_dossier_instruction_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY donnees_techniques
    ADD CONSTRAINT donnees_techniques_dossier_instruction_fkey FOREIGN KEY (dossier_instruction) REFERENCES dossier(dossier);


--
-- Name: donnees_techniques_lot_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY donnees_techniques
    ADD CONSTRAINT donnees_techniques_lot_fkey FOREIGN KEY (lot) REFERENCES lot(lot);


--
-- Name: dossier_autorisation_arrondissement_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY dossier_autorisation
    ADD CONSTRAINT dossier_autorisation_arrondissement_fkey FOREIGN KEY (arrondissement) REFERENCES arrondissement(arrondissement);


--
-- Name: dossier_autorisation_avis_decision_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY dossier_autorisation
    ADD CONSTRAINT dossier_autorisation_avis_decision_fkey FOREIGN KEY (avis_decision) REFERENCES avis_decision(avis_decision);


--
-- Name: dossier_autorisation_etat_dernier_dossier_instruction_accepte_f; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY dossier_autorisation
    ADD CONSTRAINT dossier_autorisation_etat_dernier_dossier_instruction_accepte_f FOREIGN KEY (etat_dernier_dossier_instruction_accepte) REFERENCES etat_dossier_autorisation(etat_dossier_autorisation);


--
-- Name: dossier_autorisation_etat_dossier_autorisation_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY dossier_autorisation
    ADD CONSTRAINT dossier_autorisation_etat_dossier_autorisation_fkey FOREIGN KEY (etat_dossier_autorisation) REFERENCES etat_dossier_autorisation(etat_dossier_autorisation);


--
-- Name: dossier_autorisation_type_detaille_dossier_autorisation_type_fk; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY dossier_autorisation_type_detaille
    ADD CONSTRAINT dossier_autorisation_type_detaille_dossier_autorisation_type_fk FOREIGN KEY (dossier_autorisation_type) REFERENCES dossier_autorisation_type(dossier_autorisation_type);


--
-- Name: dossier_autorisation_type_detaille_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY dossier_autorisation
    ADD CONSTRAINT dossier_autorisation_type_detaille_fkey FOREIGN KEY (dossier_autorisation_type_detaille) REFERENCES dossier_autorisation_type_detaille(dossier_autorisation_type_detaille);


--
-- Name: dossier_autorisation_type_groupe_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY dossier_autorisation_type
    ADD CONSTRAINT dossier_autorisation_type_groupe_fkey FOREIGN KEY (groupe) REFERENCES groupe(groupe);


--
-- Name: dossier_autorite_competente_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY dossier
    ADD CONSTRAINT dossier_autorite_competente_fkey FOREIGN KEY (autorite_competente) REFERENCES autorite_competente(autorite_competente);


--
-- Name: dossier_avis_decision_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY dossier
    ADD CONSTRAINT dossier_avis_decision_fkey FOREIGN KEY (avis_decision) REFERENCES avis_decision(avis_decision);


--
-- Name: dossier_commission_commission_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY dossier_commission
    ADD CONSTRAINT dossier_commission_commission_fkey FOREIGN KEY (commission) REFERENCES commission(commission);


--
-- Name: dossier_commission_commission_type_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY dossier_commission
    ADD CONSTRAINT dossier_commission_commission_type_fkey FOREIGN KEY (commission_type) REFERENCES commission_type(commission_type);


--
-- Name: dossier_commission_dossier_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY dossier_commission
    ADD CONSTRAINT dossier_commission_dossier_fkey FOREIGN KEY (dossier) REFERENCES dossier(dossier);


--
-- Name: dossier_division_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY dossier
    ADD CONSTRAINT dossier_division_fkey FOREIGN KEY (division) REFERENCES division(division);


--
-- Name: dossier_dossier_autorisation_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY dossier
    ADD CONSTRAINT dossier_dossier_autorisation_fkey FOREIGN KEY (dossier_autorisation) REFERENCES dossier_autorisation(dossier_autorisation);


--
-- Name: dossier_etat_archive_etat_pendant_incompletude_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY instruction
    ADD CONSTRAINT dossier_etat_archive_etat_pendant_incompletude_fkey FOREIGN KEY (archive_etat_pendant_incompletude) REFERENCES etat(etat);


--
-- Name: dossier_etat_etat_pendant_incompletude_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY dossier
    ADD CONSTRAINT dossier_etat_etat_pendant_incompletude_fkey FOREIGN KEY (etat_pendant_incompletude) REFERENCES etat(etat);


--
-- Name: dossier_etat_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY dossier
    ADD CONSTRAINT dossier_etat_fkey FOREIGN KEY (etat) REFERENCES etat(etat);


--
-- Name: dossier_evenement_archive_evenement_suivant_tacite_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY instruction
    ADD CONSTRAINT dossier_evenement_archive_evenement_suivant_tacite_fkey FOREIGN KEY (archive_evenement_suivant_tacite) REFERENCES evenement(evenement);


--
-- Name: dossier_evenement_archive_evenement_suivant_tacite_incompletude; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY instruction
    ADD CONSTRAINT dossier_evenement_archive_evenement_suivant_tacite_incompletude FOREIGN KEY (archive_evenement_suivant_tacite_incompletude) REFERENCES evenement(evenement);


--
-- Name: dossier_evenement_evenement_suivant_tacite_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY dossier
    ADD CONSTRAINT dossier_evenement_evenement_suivant_tacite_fkey FOREIGN KEY (evenement_suivant_tacite) REFERENCES evenement(evenement);


--
-- Name: dossier_evenement_evenement_suivant_tacite_incompletude_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY dossier
    ADD CONSTRAINT dossier_evenement_evenement_suivant_tacite_incompletude_fkey FOREIGN KEY (evenement_suivant_tacite_incompletude) REFERENCES evenement(evenement);


--
-- Name: dossier_instructeur_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY dossier
    ADD CONSTRAINT dossier_instructeur_fkey FOREIGN KEY (instructeur) REFERENCES instructeur(instructeur);


--
-- Name: dossier_instruction_type_cerfa_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY dossier_instruction_type
    ADD CONSTRAINT dossier_instruction_type_cerfa_fkey FOREIGN KEY (cerfa) REFERENCES cerfa(cerfa);


--
-- Name: dossier_instruction_type_cerfa_lot_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY dossier_instruction_type
    ADD CONSTRAINT dossier_instruction_type_cerfa_lot_fkey FOREIGN KEY (cerfa_lot) REFERENCES cerfa(cerfa);


--
-- Name: dossier_instruction_type_dossier_autorisation_type_detaille_fke; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY dossier_instruction_type
    ADD CONSTRAINT dossier_instruction_type_dossier_autorisation_type_detaille_fke FOREIGN KEY (dossier_autorisation_type_detaille) REFERENCES dossier_autorisation_type_detaille(dossier_autorisation_type_detaille);


--
-- Name: dossier_instruction_type_dossier_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY dossier
    ADD CONSTRAINT dossier_instruction_type_dossier_fkey FOREIGN KEY (dossier_instruction_type) REFERENCES dossier_instruction_type(dossier_instruction_type);


--
-- Name: dossier_message_dossier_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY dossier_message
    ADD CONSTRAINT dossier_message_dossier_fkey FOREIGN KEY (dossier) REFERENCES dossier(dossier);


--
-- Name: dossier_parcelle_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY dossier
    ADD CONSTRAINT dossier_parcelle_fkey FOREIGN KEY (parcelle) REFERENCES parcelle(parcelle);


--
-- Name: dossier_parcelle_lot_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY dossier
    ADD CONSTRAINT dossier_parcelle_lot_fkey FOREIGN KEY (parcelle_lot) REFERENCES parcelle_lot(parcelle_lot);


--
-- Name: dossier_pos_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY dossier
    ADD CONSTRAINT dossier_pos_fkey FOREIGN KEY (pos) REFERENCES pos(pos);


--
-- Name: evenement_action_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY evenement
    ADD CONSTRAINT evenement_action_fkey FOREIGN KEY (action) REFERENCES action(action);


--
-- Name: evenement_avis_decision_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY evenement
    ADD CONSTRAINT evenement_avis_decision_fkey FOREIGN KEY (avis_decision) REFERENCES avis_decision(avis_decision);


--
-- Name: evenement_etat_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY evenement
    ADD CONSTRAINT evenement_etat_fkey FOREIGN KEY (etat) REFERENCES etat(etat);


--
-- Name: evenement_evenement_retour_ar_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY evenement
    ADD CONSTRAINT evenement_evenement_retour_ar_fkey FOREIGN KEY (evenement_retour_ar) REFERENCES evenement(evenement);


--
-- Name: evenement_evenement_retour_signature_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY evenement
    ADD CONSTRAINT evenement_evenement_retour_signature_fkey FOREIGN KEY (evenement_retour_signature) REFERENCES evenement(evenement);


--
-- Name: evenement_evenement_suivant_tacite_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY evenement
    ADD CONSTRAINT evenement_evenement_suivant_tacite_fkey FOREIGN KEY (evenement_suivant_tacite) REFERENCES evenement(evenement);


--
-- Name: groupe_genre_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY groupe
    ADD CONSTRAINT groupe_genre_fkey FOREIGN KEY (genre) REFERENCES genre(genre);


--
-- Name: instructeur_division_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY instructeur
    ADD CONSTRAINT instructeur_division_fkey FOREIGN KEY (division) REFERENCES division(division);


--
-- Name: instructeur_om_utilisateur_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY instructeur
    ADD CONSTRAINT instructeur_om_utilisateur_fkey FOREIGN KEY (om_utilisateur) REFERENCES om_utilisateur(om_utilisateur);


--
-- Name: instruction_action_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY instruction
    ADD CONSTRAINT instruction_action_fkey FOREIGN KEY (action) REFERENCES action(action);


--
-- Name: instruction_avis_decision_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY instruction
    ADD CONSTRAINT instruction_avis_decision_fkey FOREIGN KEY (avis_decision) REFERENCES avis_decision(avis_decision);


--
-- Name: instruction_document_numerise_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY instruction
    ADD CONSTRAINT instruction_document_numerise_fkey FOREIGN KEY (document_numerise) REFERENCES document_numerise(document_numerise);


--
-- Name: instruction_dossier_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY instruction
    ADD CONSTRAINT instruction_dossier_fkey FOREIGN KEY (dossier) REFERENCES dossier(dossier);


--
-- Name: instruction_etat_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY instruction
    ADD CONSTRAINT instruction_etat_fkey FOREIGN KEY (etat) REFERENCES etat(etat);


--
-- Name: instruction_evenement_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY instruction
    ADD CONSTRAINT instruction_evenement_fkey FOREIGN KEY (evenement) REFERENCES evenement(evenement);


--
-- Name: instruction_signataire_arrete_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY instruction
    ADD CONSTRAINT instruction_signataire_arrete_fkey FOREIGN KEY (signataire_arrete) REFERENCES signataire_arrete(signataire_arrete);


--
-- Name: lien_demande_demandeur_demande_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY lien_demande_demandeur
    ADD CONSTRAINT lien_demande_demandeur_demande_fkey FOREIGN KEY (demande) REFERENCES demande(demande);


--
-- Name: lien_demande_demandeur_demandeur_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY lien_demande_demandeur
    ADD CONSTRAINT lien_demande_demandeur_demandeur_fkey FOREIGN KEY (demandeur) REFERENCES demandeur(demandeur);


--
-- Name: lien_demande_type_etat_dossier_autorisation_demande_type_fk; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY lien_demande_type_etat_dossier_autorisation
    ADD CONSTRAINT lien_demande_type_etat_dossier_autorisation_demande_type_fk FOREIGN KEY (demande_type) REFERENCES demande_type(demande_type);


--
-- Name: lien_demande_type_etat_dossier_autorisation_etat_dossier_autori; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY lien_demande_type_etat_dossier_autorisation
    ADD CONSTRAINT lien_demande_type_etat_dossier_autorisation_etat_dossier_autori FOREIGN KEY (etat_dossier_autorisation) REFERENCES etat_dossier_autorisation(etat_dossier_autorisation);


--
-- Name: lien_dossier_autorisation_demandeur_demande_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY lien_dossier_autorisation_demandeur
    ADD CONSTRAINT lien_dossier_autorisation_demandeur_demande_fkey FOREIGN KEY (dossier_autorisation) REFERENCES dossier_autorisation(dossier_autorisation);


--
-- Name: lien_dossier_autorisation_demandeur_demandeur_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY lien_dossier_autorisation_demandeur
    ADD CONSTRAINT lien_dossier_autorisation_demandeur_demandeur_fkey FOREIGN KEY (demandeur) REFERENCES demandeur(demandeur);


--
-- Name: lien_dossier_demandeur_demandeur_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY lien_dossier_demandeur
    ADD CONSTRAINT lien_dossier_demandeur_demandeur_fkey FOREIGN KEY (demandeur) REFERENCES demandeur(demandeur);


--
-- Name: lien_dossier_demandeur_dossier_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY lien_dossier_demandeur
    ADD CONSTRAINT lien_dossier_demandeur_dossier_fkey FOREIGN KEY (dossier) REFERENCES dossier(dossier);


--
-- Name: lien_dossier_instruction_type_evenement_dossier_instruction_typ; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY lien_dossier_instruction_type_evenement
    ADD CONSTRAINT lien_dossier_instruction_type_evenement_dossier_instruction_typ FOREIGN KEY (dossier_instruction_type) REFERENCES dossier_instruction_type(dossier_instruction_type);


--
-- Name: lien_dossier_instruction_type_evenement_evenement_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY lien_dossier_instruction_type_evenement
    ADD CONSTRAINT lien_dossier_instruction_type_evenement_evenement_fkey FOREIGN KEY (evenement) REFERENCES evenement(evenement);


--
-- Name: lien_dossier_lot_dossier_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY lien_dossier_lot
    ADD CONSTRAINT lien_dossier_lot_dossier_fkey FOREIGN KEY (dossier) REFERENCES dossier(dossier);


--
-- Name: lien_dossier_lot_lot_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY lien_dossier_lot
    ADD CONSTRAINT lien_dossier_lot_lot_fkey FOREIGN KEY (lot) REFERENCES lot(lot);


--
-- Name: lien_evenement_dossier_autorisation_type_dossier_autorisation_t; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY lien_evenement_dossier_autorisation_type
    ADD CONSTRAINT lien_evenement_dossier_autorisation_type_dossier_autorisation_t FOREIGN KEY (dossier_autorisation_type) REFERENCES dossier_autorisation_type(dossier_autorisation_type);


--
-- Name: lien_evenement_dossier_autorisation_type_evenement_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY lien_evenement_dossier_autorisation_type
    ADD CONSTRAINT lien_evenement_dossier_autorisation_type_evenement_fkey FOREIGN KEY (evenement) REFERENCES evenement(evenement);


--
-- Name: lien_lot_demandeur_demandeur_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY lien_lot_demandeur
    ADD CONSTRAINT lien_lot_demandeur_demandeur_fkey FOREIGN KEY (demandeur) REFERENCES demandeur(demandeur);


--
-- Name: lien_lot_demandeur_lot_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY lien_lot_demandeur
    ADD CONSTRAINT lien_lot_demandeur_lot_fkey FOREIGN KEY (lot) REFERENCES lot(lot);


--
-- Name: lien_service_om_utilisateur_om_utilisateur_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY lien_service_om_utilisateur
    ADD CONSTRAINT lien_service_om_utilisateur_om_utilisateur_fkey FOREIGN KEY (om_utilisateur) REFERENCES om_utilisateur(om_utilisateur);


--
-- Name: lien_service_om_utilisateur_service_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY lien_service_om_utilisateur
    ADD CONSTRAINT lien_service_om_utilisateur_service_fkey FOREIGN KEY (service) REFERENCES service(service);


--
-- Name: lien_service_service_categorie_service_categorie_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY lien_service_service_categorie
    ADD CONSTRAINT lien_service_service_categorie_service_categorie_fkey FOREIGN KEY (service_categorie) REFERENCES service_categorie(service_categorie);


--
-- Name: lien_service_service_categorie_service_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY lien_service_service_categorie
    ADD CONSTRAINT lien_service_service_categorie_service_fkey FOREIGN KEY (service) REFERENCES service(service);


--
-- Name: lot_dossier_autorisation_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY lot
    ADD CONSTRAINT lot_dossier_autorisation_fkey FOREIGN KEY (dossier_autorisation) REFERENCES dossier_autorisation(dossier_autorisation);


--
-- Name: parcelle_pos_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY parcelle
    ADD CONSTRAINT parcelle_pos_fkey FOREIGN KEY (pos) REFERENCES pos(pos);


--
-- Name: parcelle_proprietaire_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY parcelle
    ADD CONSTRAINT parcelle_proprietaire_fkey FOREIGN KEY (proprietaire) REFERENCES proprietaire(proprietaire);


--
-- Name: parcelle_rivoli_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY parcelle
    ADD CONSTRAINT parcelle_rivoli_fkey FOREIGN KEY (rivoli) REFERENCES rivoli(rivoli);


--
-- Name: proprietaire_civilite_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY proprietaire
    ADD CONSTRAINT proprietaire_civilite_fkey FOREIGN KEY (civilite) REFERENCES civilite(civilite);


--
-- Name: quartier_arrondissement_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY quartier
    ADD CONSTRAINT quartier_arrondissement_fkey FOREIGN KEY (arrondissement) REFERENCES arrondissement(arrondissement);


--
-- Name: rapport_instruction_dossier_instruction_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY rapport_instruction
    ADD CONSTRAINT rapport_instruction_dossier_instruction_fkey FOREIGN KEY (dossier_instruction) REFERENCES dossier(dossier);


--
-- Name: signataire_arrete_civilite_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY signataire_arrete
    ADD CONSTRAINT signataire_arrete_civilite_fkey FOREIGN KEY (civilite) REFERENCES civilite(civilite);


--
-- Name: terrain_dossier_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY terrain
    ADD CONSTRAINT terrain_dossier_fkey FOREIGN KEY (dossier) REFERENCES dossier(dossier);


--
-- Name: terrain_parcelle_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY terrain
    ADD CONSTRAINT terrain_parcelle_fkey FOREIGN KEY (parcelle) REFERENCES parcelle(parcelle);


--
-- Name: transition_etat_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY transition
    ADD CONSTRAINT transition_etat_fkey FOREIGN KEY (etat) REFERENCES etat(etat);


--
-- Name: transition_evenement_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY transition
    ADD CONSTRAINT transition_evenement_fkey FOREIGN KEY (evenement) REFERENCES evenement(evenement);


--
-- Name: travaux_dossier_autorisation_type_fkey; Type: FK CONSTRAINT; Schema: openads; Owner: -
--

ALTER TABLE ONLY travaux
    ADD CONSTRAINT travaux_dossier_autorisation_type_fkey FOREIGN KEY (dossier_autorisation_type) REFERENCES dossier_autorisation_type(dossier_autorisation_type);


--
-- PostgreSQL database dump complete
--
