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

--------------------------------------------------------------------------------------------------------------------------------------------
-- Linking metadata descriptions to their sections
-- More fields in here?
-------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE ndgb.tbl_md_desc_to_sections
(
  fld_precedence int4 NOT NULL,
  fld_md_desc_local_id varchar(255) NOT NULL,
  fld_md_desc_id varchar(255) NOT NULL,
  fld_desc_section_local_id varchar(255) NOT NULL,
  fld_desc_section_id varchar(255) NOT NULL,
  CONSTRAINT pk_md_desc_to_sections 
    PRIMARY KEY (fld_md_desc_id, fld_md_desc_local_id, fld_desc_section_local_id, fld_desc_section_id)
) 
WITHOUT OIDS;
COMMENT ON COLUMN ndgb.tbl_md_desc_to_sections.fld_precedence IS 'Allows ordering of sections'; 

CREATE UNIQUE INDEX pkix_tbl_md_desc_to_sections 
  ON ndgb.tbl_md_desc_to_sections 
  USING btree (fld_md_desc_id, fld_md_desc_local_id, fld_desc_section_local_id, fld_desc_section_id);

ALTER TABLE ndgb.tbl_md_desc_to_sections 
  ADD CONSTRAINT tbl_md_desc_to_sections_md_desc_section_precedence 
  UNIQUE(fld_md_desc_id, fld_md_desc_local_id, fld_precedence);
CREATE UNIQUE INDEX ix_tbl_md_desc_section_precedence 
  ON ndgb.tbl_md_desc_to_sections 
  USING btree (fld_md_desc_id, fld_md_desc_local_id, fld_precedence);
COMMENT ON INDEX ndgb.ix_tbl_md_desc_section_precedence IS 'Ensures that no two sections within a description have the same precedence'; 

ALTER TABLE ndgb.tbl_md_desc_to_sections 
  ADD CONSTRAINT fk_tbl_md_desc_to_sections_metadata_descriptions 
  FOREIGN KEY (fld_md_desc_id, fld_md_desc_local_id) 
  REFERENCES ndgb.tbl_metadata_descriptions (fld_data_provider_id, fld_data_provider_local_id)
   ON UPDATE RESTRICT ON DELETE RESTRICT;
CREATE INDEX fki_tbl_md_desc_to_sections_sections
  ON ndgb.tbl_md_desc_to_sections 
  USING btree (fld_md_desc_id, fld_md_desc_local_id);

ALTER TABLE ndgb.tbl_md_desc_to_sections 
  ADD CONSTRAINT fk_tbl_md_desc_to_sections_md_desc_sections 
  FOREIGN KEY (fld_desc_section_id, fld_desc_section_local_id) 
  REFERENCES ndgb.metadata_description_sections (fld_desc_section_id, fld_desc_section_local_id)
   ON UPDATE RESTRICT ON DELETE RESTRICT;
CREATE INDEX fki_tbl_md_desc_to_sections_desc
  ON ndgb.tbl_md_desc_to_sections 
  USING btree (fld_desc_section_id, fld_desc_section_local_id);

ALTER TABLE ndgb.tbl_md_desc_to_sections OWNER TO ndgadmin;