Re: [Info-Ingres] Making a nullable column not-nullable with ALTERTABLE?

Hi Karl,

This is from a topic earlier this year.

How difficult would it be to modify ingres to support this operation?
MSSQL does it by checking the values in the table at the time.
It is a limitation which makes life difficult for lazy programmers.

Just one syntax I would like to see:
Alter table XXX add column YYY integer not null default 0;
I'm not being greedy am I?

This should populate all rows automatically with the new value.
But, I am happy to do this if necessary.

Alter table XXX add column YYY integer;
Update XXX set YYY = 0;
Alter table XXX add column YYY integer not null default 0;



Paul
(cross posted to community forums)


-----Original Message-----
From: info-ingres-bounces-at-kettleriverconsulting.com
[mailto:info-ingres-bounces-at-kettleriverconsulting.com] On Behalf Of Karl
& Betty Schendel
Sent: Saturday, 19 January 2008 1:37 AM
To: info-ingres-at-kettleriverconsulting.com
Subject: Re: [Info-Ingres] Making a nullable column not-nullable with
ALTER TABLE?

At 5:21 AM -0800 1/18/08, Gerhard Hofmann wrote:
>Hi all,
>
>is it possible to change a nullable column to not-nullable with Ingres
>2006's ALTER TABLE... ALTER COLUMN... command?
>
>If yes, how would this look like?

Converting a column from nullable to non-nullable is explicitly checked
for and disallowed. I'm not 100% sure why, I suppose it's because there
would be no real good way of dealing with an existing NULL at runtime.
I think I would prefer to see it allowed, and convert existing NULL to
default; however this is arguably less "correct".

I guess you will have to drop and re-add the column.
If the table involved doesn't have a unique key, you could temporarily
add a holding column to that table and update it with the old column's
values while you drop and recreate the old column.

Karl
_______________________________________________
Info-Ingres mailing list
Info-Ingres-at-kettleriverconsulting.com
http://www.kettleriverconsulting.com...fo/info-ingres