On 8/25/05, Dan Scott <[EMAIL PROTECTED]> wrote: > On 8/25/05, Daniel John Debrunner <[EMAIL PROTECTED]> 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): > > [EMAIL PROTECTED] ~ $ db2 'create table testy (id integer)' > DB20000I The SQL command completed successfully. > [EMAIL PROTECTED] ~ $ db2 'insert into testy (id) values (NULL)' > DB20000I The SQL command completed successfully. > [EMAIL PROTECTED] ~ $ 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: [EMAIL PROTECTED] ~ $ 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
