5 Replies Latest reply: Feb 8, 2012 5:03 AM by David Last-Oracle RSS

    Naming of PK, Unique constraints and supporting indexes

    mtefft
      I want to have my primary key constraint and its supporting index share the same name, i.e:
      CREATE UNIQUE INDEX MYTABLE_PK ON MYTABLE(...);
      ALTER TABLE MYTABLE ADD CONSTRAINT MYTABLE_PK PRIMARY KEY(...);
      Without explicitly defining a unique index via the Relational model, the generated DDL appended 'X' to the index name:
      CREATE UNIQUE INDEX MYTABLE_PKX ON MYTABLE(...);
      ALTER TABLE MYTABLE ADD CONSTRAINT MYTABLE_PK PRIMARY KEY(...);
      I tried adding an explicit unique index to the table, but it changes the name (adds 'v1') when I exit the table editor:
      CREATE UNIQUE INDEX MYTABLE_PKv1 ON MYTABLE(...);
      ALTER TABLE MYTABLE ADD CONSTRAINT MYTABLE_PK PRIMARY KEY(...);
      I have the same issue with unique constraints & indexes.

      Questions:
      Can I generate DDL with matching names? from the 'v1' trick it seems unlikely, it doesn't want me to even save the definition the way I want.
      Where are the naming transformations for this located? I found Tools, Preferences, Data Modeler/Naming Standards/Templates but this nugget does nto seem to be there.

      Thanks.
        • 1. Re: Naming of PK, Unique constraints and supporting indexes
          jbellver
          Hi,

          in the Physical model you can specify for this PK the clause USING INDEX in the properties of the PK, you should select BY INDEX NAME value,
          then the tab of Using Index will be enabled to specify the tablespace,sorting ,logging ,etc and If you want to specify a existing index,

          then the DDL generated wiill look like this ;

          ALTER TABLE MYTABLE ADD CONSTRAINT MYTABLE_PK PRIMARY KEY(...) USING INDEX TABLESPACE TB_INDX01;

          With this statement the Oracle will create an index with the same name than the PK,
          take into account that if you have another unique index qith the same columns Oracle will use the old one saving the necessity to create a new one,

          Maybe I'm not so clear, if you have more questions or I couldn't explain me well , please let me know
          • 2. Re: Naming of PK, Unique constraints and supporting indexes
            mtefft
            Thanks for your suggestions, it helped me find some new areas to try out.

            For selecting an existing index, I can only choose one that I had defined earlier (no surprise) but I was not allowed to save an index with the correct name.

            I think I have accomplished it by:
            - Definiing unique indexes with the names I want (first)
            - Defining pk and unique constraints, and let the tool change the names (suffixing with v1, for example)
            - In the physical model, open the PK constraint
            - Rename the PK constraint there (remove the v1 suffix)
            - Choose By Index Name for Using Index, then use the Using Index tab to choose the corresponding index
            - Repeat for Unique constraints

            Now the DDL generates the way I want. In the relational model, the PK and unique constraint names still have the annoying v1 suffixes.

            Thanks,
            Mike
            • 3. Re: Naming of PK, Unique constraints and supporting indexes
              jbellver
              Hi,

              If you want to have a PK and the index of the PK with the same name,
              with this sintaxys will be acomplish this:

              ALTER TABLE table_name ADD(CONSTRAINT pk_name PRIMARY KEY ( ) USING INDEX);

              if you create the PK in the relational model, not create any index,

              in the physical model you select BY INDEX NAME in the USING INDEX option of the primary key,

              the resulted DDL will acomplish your necessities,

              Oracle will create an unique index with the name of the PK and assign to the primary key.

              best regards
              • 4. Re: Naming of PK, Unique constraints and supporting indexes
                Philip Stoyanov-Oracle
                Hi Mike,
                Rename the PK constraint there (remove the v1 suffix)
                I'm glad you have your solution. Indeed objects in physical model can have name that differ from one in relational. We have bug logged for this PK/index name problem and at some point it'll be possible to have them with same name in relational model.

                Philip
                • 5. Re: Naming of PK, Unique constraints and supporting indexes
                  David Last-Oracle
                  Hi Mike,

                  The Production Release of Data Modeler 3.1 has been updated to allow primary and unique constraints and their Indexes to share the same name.

                  David