1 | -------------------------------------------------------------------------------------------------------------------------------------------- |
---|
2 | -- Linking metadata descriptions to their sections |
---|
3 | -- More fields in here? |
---|
4 | ------------------------------------------------------------------------------------------------------------------------------------------- |
---|
5 | CREATE TABLE ndgb.tbl_md_desc_to_sections |
---|
6 | ( |
---|
7 | fld_precedence int4 NOT NULL, |
---|
8 | fld_md_desc_local_id varchar(255) NOT NULL, |
---|
9 | fld_md_desc_id varchar(255) NOT NULL, |
---|
10 | fld_desc_section_local_id varchar(255) NOT NULL, |
---|
11 | fld_desc_section_id varchar(255) NOT NULL, |
---|
12 | CONSTRAINT pk_md_desc_to_sections |
---|
13 | PRIMARY KEY (fld_md_desc_id, fld_md_desc_local_id, fld_desc_section_local_id, fld_desc_section_id) |
---|
14 | ) |
---|
15 | WITHOUT OIDS; |
---|
16 | COMMENT ON COLUMN ndgb.tbl_md_desc_to_sections.fld_precedence IS 'Allows ordering of sections'; |
---|
17 | |
---|
18 | CREATE UNIQUE INDEX pkix_tbl_md_desc_to_sections |
---|
19 | ON ndgb.tbl_md_desc_to_sections |
---|
20 | USING btree (fld_md_desc_id, fld_md_desc_local_id, fld_desc_section_local_id, fld_desc_section_id); |
---|
21 | |
---|
22 | ALTER TABLE ndgb.tbl_md_desc_to_sections |
---|
23 | ADD CONSTRAINT tbl_md_desc_to_sections_md_desc_section_precedence |
---|
24 | UNIQUE(fld_md_desc_id, fld_md_desc_local_id, fld_precedence); |
---|
25 | CREATE UNIQUE INDEX ix_tbl_md_desc_section_precedence |
---|
26 | ON ndgb.tbl_md_desc_to_sections |
---|
27 | USING btree (fld_md_desc_id, fld_md_desc_local_id, fld_precedence); |
---|
28 | COMMENT ON INDEX ndgb.ix_tbl_md_desc_section_precedence IS 'Ensures that no two sections within a description have the same precedence'; |
---|
29 | |
---|
30 | ALTER TABLE ndgb.tbl_md_desc_to_sections |
---|
31 | ADD CONSTRAINT fk_tbl_md_desc_to_sections_metadata_descriptions |
---|
32 | FOREIGN KEY (fld_md_desc_id, fld_md_desc_local_id) |
---|
33 | REFERENCES ndgb.tbl_metadata_descriptions (fld_data_provider_id, fld_data_provider_local_id) |
---|
34 | ON UPDATE RESTRICT ON DELETE RESTRICT; |
---|
35 | CREATE INDEX fki_tbl_md_desc_to_sections_sections |
---|
36 | ON ndgb.tbl_md_desc_to_sections |
---|
37 | USING btree (fld_md_desc_id, fld_md_desc_local_id); |
---|
38 | |
---|
39 | ALTER TABLE ndgb.tbl_md_desc_to_sections |
---|
40 | ADD CONSTRAINT fk_tbl_md_desc_to_sections_md_desc_sections |
---|
41 | FOREIGN KEY (fld_desc_section_id, fld_desc_section_local_id) |
---|
42 | REFERENCES ndgb.metadata_description_sections (fld_desc_section_id, fld_desc_section_local_id) |
---|
43 | ON UPDATE RESTRICT ON DELETE RESTRICT; |
---|
44 | CREATE INDEX fki_tbl_md_desc_to_sections_desc |
---|
45 | ON ndgb.tbl_md_desc_to_sections |
---|
46 | USING btree (fld_desc_section_id, fld_desc_section_local_id); |
---|
47 | |
---|
48 | ALTER TABLE ndgb.tbl_md_desc_to_sections OWNER TO ndgadmin; |
---|
49 | |
---|