Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 53963 invoked from network); 22 Aug 2005 23:53:40 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 22 Aug 2005 23:53:40 -0000 Received: (qmail 84714 invoked by uid 500); 22 Aug 2005 23:53:39 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 84693 invoked by uid 500); 22 Aug 2005 23:53:39 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 84678 invoked by uid 99); 22 Aug 2005 23:53:39 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 22 Aug 2005 16:53:39 -0700 X-ASF-Spam-Status: No, hits=2.3 required=10.0 tests=DNS_FROM_RFC_ABUSE,DNS_FROM_RFC_POST,DNS_FROM_RFC_WHOIS,HTML_30_40,HTML_MESSAGE X-Spam-Check-By: apache.org Received-SPF: neutral (asf.osuosl.org: local policy) Received: from [206.190.37.82] (HELO web81307.mail.yahoo.com) (206.190.37.82) by apache.org (qpsmtpd/0.29) with SMTP; Mon, 22 Aug 2005 16:53:56 -0700 Received: (qmail 19694 invoked by uid 60001); 22 Aug 2005 23:53:35 -0000 Message-ID: <20050822235335.19692.qmail@web81307.mail.yahoo.com> Received: from [32.97.110.142] by web81307.mail.yahoo.com via HTTP; Mon, 22 Aug 2005 16:53:35 PDT Date: Mon, 22 Aug 2005 16:53:35 -0700 (PDT) From: Susan Cline Subject: Re: Ability to create a unique index on the same column, doc bug or "bug" bug? To: Derby Discussion In-Reply-To: <200508221733.16620.msegel@segel.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1396343525-1124754815=:19134" Content-Transfer-Encoding: 8bit X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N --0-1396343525-1124754815=:19134 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Hi Michael, To answer your questions: Did Derby actually create the second index? Yes. Also when you altered the table, what happened to the initial index? Not sure what you mean by this ... nothing happened, it is still there. Depending on what you find, there may be a bug. If there is a bug, it would have to deal with the ALTER TABLE statement where you added a constraint. Can you also try to drop your initial index? Yes, I did drop it and it was successful. If you do, what happens to your second INDEX? The second index is still there, and I can successfully insert and select from the table. As I said above, here is what happens if I reverse the order: 1) Create the table 2) Alter the table to add the primary key constraint: I have a constraint called myprimkey2 I have a unique index called SQL050822044536100 3) Then I try to create another unique index called idx2 with this SQL: CREATE UNIQUE INDEX "APP"."idx2" ON "APP"."TABLE2" ("COL1" ASC); and it fails. SQLState=01504 The new index is a duplicate of an existing index: SQL050822044536100. : I think it is a bug, what do you think? Thanks, Susan "Michael J. Segel" wrote: On Monday 22 August 2005 15:33, Susan Cline wrote: > In the 'Create Index' statement documentation of the 10.1 Reference Guide > this statement is made about creating unique indexes: > > 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. > > However, this SQL succeeds and does create two unique indexes on the same > column: > > First I ran this SQL: > > CREATE TABLE APP.table5 ( > col1 CHAR(3) NOT NULL, > col2 INTEGER, > col3 VARCHAR(28) NOT NULL); > > Then I created a unique index called idx1 first: > > CREATE UNIQUE INDEX "APP"."idx1" ON "APP"."TABLE5" ("COL1" ASC); > > Then I added a primary key: > > ALTER TABLE "APP"."TABLE5" ADD CONSTRAINT "myprimkey" PRIMARY KEY (COL1); > > This added an additional unique index, a backing index: > > CREATE UNIQUE INDEX "APP"."SQL050822005949400" ON "APP"."TABLE5" ("COL1" > ASC); > > So either this is a Derby bug, and the second index should not be > created, or the documentation needs to be updated to say that if a unique > index exists, but a primary key is added to a column with a unique index it > will succeed. > > If I first create a primary key, which subsequently adds the backing index, > then I try to add the unique index it does fail. This behaviour does jive > with the documentation. > > Thanks, > > Susan Ok, Did Derby actually create the second index? Also when you altered the table, what happened to the initial index? Depending on what you find, there may be a bug. If there is a bug, it would have to deal with the ALTER TABLE statement where you added a constraint. Can you also try to drop your initial index? If you do, what happens to your second INDEX? -- Michael Segel Principal MSCC (312) 952-8175 --0-1396343525-1124754815=:19134 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
Hi Michael,
 
To answer your questions:

Did Derby actually create the second index?
 
    Yes.

Also when you altered the table, what happened to the initial index?
 
        Not sure what you mean by this ... nothing happened, it is still there.

Depending on what you find, there may be a bug. If there is a bug, it
would
have to deal with the ALTER TABLE statement where you added a
constraint.


Can you also try to drop your initial index?
 
   Yes, I did drop it and it was successful.

If you do, what happens to your second INDEX?
 
   The second index is still there, and I can successfully insert and select from the table.
 
 
As I said above, here is what happens if I reverse the order:
 
1) Create the table
 
2) Alter the table to add the primary key constraint:
     I have a constraint called myprimkey2
     I have a unique index called SQL050822044536100
 
3) Then I try to create another unique index called idx2 with this SQL:
 

CREATE UNIQUE INDEX "APP"."idx2" ON "APP"."TABLE2" ("COL1" ASC);

and it fails.

SQLState=01504 The new index is a duplicate of an existing index: SQL050822044536100. :

I think it is a bug, what do you think?
 
Thanks,
 
Susan


"Michael J. Segel" <msegel@segel.com> wrote:
On Monday 22 August 2005 15:33, Susan Cline wrote:
> In the 'Create Index' statement documentation of the 10.1 Reference Guide
> this statement is made about creating unique indexes:
>
> 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.
>
> However, this SQL succeeds and does create two unique indexes on the same
> column:
>
> First I ran this SQL:
>
> CREATE TABLE APP.table5 (
> col1 CHAR(3) NOT NULL,
> col2 INTEGER,
> col3 VARCHAR(28) NOT NULL);
>
> Then I created a unique index called idx1 first:
>
> CREATE UNIQUE INDEX "APP"."idx1" ON "APP"."TABLE5" ("COL1" ASC);
>
> Then I added a primary key:
>
> ALTER TABLE "APP"."TABLE5" ADD CONSTRAINT "myprimkey" PRIMARY KEY (COL1);
>
> This added an additional unique index, a backing index:
>
> CREATE UNIQUE INDEX "APP"."SQL050822005949400" ON "APP"."TABLE5" ("COL1"
> ASC);
>
> So either this is a Derby bug, and the second index should not be
> created, or the documentation needs to be updated to say that if a unique
> index exists, but a primary key is added to a column with a unique index it
> will succeed.
>
> If I first create a primary key, which subsequently adds the backing index,
> then I try to add the unique index it does fail. This behaviour does jive
> with the documentation.
>
> Thanks,
>
> Susan
Ok,
Did Derby actually create the second index?

Also when you altered the table, what happened to the initial index?

Depending on what you find, there may be a bug. If there is a bug, it would
have to deal with the ALTER TABLE statement where you added a constraint.


Can you also try to drop your initial index?
If you do, what happens to your second INDEX?


--
Michael Segel
Principal
MSCC
(312) 952-8175
--0-1396343525-1124754815=:19134--