db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: Ability to create a unique index on the same column, doc bug or "bug" bug?
Date Tue, 23 Aug 2005 16:39:46 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Thanks for the research... I think Derby has this optimization that
only catches the case of duplicate indexes one way. (Where
primary/unique constraint is created first) The second duplicate index
that may get created the other way doesn't cause any issues, other than
being redundent and wasting cycles. This is not techincally a bug... a
partially implemented optimization. Mike had mentioned how this
optimization can be made more generic last week.<br>
<br>
Regarding documentation, it only talks about catching duplicate indexes
created after creating primary/unique constraints. It doesn't mention
what happens if an index is created first, before the constraints. May
be docs can be made more clearer, until the optimization is made more
generic.<br>
<br>
Satheesh<br>
<br>
Susan Cline wrote:
<blockquote cite="mid20050822235335.19692.qmail@web81307.mail.yahoo.com"
 type="cite">
  <div>Hi Michael,</div>
  <div>&nbsp;</div>
  <div>To answer your questions:</div>
  <div><br>
Did Derby actually create the second index?</div>
  <div>&nbsp;</div>
  <div>&nbsp;&nbsp;&nbsp; Yes.<br>
  <br>
Also when you altered the table, what happened to the initial index? </div>
  <div>&nbsp;</div>
  <div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Not sure
what you mean by this ... nothing happened, it
is still there.<br>
  <br>
Depending on what you find, there may be a bug. If there is a bug, it <br>
would <br>
have to deal with the ALTER TABLE statement where you added a <br>
constraint.<br>
  <br>
  <br>
Can you also try to drop your initial index?</div>
  <div>&nbsp;</div>
  <div>&nbsp;&nbsp; Yes, I did drop it and it was successful.</div>
  <div><br>
If you do, what happens to your second INDEX?</div>
  <div>&nbsp;</div>
  <div>&nbsp;&nbsp; The second index is still there, and I can successfully
insert and select from the table.</div>
  <div>&nbsp;</div>
  <div>&nbsp;</div>
  <div>As I said above, here is what happens if I reverse the order:</div>
  <div>&nbsp;</div>
  <div>1) Create the table</div>
  <div>&nbsp;</div>
  <div>2) Alter the table to add the primary key constraint:</div>
  <div>&nbsp;&nbsp;&nbsp;&nbsp; I have a constraint called myprimkey2</div>
  <div>&nbsp;&nbsp;&nbsp;&nbsp; I have a unique index called SQL050822044536100</div>
  <div>&nbsp;</div>
  <div>3) Then I try to create another unique index called idx2 with
this SQL:</div>
  <div>&nbsp;</div>
  <div><font size="2">
  </font>
  <p><font color="#800000" size="2">CREATE</font><font size="2"> </font><font
 color="#800000" size="2">UNIQUE</font><font size="2"> </font><font
 color="#800000" size="2">INDEX</font><font size="2"> </font><font
 color="#008000" size="2">"APP"</font><font size="2">.</font><font
 color="#008000" size="2">"idx2"</font><font size="2"> </font><font
 color="#800000" size="2">ON</font><font size="2"> </font><font
 color="#008000" size="2">"APP"</font><font size="2">.</font><font
 color="#008000" size="2">"TABLE2"</font><font size="2"> (</font><font
 color="#008000" size="2">"COL1"</font><font size="2"> </font><font
 color="#800000" size="2">ASC</font><font size="2">);</font></p>
  <p>and it fails.</p>
  </div>
  <font size="1"></font>
  <p><font size="1">SQLState=01504 The new index is a duplicate of an
existing index: SQL050822044536100. : </font></p>
  <div>I think it is a bug, what do you think?</div>
  <div>&nbsp;</div>
  <div>Thanks,</div>
  <div>&nbsp;</div>
  <div>Susan<br>
  <br>
  <br>
  <b><i>"Michael J. Segel" <a class="moz-txt-link-rfc2396E" href="mailto:msegel@segel.com">&lt;msegel@segel.com&gt;</a></i></b>
wrote:</div>
  <blockquote class="replbq"
 style="border-left: 2px solid rgb(16, 16, 255); padding-left: 5px; margin-left: 5px;">On
Monday 22 August 2005 15:33, Susan Cline wrote:<br>
&gt; In the 'Create Index' statement documentation of the 10.1
Reference Guide<br>
&gt; this statement is made about creating unique indexes:<br>
&gt;<br>
&gt; Indexes and constraints<br>
&gt;<br>
&gt; Unique, primary key, and foreign key constraints generate indexes
that<br>
&gt; enforce or "back" the constraint (and are thus sometimes called
backing<br>
&gt; indexes). If a column or set of columns has a UNIQUE or PRIMARY KEY<br>
&gt; constraint on it, you can not create an index on those columns.<br>
&gt;<br>
&gt; However, this SQL succeeds and does create two unique indexes on
the same<br>
&gt; column:<br>
&gt;<br>
&gt; First I ran this SQL:<br>
&gt;<br>
&gt; CREATE TABLE APP.table5 (<br>
&gt; col1 CHAR(3) NOT NULL,<br>
&gt; col2 INTEGER,<br>
&gt; col3 VARCHAR(28) NOT NULL);<br>
&gt;<br>
&gt; Then I created a unique index called idx1 first:<br>
&gt;<br>
&gt; CREATE UNIQUE INDEX "APP"."idx1" ON "APP"."TABLE5" ("COL1" ASC);<br>
&gt;<br>
&gt; Then I added a primary key:<br>
&gt;<br>
&gt; ALTER TABLE "APP"."TABLE5" ADD CONSTRAINT "myprimkey" PRIMARY KEY
(COL1);<br>
&gt;<br>
&gt; This added an additional unique index, a backing index:<br>
&gt;<br>
&gt; CREATE UNIQUE INDEX "APP"."SQL050822005949400" ON "APP"."TABLE5"
("COL1"<br>
&gt; ASC);<br>
&gt;<br>
&gt; So either this is a Derby bug, and the second index should not be<br>
&gt; created, or the documentation needs to be updated to say that if a
unique<br>
&gt; index exists, but a primary key is added to a column with a unique
index it<br>
&gt; will succeed.<br>
&gt;<br>
&gt; If I first create a primary key, which subsequently adds the
backing index,<br>
&gt; then I try to add the unique index it does fail. This behaviour
does jive<br>
&gt; with the documentation.<br>
&gt;<br>
&gt; Thanks,<br>
&gt;<br>
&gt; Susan<br>
Ok,<br>
Did Derby actually create the second index?<br>
    <br>
Also when you altered the table, what happened to the initial index? <br>
    <br>
Depending on what you find, there may be a bug. If there is a bug, it
would <br>
have to deal with the ALTER TABLE statement where you added a
constraint.<br>
    <br>
    <br>
Can you also try to drop your initial index?<br>
If you do, what happens to your second INDEX?<br>
    <br>
    <br>
-- <br>
Michael Segel<br>
Principal<br>
MSCC<br>
(312) 952-8175<br>
  </blockquote>
</blockquote>
</body>
</html>


Mime
View raw message