Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (asf.osuosl.org: local policy)
From: "Michael J. Segel" <msegel@segel.com>
To: derby-user@db.apache.org
Subject: Re: Ability to create a unique index on the same column,
 doc bug or "bug" bug?
Date: Tue, 23 Aug 2005 13:30:46 -0500
User-Agent: KMail/1.8
Cc: Mike Matrigali <mikem_app@sbcglobal.net>
References: <20050822203321.25920.qmail@web81303.mail.yahoo.com>
 <200508222216.31305.msegel@segel.com> <430B5C95.9020705@sbcglobal.net>
In-Reply-To: <430B5C95.9020705@sbcglobal.net>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200508231330.46241.msegel@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