6 Replies Latest reply: Nov 30, 2011 12:09 PM by Vin Steele RSS

    3.1EA2 index edit display bug

    Vin Steele
      When editing a table by right-clicking on the table in the Connections window [table->Edit->Indexes], indexes that end in -UK or -FK (because they support unique-key or foreign-key constraints) do not appear in the list of editable indexes. The indexes exist, and are visible in the Connections window (under Indexes) or when Opening the table, but not in the list of editable indexes within the Edit Table popup window.
        • 1. Re: 3.1EA2 index edit display bug
          Gary Graham-Oracle
          Hi Vin,

          I believe it's functioning as-designed. If an index has no associated constraint, then it will appear in the editable index list. If it does, then each case (Primary|Unique|Foreign) requires its own custom UI, and any changes should be made by right-clicking on the given table node, selecting Edit, then clicking on ...

          Primary Key
          or
          Unique Constraints
          or
          Foreign Keys

          in the table characteristics list below the Search box on the left side of the Table Edit panel. In addition, you may add an index with the same name as an existing constraint, but not the other way around. Finally, if you add a constraint with the same columns as an existing index (but using a different name), the create will fail if the same column order is used. When a different column order is used, the create succeeds and the new constraint is automatically associated with the existing index having the same columns but in a different order.

          Regards,
          Gary
          SQL Developer Team
          • 2. Re: 3.1EA2 index edit display bug
            Vin Steele
            Hi Gary,
            Thank you for your explanation. It makes good sense for Primary Keys and Unique Keys, which automatically generate indices to support the Constraints. However, for Foreign Keys, there is no automatic generation of the supporting indices. If such an enhancement is in the works, I think that would be great. I know that there is such a request for the Data Modeler. Are there any plans for such an enhancement? I know that there is a report [ Data Dictionary Reports -> Tables -> Quality Assurance -> Tables with Unindexed Foreign Keys]. In the absence of such an enhancement, I still think the Indices that support Foreign-key Constraints should show up in Indexes pane.

            In any case, I request that the content of your answer to my question be added to the Help for the index pane, which currently states:

            Indexes pane

            Specifies properties for each index on the table.

            Indexes: Lists the indexes currently defined on the table. To add an index, click the Add Index (+) icon; to delete an index, select it and click the Remove Index (X) icon.
            • 3. Re: 3.1EA2 index edit display bug
              Gary Graham-Oracle
              Hi Vin,

              I'm no architect, but the main concepts I keep in mind when thinking about such things are:

              1) Constraints are metadata.
              2) Constraints must be enforced by the database. How it does so is an implementation detail.
              3) Enforcement of primary/unique key constraints means no duplicates, and a unique index handles that very efficiently.
              4) Otherwise indices exist to speed up data retrieval.

              Now, to your points
              for Foreign Keys, there is no automatic generation of the supporting indices
              True, but look at the full requirement for a foreign key constraint. First off, an index for speeding up enforcement of the rule "don't add a child unless its parent exists" already exists: it is (one of) the "parent" table's primary/unique key indices, also known as the referenced constraint name's index.

              Next, an additional index (typically thought of as a foreign key index) can come in handy when deleting a parent with children in a dependent table. It helps the database deal efficiently with all "On Delete" rule cases:
              1) block the parent's deletion in order to prevent orphaning its children (RESTRICT)
              2) delete the children along with the parent (CASCADE)
              3) orphan the children by setting each child's (parent) foreign key column to null (SET NULL)
              Are there any plans for such an enhancement?
              But what if an application only uses the foreign key constraint to enforce the "add" rule, not the "delete" rule. Maybe the data is partitioned and the old stuff just gets dropped. Then an automatically generated foreign key index just adds unnecessary overhead. So such an enhancement might not be welcomed by all.
              I still think the Indices that support Foreign-key Constraints should show up in Indexes pane
              Any index manually created to support the "On Delete" cases will appear there, unless it has been "overloaded" to support a primary/unique key constraint, probably a rare situation.

              I logged the following internal bug in response to your comment about the misleading Help information:
              Bug 13443904 - FORUM: CLARIFICATION: CONSTRAINT INDEX NEVER LISTED IN TABLE/EDIT/INDEXES

              -Gary
              • 4. Re: 3.1EA2 index edit display bug
                Vin Steele
                Thank you for your quick and detailed response.

                My experience differs from what you predict. See the DDL below. I created a couple of test tables, created primary keys on both, created a foreign-key constraint from one to the other, and an index of the same name to support that foreign-key. In SQL Developer, the index does not show up in the 'Edit' index pane, but does show up in the list of indexes when I 'Open' the table.


                --------------------------------------------------------
                -- File created - Tuesday-November-29-2011
                --------------------------------------------------------
                DROP TABLE "VIN_TEST1" cascade constraints;
                DROP TABLE "VIN_TEST2" cascade constraints;
                --------------------------------------------------------
                -- DDL for Table VIN_TEST1
                --------------------------------------------------------

                CREATE TABLE "VIN_TEST1"
                (     "VIN_TEST1_SK" NUMBER,
                     "COLUMN1" VARCHAR2(20 BYTE)
                ) ;
                --------------------------------------------------------
                -- DDL for Table VIN_TEST2
                --------------------------------------------------------

                CREATE TABLE "VIN_TEST2"
                (     "VIN_TEST2_SK" NUMBER,
                     "VIN_TEST1_SK" NUMBER,
                     "FILLER2" VARCHAR2(20 BYTE)
                ) ;


                --------------------------------------------------------
                -- Constraints for Table VIN_TEST1
                --------------------------------------------------------

                ALTER TABLE "VIN_TEST1" MODIFY ("VIN_TEST1_SK" NOT NULL ENABLE);

                ALTER TABLE "VIN_TEST1" ADD CONSTRAINT "VIN_TEST1_PK" PRIMARY KEY ("VIN_TEST1_SK") ENABLE;
                --------------------------------------------------------
                -- Constraints for Table VIN_TEST2
                --------------------------------------------------------

                ALTER TABLE "VIN_TEST2" MODIFY ("VIN_TEST2_SK" NOT NULL ENABLE);

                ALTER TABLE "VIN_TEST2" ADD CONSTRAINT "VIN_TEST2_PK" PRIMARY KEY ("VIN_TEST2_SK") ENABLE;

                --------------------------------------------------------
                -- Ref Constraints for Table VIN_TEST2
                --------------------------------------------------------

                ALTER TABLE "VIN_TEST2" ADD CONSTRAINT "VIN_TEST2_VIN_TEST1_FK" FOREIGN KEY ("VIN_TEST1_SK")
                     REFERENCES "VIN_TEST1" ("VIN_TEST1_SK") ENABLE;
                --------------------------------------------------------
                -- DDL for Index VIN_TEST2_VIN_TEST1_FK
                --------------------------------------------------------

                CREATE INDEX "VIN_TEST2_VIN_TEST1_FK" ON "STND_RPT"."VIN_TEST2" ("VIN_TEST1_SK")
                ;
                • 5. Re: 3.1EA2 index edit display bug
                  Gary Graham-Oracle
                  Vin,

                  I stand corrected.

                  I replicated the same scenario based on my existing case plus adding the final index via the Table|Edit|Indexes|Add dialog. With the foreign key constraint name as the index name, an index is created but then, upon closing and re-opening the Edit dialog, it never appears in the Indexes list. It does appear everywhere else one would expected, however, at least after clicking all relevant Refresh icons.

                  So I dropped that index and repeated the steps above using an index name different from the constraint name. Then all worked as I noted before. So I must qualify my prior statement
                  Any index manually created to support the "On Delete" cases will appear there, unless it has been "overloaded" to support a primary/unique key constraint, probably a rare situation.
                  with the additional condition that the index name must differ from the foreign key constraint name. Probably it must be unique across all index and "key" constraint names.

                  Thanks,
                  Gary
                  • 6. Re: 3.1EA2 index edit display bug
                    Vin Steele
                    Thank you for the clarification, and for the detailed explanation of the thought process behind the code.

                    Vin Steele