source: TI07-MOLES/trunk/RDBMS/Postgres/basicbuildscripts/inst_level_0/inst_level_1/inst_level_2/inst_level_3/tbl_md_desc_to_sections.sql @ 1513

Subversion URL: http://proj.badc.rl.ac.uk/svn/ndg/TI07-MOLES/trunk/RDBMS/Postgres/basicbuildscripts/inst_level_0/inst_level_1/inst_level_2/inst_level_3/tbl_md_desc_to_sections.sql@1513
Revision 1513, 2.4 KB checked in by aharwood, 13 years ago (diff)

Changed constraint name. Previously two constraints were defined with the same name:
fk_tbl_md_desc_to_sections_md_desc_sections

  • Property svn:eol-style set to native
  • Property svn:keywords set to Author Date Id Revision
Line 
1--------------------------------------------------------------------------------------------------------------------------------------------
2-- Linking metadata descriptions to their sections
3-- More fields in here?
4-------------------------------------------------------------------------------------------------------------------------------------------
5CREATE 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) 
15WITHOUT OIDS;
16COMMENT ON COLUMN ndgb.tbl_md_desc_to_sections.fld_precedence IS 'Allows ordering of sections'; 
17
18CREATE 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
22ALTER 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);
25CREATE 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);
28COMMENT ON INDEX ndgb.ix_tbl_md_desc_section_precedence IS 'Ensures that no two sections within a description have the same precedence'; 
29
30ALTER 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;
35CREATE 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
39ALTER 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;
44CREATE 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
48ALTER TABLE ndgb.tbl_md_desc_to_sections OWNER TO ndgadmin;
49
Note: See TracBrowser for help on using the repository browser.