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 20:48:39 GMT
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)

View raw message