[ 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() ==
DataDictionary.PRIMARYKEY_CONSTRAINT))
to
((existingConstraint.getConstraintType() ==
DataDictionary.PRIMARYKEY_CONSTRAINT) ||
(existingConstraint.getConstraintType() ==
DataDictionary.UNIQUE_CONSTRAINT)))
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
