db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Segel <mse...@segel.com>
Subject Re: Alter table ... alter column null
Date Sat, 05 Nov 2005 15:51:30 GMT
On Friday 04 November 2005 22:13, edson.richter@mgrinformatica.com.br 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 


"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

View raw message