[jira] Commented: (DERBY-539) Update the Create Index statement in the Derby documentation with additional information

["Daniel John Debrunner (JIRA)" <de...@db.apache.org>]

    [ http://issues.apache.org/jira/browse/DERBY-539?page=comments#action_12320047 ] 

Daniel John Debrunner commented on DERBY-539:
---------------------------------------------

I see what you are trying to do, but I think it would be better achieved by adding a sentence/paragraph, not by modifying the existing documentation.

Something like:
---
Adding a PRIMARY KEY or UNIQUE constraint  when an existing UNIQUE index exists on the same set of columns will result in two physical indexes on the table for the same set of columns. One is the original UNIQUE index and one is the backing index for the new constaint.
---

I thought I had entered a jira entry similar to this, but I can't find it. Maybe I just posted to derby-dev. My issue was similar in that the backing indexes sometimes overlap existing indexes and thus Derby ends up with more physical indexes on a table than are strictly required. Optimising index creation by splitting the logical index from the physical index would be a big help, so that multiple logical indexes could share the same physical index, including subsetting. E.g. index on a,b and use the physical index for a,b,c.



> Update the Create Index statement in the Derby documentation with additional information
> ----------------------------------------------------------------------------------------
>
>          Key: DERBY-539
>          URL: http://issues.apache.org/jira/browse/DERBY-539
>      Project: Derby
>         Type: Improvement
>   Components: Documentation
>     Reporter: Susan Cline
>     Priority: Minor

>
>  In the 'Create Index' statement documentation of the 10.1 Reference Guide (derby/docs/10.1/ref/rrefsqlj20937.html)
> this statement is made about creating indexes and constraints:
> Indexes and constraints
> Unique, primary key, and foreign key constraints generate indexes that enforce or "back" the constraint (and are thus sometimes called backing indexes). If a column or set of columns has a UNIQUE or PRIMARY KEY constraint on it, you can not create an index on those columns. Derby has already created it for you with a system-generated name.
> This is true, but I think it can be expanded upon to be clearer.  A suggestion for this is below:
> Indexes and constraints
> Unique, primary key, and foreign key constraints generate indexes that enforce or "back" the constraint (and are thus sometimes called backing indexes).
> If a column or set of columns has a PRIMARY KEY constraint on it, you can not 
> create an index on those columns.  If a column or set of columns has a UNIQUE constraint on it, you can not create an index on those columns, but you can create
> a PRIMARY KEY constraint on it.  Addtionally, if this is the case, a backing index
> will be created for the PRIMARY KEY constraint so two indexes will now exist on the column or set of columns that had the UNIQUE constraint on it.
> This issue came up when I noticed that I could create a unique index on a column, then create a PK on that column.  When I used a tool to generate DDL for the table I noticed one constraint and two indexes on the column which didn't make sense at first when reading the existing documentation.  With the additional information above I think it explains the real behaviour better. 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira