P34FizW3

1CREATE TABLE IF NOT EXISTS resource(
2    id serial PRIMARY KEY,
3    kind resource_kind NOT NULL,
4    author_id int REFERENCES user(id) ON DELETE SET NULL,
5
6    ---uri text NOT NULL,
7    creation_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
8    modification_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
9
10    message_id int references message,
11    track_id int references track,
12    location_id int references track,
13    fileupload_id int references upload
14
15    check (
16        (
17            (message_id is not null)::integer +
18            (track_id is not null)::integer +
19            (location_id is not null)::integer +
20            (fileupload_id is not null)::integer
21
22        ) = 1
23    )
24);
25
26create unique index on resource (message_id) where message_id is not null;
27create unique index on resource (track_id) where track_id is not null;
28create unique index on resource (location_id) where location_id is not null;
29create unique index on resource (fileupload_id) where fileupload_id is not null;
30	
31Colonna      |            Tipo             | Ordinamento |         |                          Default                     
32-------------------+-----------------------------+-------------+-----------------+------------------------------------------------
33 id                | integer                     |             | not null        | nextval('resource_id_seq'::regclass)
34 kind              | resource_kind     |             | not null        | 
35 author_id         | integer                     |             |                 | 
36 creation_time     | timestamp without time zone |             | not null        | CURRENT_TIMESTAMP
37 modification_time | timestamp without time zone |             | not null        | CURRENT_TIMESTAMP
38 message_id        | integer                     |             |                 | 
39 track_id          | integer                     |             |                 | 
40 location_id       | integer                     |             |                 | 
41 fileupload_id     | integer                     |             |                 | 
42Indici:
43    "resource_pkey" PRIMARY KEY, btree (id)
44    "resource_fileupload_id_idx" UNIQUE, btree (fileupload_id) WHERE fileupload_id IS NOT NULL
45    "resource_fileupload_id_idx1" UNIQUE, btree (fileupload_id) WHERE fileupload_id IS NOT NULL
46    "resource_fileupload_id_idx10" UNIQUE, btree (fileupload_id) WHERE fileupload_id IS NOT NULL
47    "resource_fileupload_id_idx100" UNIQUE, btree (fileupload_id) WHERE fileupload_id IS NOT NULL
48    "resource_fileupload_id_idx101" UNIQUE, btree (fileupload_id) WHERE fileupload_id IS NOT NULL
49    "resource_fileupload_id_idx102" UNIQUE, btree (fileupload_id) WHERE fileupload_id IS NOT NULL
50    "resource_fileupload_id_idx103" UNIQUE, btree (fileupload_id) WHERE fileupload_id IS NOT NULL
51    "resource_fileupload_id_idx104" UNIQUE, btree (fileupload_id) WHERE fileupload_id IS NOT NULL
52    "resource_fileupload_id_idx105" UNIQUE, btree (fileupload_id) WHERE fileupload_id IS NOT NULL
53    "resource_fileupload_id_idx106" UNIQUE, btree (fileupload_id) WHERE fileupload_id IS NOT NULL
54    "resource_fileupload_id_idx107" UNIQUE, btree (fileupload_id) WHERE fileupload_id IS NOT NULL
55...