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: neutral (asf.osuosl.org: local policy)
Message-ID: <430E2C0A.8080606@debrunners.com>
Date: Thu, 25 Aug 2005 13:37:30 -0700
From: Daniel John Debrunner <djd@debrunners.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
 rv:1.7.3) Gecko/20040910
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: Ability to create a unique index on the same column, doc bug
 or "bug" bug?
References: <20050824233332.81997.qmail@web81305.mail.yahoo.com>
 <200508250822.09759.msegel@segel.com> <430DD4F5.7030805@debrunners.com>
 <200508251415.14261.msegel@segel.com>
In-Reply-To: <200508251415.14261.msegel@segel.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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.