db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dan Scott <deni...@gmail.com>
Subject Re: Ability to create a unique index on the same column, doc bug or "bug" bug?
Date Thu, 25 Aug 2005 21:00:47 GMT
On 8/25/05, Dan Scott <denials@gmail.com> wrote:
> On 8/25/05, Daniel John Debrunner <djd@debrunners.com> wrote:
> > Michael J. Segel wrote:
> >
> > > On Thursday 25 August 2005 09:25, Daniel John Debrunner wrote:
> > >
> > >>Michael J. Segel wrote:
> > >>
> > >>>On Wednesday 24 August 2005 21:50, Jean T. Anderson wrote:
> > >>>*WARNING*
> > >>>This post may require the readers donning flame retardant clothing.
;-)
> > >>
> > >>It seems to me that Susan and Michael are discussing different aspects
> > >>of constraints and maybe that is where the confusion is coming in.
> > >>
> > >
> > > Uhm not exactly.
> > >
> > > Sigh.
> > > Before I begin, let me be clear that while I am not flaming anyone or trying
> > > to start a flame war, I sense that some may have misinterpreted my comments.
> > > Hence my warning about flame retardant clothing. :-)
> > >
> > > Ok, lets recap.
> > >
> > > Susan indicated that she saw what she thought was an inconsistency on how a
> > > constraint worked on an index. (HINT: UNIQUE or PRIMARY KEY specifications
is
> > > a CONSTRAINT. ) She thought that 1) This was a bug. and 2) That the
> > > documentation should better explain how this can occur.
> > >
> > > What she saw was not a bug or an inconsistency at all. Constraints only impose
> > > their rules on an object after they have been invoked.
> > >
> > > I chose the example of altering a column in a table that accepted NULLS to
now
> > > not except NULLS. If you then do a select on the table, you will see that
> > > NULLS can still exist in the table.  This would show the behavior of a
> > > constraint without having to deal with Indexes and also implied constraints.
> > > This is actually the simplest examples of implementing a constraint....
> >
> > I still don't see what existing data has to do with the issue Susan was
> > raising, but at least two databases enforce that existing data must
> > match added constraints, Derby and Microsoft SQL Server. It does look as
> > though other databases take different approaches. Derby's model is that
> > constraints are enforced at all times on all the data, I think this
> > makes most sense for an "easy to use" database.
> >
> > Derby
> > http://db.apache.org/derby/docs/10.1/ref/rrefsqlj81859.html
> > "When adding a foreign key or check constraint to an existing table,
> > Derby checks the table to make sure existing rows satisfy the
> > constraint. If any row is invalid, Derby throws a statement exception
> > and the constraint is not added."
> >
> > Microsoft SQL Server
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_3ied.asp
> > "When constraints are added, all existing data is verified for
> > constraint violations. If any violations occur, the ALTER TABLE
> > statement fails and an error is returned."
> >
> > Dan.
> >
> 
> For what it's worth, in this scenario DB2 on Linux also fails with an
> error (SQL0542, to be exact, with SQLSTATE 42831):
> 
> dan@localhost ~ $ db2 'create table testy (id integer)'
> DB20000I  The SQL command completed successfully.
> dan@localhost ~ $ db2 'insert into testy (id) values (NULL)'
> DB20000I  The SQL command completed successfully.
> dan@localhost ~ $ db2 'alter table testy add constraint pk_1 primary key  (id)'
> DB21034E  The command was processed as an SQL statement because it was not a
> valid Command Line Processor command.  During SQL processing it returned:
> SQL0542N  "ID" cannot be a column of a primary key or unique key because it
> can contain null values.  SQLSTATE=42831
> 
> Dan (the other)
> 

Sorry, that was a slightly bogus example; the addition of the primary
key constraint failed because the column wasn't defined as null, not
because the column already contained null values. A better example is:

dan@localhost ~ $ db2 'alter table testy add constraint null_1 check
(id is not null)'
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0544N  The check constraint "NULL_1" cannot be added because the table 
contains a row that violates the constraint.  SQLSTATE=23512

Same result, different SQLCODE and SQLSTATE :)

Dan

Mime
View raw message