1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91
|
--
-- Table Personnes
--
CREATE TABLE Personnes (
id_personne integer NOT NULL,
nom character varying(50),
prenom character varying(50),
date_naissance date,
CONSTRAINT "Personnes_pkey" PRIMARY KEY (id_personne),
CONSTRAINT "nom_prenom_unique" UNIQUE (nom, prenom)
);
-- Séquence pour id_personne
CREATE SEQUENCE Personnes_id_personne_seq
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
ALTER SEQUENCE Personnes_id_personne_seq OWNED BY Personnes.id_personne;
ALTER TABLE Personnes ALTER COLUMN id_personne SET DEFAULT nextval('Personnes_id_personne_seq'::regclass);
--
-- Table Adherents
--
CREATE TABLE Adherents (
num_licence bigint,
num_secu_sociale bigint,
statut character varying,
CONSTRAINT "num_licence_unique" UNIQUE (num_licence),
CONSTRAINT "num_secu_sociale_unique" UNIQUE (num_secu_sociale)
)INHERITS (Personnes);
--
-- Table Touristes
--
CREATE TABLE Touristes (
adresse text,
num_tel character varying(20),
ville character varying(50),
code_postal character varying(10),
CONSTRAINT "num_tel_unique" UNIQUE (num_tel)
)INHERITS (Personnes);
--
-- Table Familles
--
CREATE TABLE Familles (
id_famille integer NOT NULL,
id_chef_famille integer NOT NULL,
code_postal integer NOT NULL,
ville character varying(25) NOT NULL,
pays character varying(25) NOT NULL,
num_mobile character varying(13) NOT NULL,
num_fixe character varying(13) NOT NULL,
email character varying(35) NOT NULL,
bateau1 integer,
bateau2 integer,
CONSTRAINT "Familles_pkey" PRIMARY KEY (id_famille),
CONSTRAINT "Famille_to_Adherents_id_personne" FOREIGN KEY (id_chef_famille)
REFERENCES Adherents(id_personne) ON UPDATE SET NULL ON DELETE SET NULL,
CONSTRAINT "Famille_to_bateaux_adherents_id_bateau_adherent" FOREIGN KEY (bateau1)
REFERENCES Bateaux(id_bateau) ON UPDATE SET NULL ON DELETE SET NULL,
CONSTRAINT "Famille_to_bateaux_adherents_id_bateau_adherent2" FOREIGN KEY (bateau2)
REFERENCES Bateaux(id_bateau) ON UPDATE SET NULL ON DELETE SET NULL
);
-- Séquence pour id_famille
CREATE SEQUENCE Familles_id_famille_seq
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
ALTER SEQUENCE Familles_id_famille_seq OWNED BY Familles.id_famille;
ALTER TABLE Familles ALTER COLUMN id_famille SET DEFAULT nextval('Familles_id_famille_seq'::regclass); |
Partager