On Friday 04 November 2005 22:13, [EMAIL PROTECTED] wrote: > Already found the answer: not, it can't. > Already voted for this on Jira... > > Tkx, > > Edson Richter > > > How can I make a NOT NULL column accept NULL values? > > > > Something like: > > > > alter table MY_TABLE > > alter MY_COLUMN set data type timestamp NULL > > > > Is this possible? Turn NOT NULL columns into NULLABLE columns? > > > > Richter Well you shouldn't stop there....
Using Informix as an example, when you alter a table, Informix will rename the table with a new/temp name, then create your new table. It then will copy all of the rows back to the original table. Note that if the table has been altered by adding a constraint, if a row in the previous table fails the new constraint, then it is gone. (Poof! Vanished...) No Error or Warnings, just gone. [More on this in a second...] Looking at Derby, if you were to try to rename a table, you will get an error if there is a foreign key, or a constraint on the table. In order to "fix" the ALTER TABLE command, you'll also need to fix the RENAME command to cascade to dependent objects as well. [Getting back to the Informix issue...] Now there is a problem, or rather a design issue, with how Informix implements an ALTER TABLE. You can lose data from the table without warning. (I believe that DB2 will roll back the transaction...) Neither is right or wrong. Its how the answer the question "Is there an order of precedence with respect to the container, or to the data? ". Informix says yes. IBM says no. Since this is a design issue, I'll let the developer forum figure that one out. -G "On a scale of 1 to 10, you know you're rated a 10 as a C programmer, when you can write a device driver using cat.... " -- Michael Segel Principal MSCC
