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
| drop database if exists "essai";
create database "essai"
template = "template0"
encoding = 'utf8';
\connect "essai"
set role "postgres";
create table "_cat_titre" (
"id_titre" smallint not null check ("id_titre" > 0),
"sigle" character varying(10) not null check ("sigle" not similar to ' *'),
"libelle" character varying(20) not null check ("libelle" not similar to ' *'),
primary key("id_titre")
) with (oids=false);
insert into "_cat_titre" values (1, 'M.', 'Monsieur');
insert into "_cat_titre" values (2, 'Mme', 'Madame');
insert into "_cat_titre" values (3, 'Mlle', 'Mademoiselle');
create table "personne" (
"id_personne" integer not null check ("id_personne" > 0),
"nom" character varying(40) not null check ("nom" not similar to ' *'),
"prenom" character varying(30) not null check ("prenom" not similar to ' *'),
"tel1" character(10) default null check ("tel1" not similar to ' *'),
"tel2" character(10) default null check ("tel2" not similar to ' *'),
"adresse" character varying(100) default null check ("adresse" not similar to ' *'),
"cp" character(5) default null check ("cp" not similar to ' *'),
"ville" character varying(30) default null check ("ville" not similar to ' *'),
"comment" text default null check ("comment" not similar to ' *'),
"date_creation" date not null check ("date_creation" <= ('today'::text)::date) default ('today'::text)::date,
"id_titre" smallint not null,
constraint "fk_titre" foreign key ("id_titre")
references "_cat_titre" ("id_titre") match full
on update cascade on delete restrict,
primary key("id_personne")
) with (oids=false);
insert into "personne" values (1, 'Moi nom', 'Moi prénom', '0123456789', '9876543210', '1 rue du chat perché', '01234', 'Moi ville', 'Super type', ('today'::text)::date, 1);
insert into "personne" values (2, 'Toi nom', 'Toi prénom', '0123456789', '9876543210', '1 rue du chat perché', '01234', 'Toi ville', 'Super type', ('today'::text)::date, 1);
insert into "personne" values (3, 'Lui nom', 'Lui prénom', '0123456789', '9876543210', '1 rue du chat perché', '01234', 'Lui ville', 'Super type', ('today'::text)::date, 1); |
