You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Susan Cline <ho...@pacbell.net> on 2005/08/22 22:33:21 UTC
Ability to create a unique index on the same column, doc bug or "bug" bug?
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
Re: Ability to create a unique index on the same column, doc bug or "bug" bug?
Posted by Susan Cline <ho...@pacbell.net>.
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" <ms...@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
Re: Ability to create a unique index on the same column, doc bug or "bug" bug?
Posted by "Michael J. Segel" <ms...@segel.com>.
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
Re: Ability to create a unique index on the same column, doc bug or "bug" bug?
Posted by "Michael J. Segel" <ms...@segel.com>.
On Tuesday 23 August 2005 12:27, Mike Matrigali wrote:
> Is there any database system out there that ignores current data
> when adding a constraint to the table? It seems almost all applications
> which require a constraint to be added, will fail if "some" of the
> data does not meet the constraint.
>
Absolutely.
Ok, so maybe the bath tub gin I used to drink really did rot my brain cells.
I'm in the process of setting up a sample DB2 and IDS 10 instances on one of
my linux boxes so I can confirm this, rather than going from memory. ;-)
Try my NULL experiment for yourself. If my brain is still functioning, you can
create the table with NULLs, then alter the table to have a NOT NULL
contraint added to that column. It should accept that constraint, however any
data entered previous to the constraint should still exist within the table.
You just can't update or alter those rows. Again, the alter or update of the
row would then occur after you have applied the constraint so that row would
be rejected.
> For Derby's implementation of uniqueness constaint this just won't work.
> When the constraint is added a unique index is created on the existing
> data and it will fail when it hits a duplicate.
>
OK,
Think about what you're doing. You're creating a new index using a new
constraint. Since there is duplicate data, it will fail. No argument there.
As I said, the constraints only apply to actions moving forward. (Even though
its an 'atomic' action, the constraint is applied before actually creating
the index.)
If you go back to the initial question, Susan is talking about creating a
table, then an index, then the constraint, which creates its own unique index
to enforce the constraint.
When you create a unique constraint on a table, why would you check to see if
an index already exists? Hence her issue.
Does this make sense?
-G
> 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.
> >
> > Its not a bug.
> >
> >>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.
> >
> > Yes and no.
> >
> > Yes, this is what is supposed to happen.
> > No, while it doesn't "jive", its not to say that the documentation is
> > incorrect... ;-)
> >
> > What you're looking at is a design issue.
> >
> > There's a couple of issues at work. The ALTER TABLE statement, indexes
> > and constraints.
> >
> > Consider this... You create a table foo, with two columns, bar and retz.
> > bar is an integer, retz is a character string. You populate the table so
> > it looks like this:
> >
> > Foo:
> >
> > BAR RETZ
> > 1 "abc"
> > 2 "def"
> > 3 NULL
> > 4 "jam"
> > ...
> > Now you then apply an ALTER TABLE command to restrict column RETZ from
> > containing NULLS.
> >
> > The question is... should the ALTER TABLE command fail because you've
> > already added rows that have NULL?
> >
> > Ok, that was a rhetorical question. The answer is no. The ALTER TABLE
> > command should succeed and any new data added would have to pass that
> > constraint on RETZ to see if the value is NOT NULL. So if you insert the
> > pair (10, NULL), it would get rejected, however the data pair (3, NULL)
> > can exist within the table, just don't update that row. ;-)
> >
> > In general the design issue is that when you apply a constraint to the
> > table, you ignore the current data that resides in the table, and only
> > apply the constraint to data that has been added after you altered the
> > table.
> >
> > The same can be said for the indexes.
> > This is really a design issue. KISS applies. ;-)
> > At some point, you have to trust that you have a real DBA and that he/she
> > knows what they are doing. ;-)
> >
> > Does that make sense?
> >
> > I guess if you want to get fancy, you could set up some logic that would
> > check to see if the index already exists and then utilizes that index and
> > just adds the constraint, but as I said, KISS applies. Why over
> > complicate things? (What do you think this is, DB2?)
> > [Yeah, I know. Gumby is back. ;-) If anyone from IBM wants to give me a
> > hard time, you've got my e-mail address. ;-]
--
Michael Segel
Principal
MSCC
(312) 952-8175
Re: Ability to create a unique index on the same column, doc bug
or "bug" bug?
Posted by Mike Matrigali <mi...@sbcglobal.net>.
Is there any database system out there that ignores current data
when adding a constraint to the table? It seems almost all applications
which require a constraint to be added, will fail if "some" of the
data does not meet the constraint.
For Derby's implementation of uniqueness constaint this just won't work.
When the constraint is added a unique index is created on the existing
data and it will fail when it hits a duplicate.
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.
>>
>
> Its not a bug.
>
>>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.
>>
>
> Yes and no.
>
> Yes, this is what is supposed to happen.
> No, while it doesn't "jive", its not to say that the documentation is
> incorrect... ;-)
>
> What you're looking at is a design issue.
>
> There's a couple of issues at work. The ALTER TABLE statement, indexes and
> constraints.
>
> Consider this... You create a table foo, with two columns, bar and retz.
> bar is an integer, retz is a character string. You populate the table so it
> looks like this:
>
> Foo:
>
> BAR RETZ
> 1 "abc"
> 2 "def"
> 3 NULL
> 4 "jam"
> ...
> Now you then apply an ALTER TABLE command to restrict column RETZ from
> containing NULLS.
>
> The question is... should the ALTER TABLE command fail because you've already
> added rows that have NULL?
>
> Ok, that was a rhetorical question. The answer is no. The ALTER TABLE command
> should succeed and any new data added would have to pass that constraint on
> RETZ to see if the value is NOT NULL. So if you insert the pair (10, NULL),
> it would get rejected, however the data pair (3, NULL) can exist within the
> table, just don't update that row. ;-)
>
> In general the design issue is that when you apply a constraint to the table,
> you ignore the current data that resides in the table, and only apply the
> constraint to data that has been added after you altered the table.
>
> The same can be said for the indexes.
> This is really a design issue. KISS applies. ;-)
> At some point, you have to trust that you have a real DBA and that he/she
> knows what they are doing. ;-)
>
> Does that make sense?
>
> I guess if you want to get fancy, you could set up some logic that would check
> to see if the index already exists and then utilizes that index and just adds
> the constraint, but as I said, KISS applies. Why over complicate things?
> (What do you think this is, DB2?)
> [Yeah, I know. Gumby is back. ;-) If anyone from IBM wants to give me a hard
> time, you've got my e-mail address. ;-]
Re: Ability to create a unique index on the same column,
doc bug or "bug" bug?
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> Michael J. Segel wrote (2005-08-22 22:16:31):
> Consider this... You create a table foo, with two columns, bar and retz.
> bar is an integer, retz is a character string. You populate the table so it
> looks like this:
>
> Foo:
>
> BAR RETZ
> 1 "abc"
> 2 "def"
> 3 NULL
> 4 "jam"
> ...
> Now you then apply an ALTER TABLE command to restrict column RETZ from
> containing NULLS.
>
> The question is... should the ALTER TABLE command fail because
> you've already added rows that have NULL?
>
> Ok, that was a rhetorical question. The answer is no. The ALTER
> TABLE command should succeed and any new data added would have to
> pass that constraint on RETZ to see if the value is NOT NULL. So if
> you insert the pair (10, NULL), it would get rejected, however the
> data pair (3, NULL) can exist within the table, just don't update
> that row. ;-)
>
> In general the design issue is that when you apply a constraint to
> the table, you ignore the current data that resides in the table,
> and only apply the constraint to data that has been added after you
> altered the table.
I think that this does not make sense. Code optimization for queries
would be impossible (or very difficult, becase the optimizer would
have to know the ALTER-history of the table and which values were in the
table at each change).
Consider the table you have populated and the following statements
INSERT INTO Foo VALUES(4,"FOO");
ALTER TABLE Foo ADD CONSTRAINT c1 UNIQUE (BAR);
SELECT DISTINCT BAR FROM Foo;
An optimizer could ignore DISTINCT, bot not in this case since the
table actually contains duplicates. A lot of similar examples could be
constructed.
And, an application can not rely on metadata to say anything about the
values in the table.
So generally: if db ignores the values in the table when constraints
is added, optimizations becomes impossible and one whould have no or
little use of indexes and constraints (the constraint no longer says
something about the values in the table, so neither the opimizator nor
the application can assume any constraint on the values already there,
just on values added to the table after the constraint was added).
--
Bernt Marius Johnsen, Database Technology Group,
Sun Microsystems, Trondheim, Norway
Re: Ability to create a unique index on the same column, doc bug or "bug" bug?
Posted by "Michael J. Segel" <ms...@segel.com>.
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.
>
Its not a bug.
> 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.
>
Yes and no.
Yes, this is what is supposed to happen.
No, while it doesn't "jive", its not to say that the documentation is
incorrect... ;-)
What you're looking at is a design issue.
There's a couple of issues at work. The ALTER TABLE statement, indexes and
constraints.
Consider this... You create a table foo, with two columns, bar and retz.
bar is an integer, retz is a character string. You populate the table so it
looks like this:
Foo:
BAR RETZ
1 "abc"
2 "def"
3 NULL
4 "jam"
...
Now you then apply an ALTER TABLE command to restrict column RETZ from
containing NULLS.
The question is... should the ALTER TABLE command fail because you've already
added rows that have NULL?
Ok, that was a rhetorical question. The answer is no. The ALTER TABLE command
should succeed and any new data added would have to pass that constraint on
RETZ to see if the value is NOT NULL. So if you insert the pair (10, NULL),
it would get rejected, however the data pair (3, NULL) can exist within the
table, just don't update that row. ;-)
In general the design issue is that when you apply a constraint to the table,
you ignore the current data that resides in the table, and only apply the
constraint to data that has been added after you altered the table.
The same can be said for the indexes.
This is really a design issue. KISS applies. ;-)
At some point, you have to trust that you have a real DBA and that he/she
knows what they are doing. ;-)
Does that make sense?
I guess if you want to get fancy, you could set up some logic that would check
to see if the index already exists and then utilizes that index and just adds
the constraint, but as I said, KISS applies. Why over complicate things?
(What do you think this is, DB2?)
[Yeah, I know. Gumby is back. ;-) If anyone from IBM wants to give me a hard
time, you've got my e-mail address. ;-]
--
Michael Segel
Principal
MSCC
(312) 952-8175