[ http://issues.apache.org/jira/browse/DERBY-119?page=comments#action_12430785 ] Bryan Pendleton commented on DERBY-119: ---------------------------------------
Here's a short release note: The ALTER TABLE statement now includes the additional ability to alter the NULL / NOT NULL constraint on a column in a table. The syntax is: ALTER TABLE table ALTER [COLUMN] column [NOT] NULL You cannot remove the NOT NULL constraint if the column is used in a PRIMARY KEY or UNIQUE constraint. You cannot add the NOT NULL constraint if there are existing NULL values for the column in the table. > 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 > Fix For: 10.3.0.0 > > Attachments: alterColumnNotNull_1.diff, alterColumnNotNull_v2.diff, > alterColumnNotNull_v3.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
