Subject Re: [ib-support] Adding a default value for a field
Author joseph osende
>What I was actually hoping for was to alter the table
>in the Firebird<BR>
>database so that the Default value property of the
>field in the database is
set to the appropriate value.

>How do I do that? (Looking back my original question
>wasn't very clear.)<BR>

>Thanks,
>Stephen


Hi, Stephen !

-- I haven't found a way to modify
-- your table's structure using just one alter
- statement


-- Please find hereafter an example
-- of doing it the hard way

-- It worked fine on my computer :

-- Firebird 1.0 796
-- Ems QuickDesk
-- Windows XP Pro
-- Pentium IV, 1.5GHz, 512 MB

-- in a Sql Editor window

-- create the table
create table myTable
(
lname varchar( 25 ) not null,
is_deleted varchar( 1 )
);

commit ;


-- add some data
insert into myTable (lname, is_deleted ) values (
'dirac', '0' ) ;
insert into myTable (lname, is_deleted ) values (
'witten', '1' ) ;

commit ;


-- have a look
select * from myTable ;


-- add a column is_del having the desired properties
alter table myTable add is_del varchar(1) default 'F'
;

commit ;


-- copy the data from the old column, is_deleted,
-- to the new one, is_del
update myTable set is_del = is_deleted ;
commit ;

-- drop the old column, providing there are
-- no constraints, triggers, procedures, or views
-- depending on it
alter table myTable drop is_deleted ;
commit ; ;


-- rename the new column to the old name of the
-- dropped one
alter table myTable alter is_del to isdeleted ;
commit ; ;

-- insert a record without providing a value for the
-- field is_deleted
insert into myTable ( lname ) values ('greene') ;
commit ;

-- check the value of the column is_deleted for the
-- last inserted record ( lname = 'greene' )
select * from mytable where lname = 'greene' ;

-- the value of the column MUST be 'F' 'cause it's
-- the default

Hope it helps.

Best regards,

Joseph

___________________________________________________________
Do You Yahoo!? -- Une adresse @... gratuite et en fran├žais !
Yahoo! Mail : http://fr.mail.yahoo.com