sSQRV0KU

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