db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <derby-...@db.apache.org>
Subject [jira] Updated: (DERBY-119) Add ALTER TABLE option to change column from NULL to NOT NULL
Date Sun, 13 Aug 2006 15:38:17 GMT
     [ http://issues.apache.org/jira/browse/DERBY-119?page=all ]

Bryan Pendleton updated DERBY-119:

    Attachment: alterColumnNotNull_v2.diff

attached is alterColumnNotNull_v2.diff, a revised patch proposal
which adds some additional tests and addresses the issue with
altering a UNIQUE column to NULL.

This patch prevents you from altering a UNIQUE column to NULL,
but I encountered some uncertainty about whether to create an
entirely new error message for this case, or just to extend the
existing PRIMARY KEY error message.

The path I took in this patch was to use the existing message, but
alter the message text to make it clear that it covers both cases now.

So I changed the check in ModifyColumnNode.java from

                   (existingConstraint.getConstraintType() ==
                   ((existingConstraint.getConstraintType() ==
                    DataDictionary.PRIMARYKEY_CONSTRAINT) ||
                    (existingConstraint.getConstraintType() ==

and I changed the text of the error message to read:

42Z20=Column ''{0}'' cannot be made nullable. It is part of a primary key or unique constraint,
which cannot have any nullable columns.

Reviewers: do you think it would be better to create an entirely
new message to cover the UNIQUE case, rather than extending the
existing message to cover the additional case?

> Add ALTER TABLE option to change column from NULL to NOT NULL
> -------------------------------------------------------------
>                 Key: DERBY-119
>                 URL: http://issues.apache.org/jira/browse/DERBY-119
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>            Reporter: Bernd Ruehlicke
>         Assigned To: Bryan Pendleton
>         Attachments: alterColumnNotNull_1.diff, alterColumnNotNull_v2.diff
> There was a thread about this on the Cloudscape forum
> http://www-106.ibm.com/developerworks/forums/dw_thread.jsp?message=4103269&cat=19&thread=59941&forum=370#4103269
> Since this describes the problem I will just copy the content of this entry as my dexscription
> The content of this was 
> "
> Hi,
> I stumbled across a behaviour of cloudscape which is not a bug but IMHO an implementation
choice. To assign a primary key to a table using ALTER TABLE all columns must be declared
NOT NULL first, which can only be specified upon column creation (no ALTER TABLE statement
exists to change the NOT NULL property of a column).
> Most databases I know do two things differently:
> 1) when a primary key is assigned all pk columns are automatically set to NOT NULL, if
one of them contains NULL values, the ALTER TABLE statement fails
> 2) it is possible to alter the column to set the NOT NULL property after column creation
(fails when there are already records containing NULL values)
> If I have understood the limitations correctly in Cloudscape I have no choice but to
remove and re-add the column which is supposed to be used in the primary key, if it is not
already declared as NOT NULL. This means that in the case of a table containing valid data
(unique and not null) in the column in all records, I would have to export the data, remove
and re-add the column and reimport that data, which would not be necessary e.g. in Oracle
or MaxDB.
> Is it possible to change that behaviour or is there a good reason for it? It looks as
if it makes the life of the user more difficult than necessary for certain metadata manipulations.
Making it possible to alter the NOT NULL property of a column would solve this and IMHO having
a primary key constraint do this implicitly makes sense as well. 
> Thanks in advance for any insight on this,
> Robert"

This message is automatically generated by JIRA.
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira


View raw message