you'll have to use your method 1 below but you don't loose data...
create myflag2,
update set myflag2=myflag
now update myflag2 to replace any nulls to your default
now drop myflag
create myflag the way you want
update set myflag=myflag2
drop myflag2

Alan



-----Original Message-----
From: Hugo [mailto:hugosan@...]
Sent: Friday, 21 March 2003 12:38 AM
To: ib-support@yahoogroups.com
Subject: [ib-support] Alter Table, add default value to an existing
field (2nd post)


I've post this question some days ago, but I don't know if it arrives, so I
re-post:

I want to update the metadata of an existing table, but only adding a
DEFAULT value for a field that already exists.
For example, I have:
CREATE TABLE MYTABLE (
ID INTEGER NOT NULL PRIMARY KEY,
DESCRIPTION VARCHAR(40),
MYFLAG CHAR(1)
);
this table is in production, and now I want to convert it to:

CREATE TABLE MYTABLE (
ID INTEGER NOT NULL PRIMARY KEY,
DESCRIPTION VARCHAR(40),
MYFLAG CHAR(1) DEFAULT 'N'
);
After reading on SQL documentation about ALTER TABLE syntax, I cannot make:
ALTER TABLE MYTABLE ALTER COLUMN MYFLAG DEFAULT 'N';
nor
ALTER TABLE MYTABLE ALTER COLUMN MYFLAG TYPE CHAR(1) DEFAULT 'N';

1) The only possibility I see is:
ALTER TABLE MYTABLE
DROP MYFLAG,
ADD MYFLAG CHAR(1) DEFAULT 'N';
but this way I lost the data.

2) Another trick I have found, to avoid DROPing, is to define a DOMAIN:
CREATE DOMAIN MYDOMAIN CHAR(1) DEFAULT 'N';
and then
ALTER TABLE MYTABLE ALTER COLUMN MYFLAG TYPE MYDOMAIN;

But on both 1) and 2) solutions, I must DROP and recreate all PROCEDUREs and
TRIGGERs with dependencies on MYFLAG.
And, finally, the question:
Is there a way to add this default, directly via system tables
(RDB$RELATION_FIELDS, I suppose)?

Many thanks in advance,
Hugo.




To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/