db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Daniel John Debrunner (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-539) Update the Create Index statement in the Derby documentation with additional information
Date Thu, 25 Aug 2005 21:02:08 GMT
    [ 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


Mime
View raw message