Rem drv: <create type="indexes" pos="+"/>
Rem
Rem $Header: test_metadata_indexes.sql 29-jun-2005.01:48:58 gsbhatia Exp $
Rem
Rem test_metadata_indexes.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      test_metadata_indexes.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    gsbhatia    06/26/05 - New repmgr header impl 
Rem    gsbhatia    02/13/05 - updating repmgr header 
Rem    gsbhatia    02/09/05 - updating repmgr header 
Rem    gsbhatia    02/07/05 - updating repmgr header 
Rem    ktlaw       01/11/05 - add repmgr header 
Rem    afontana    11/16/04 - add qualifiers 
Rem    afontana    11/11/04 - change mgmt_target_test_map to 
Rem                           mgmt_test_target_map 
Rem    afontana    09/10/04 - afontana_test_meta_repos
Rem    afontana    09/01/04 - Created
Rem

Rem
Rem  MGMT_TEST
Rem
ALTER TABLE mgmt_test
  ADD CONSTRAINT mgmt_test_pk
    PRIMARY KEY (test_type);


Rem
Rem  MGMT_TEST_TARGET_MAP
Rem
ALTER TABLE mgmt_test_target_map
  ADD CONSTRAINT mgmt_tgt_test_pk
    PRIMARY KEY (target_type, target_meta_ver,
                 cat_prop1, cat_prop2, cat_prop3,
                 cat_prop4, cat_prop5, test_type);

ALTER TABLE mgmt_test_target_map
  ADD CONSTRAINT mgmt_tgt_test_fk
    FOREIGN KEY (test_type)
    REFERENCES mgmt_test(test_type)
  ON DELETE CASCADE;



Rem
Rem  MGMT_TEST_METRICS
Rem
ALTER TABLE mgmt_test_metrics
  ADD CONSTRAINT mgmt_test_metrics_pk
    PRIMARY KEY (test_type, metric_name);

ALTER TABLE mgmt_test_metrics
  ADD CONSTRAINT mgmt_test_metrics_fk
    FOREIGN KEY (test_type)
    REFERENCES mgmt_test(test_type)
  ON DELETE CASCADE;

ALTER TABLE mgmt_test_metrics
  ADD CONSTRAINT mgmt_test_metrics_pk2
    UNIQUE (test_type, metric_number);


Rem
Rem MGMT_TEST_MCOLUMNS
Rem
ALTER TABLE mgmt_test_mcolumns
  ADD CONSTRAINT mgmt_test_mcolumns_pk
    PRIMARY KEY(test_type, metric_name, metric_column);

ALTER TABLE mgmt_test_mcolumns
  ADD CONSTRAINT mgmt_test_mcolumns_fk
    FOREIGN KEY (test_type, metric_name)
    REFERENCES mgmt_test_metrics(test_type, metric_name)
  ON DELETE CASCADE;

ALTER TABLE mgmt_test_mcolumns
  ADD CONSTRAINT mgmt_test_mcolumns_pk2
    UNIQUE (test_type, metric_name, display_order);


Rem
Rem   MGMT_TEST_PROP_UIGROUP
Rem
ALTER TABLE mgmt_test_prop_uigroup
  ADD CONSTRAINT mgmt_test_prop_uigroup_pk
    PRIMARY KEY(test_type, group_name);

ALTER TABLE mgmt_test_prop_uigroup
  ADD CONSTRAINT mgmt_test_prop_uigroup_fk
    FOREIGN KEY (test_type)
    REFERENCES mgmt_test(test_type)
  ON DELETE CASCADE;

ALTER TABLE mgmt_test_prop_uigroup
  ADD CONSTRAINT mgmt_test_prop_uigroup_pk2
    UNIQUE (test_type, display_order);

Rem
Rem   MGMT_TEST_PROP
Rem
ALTER TABLE mgmt_test_prop
  ADD CONSTRAINT mgmt_test_prop_pk
    PRIMARY KEY(test_type, prop_name);

ALTER TABLE mgmt_test_prop
  ADD CONSTRAINT mgmt_test_prop_fk
    FOREIGN KEY (test_type)
    REFERENCES mgmt_test(test_type)
  ON DELETE CASCADE;

ALTER TABLE mgmt_test_prop
  ADD CONSTRAINT mgmt_test_prop_pk2
    UNIQUE (test_type, group_name, display_order);


Rem
Rem   MGMT_TEST_PROP_LEVEL
Rem
ALTER TABLE mgmt_test_prop_level
  ADD CONSTRAINT mgmt_test_prop_level_pk
    PRIMARY KEY(test_type, property, level_name);

ALTER TABLE mgmt_test_prop_level
  ADD CONSTRAINT mgmt_test_prop_level_fk
    FOREIGN KEY (test_type, property)
    REFERENCES mgmt_test_prop(test_type, prop_name)
  ON DELETE CASCADE;



Rem
Rem   MGMT_TEST_PROP_CHOICES
Rem
ALTER TABLE mgmt_test_prop_choices
  ADD CONSTRAINT mgmt_test_prop_choices_pk
    PRIMARY KEY(test_type, prop_name, choice_name);

ALTER TABLE mgmt_test_prop_choices
  ADD CONSTRAINT mgmt_test_prop_choices_fk
    FOREIGN KEY (test_type, prop_name)
    REFERENCES mgmt_test_prop(test_type, prop_name)
  ON DELETE CASCADE;

ALTER TABLE mgmt_test_prop_choices
  ADD CONSTRAINT mgmt_test_prop_choices_pk2
    UNIQUE (test_type, prop_name, display_order);



Rem
Rem MGMT_TEST_DEFAULT_PROMOTION
Rem
ALTER TABLE mgmt_test_default_promotion
  ADD CONSTRAINT mgmt_test_default_promotion_pk
    UNIQUE (test_type, dest_metric_name, dest_metric_column,
            dest_metric_key1_value, dest_metric_key2_value,
            dest_metric_key3_value, dest_metric_key4_value,
            dest_metric_key5_value);

ALTER TABLE mgmt_test_default_promotion
  ADD CONSTRAINT mgmt_test_default_promotion_fk
    FOREIGN KEY (test_type, src_metric_name, src_metric_column)
    REFERENCES mgmt_test_mcolumns(test_type, metric_name, metric_column)
  ON DELETE CASCADE;

Rem
Rem MGMT_TEST_DEFAULT_THRESHOLDS
Rem
ALTER TABLE mgmt_test_default_thresholds
  ADD CONSTRAINT mgmt_test_def_thresholds_pk
    UNIQUE (test_type, metric_name, metric_column,
            metric_key1_value, metric_key2_value,
            metric_key3_value, metric_key4_value,
            metric_key5_value);

ALTER TABLE mgmt_test_default_thresholds
  ADD CONSTRAINT mgmt_test_def_thresholds_fk
    FOREIGN KEY (test_type, metric_name, metric_column)
    REFERENCES mgmt_test_mcolumns(test_type, metric_name, metric_column)
  ON DELETE CASCADE;


Rem
Rem MGMT_TEST_MQUERY_DESC
Rem
ALTER TABLE mgmt_test_metric_props
  ADD CONSTRAINT mgmt_test_mquery_desc_pk
    PRIMARY KEY (property_guid, type_meta_ver);

--ALTER TABLE mgmt_test_metric_props
--  ADD CONSTRAINT mgmt_test_mquery_desc_fk
--    FOREIGN KEY (target_type, type_meta_ver, metric_name)
--    REFERENCES mgmt_metrics(target_type, type_meta_ver, metric_name)
--  ON DELETE CASCADE;

CREATE INDEX mgmt_test_metric_props_idx2
  ON mgmt_test_metric_props(target_type, metric_name);


Rem
Rem MGMT_TEST_QUALIFIERS
Rem 
ALTER TABLE mgmt_test_qualifiers
  ADD CONSTRAINT mgmt_test_qualifiers_pk
    PRIMARY KEY (test_type, qualifier);

ALTER TABLE mgmt_test_qualifiers
  ADD CONSTRAINT mgmt_test_qualifiers_fk
    FOREIGN KEY (test_type)
    REFERENCES mgmt_test(test_type)
  ON DELETE CASCADE;


Rem
Rem MGMT_TEST_PROP_QUALIFIERS
Rem
ALTER TABLE mgmt_test_prop_qualifiers
  ADD CONSTRAINT mgmt_test_prop_qualifiers_pk
    PRIMARY KEY (test_type, property, qualifier);

ALTER TABLE mgmt_test_prop_qualifiers
  ADD CONSTRAINT mgmt_test_prop_qualifiers_fk
    FOREIGN KEY (test_type, property)
    REFERENCES mgmt_test_prop(test_type, prop_name)
  ON DELETE CASCADE;