At 06:58 PM 2/24/2001 +0300, Dmitry Yemanov wrote:

>Maybe I wasn't clear enough. I was talking about inserts only. Existing
>values should be kept as they are. I meant how I can change the DEFAULT
>value to force the engine to use it in all subsequent inserts instead of the
>old one. It is not allowed in IB by using ALTER TABLE ALTER COLUMN
>statement. ALTER DOMAIN statement modifies RDB$FIELDS only and doesn't touch
>RDB$RELATION_FIELDS, so I cannot use it for this purpose either.

Have you actually tried this? I just did and it appeared to work. See
below.

Ahh!!! Now I understand. If you defined the constraint on the column
definition, it was stored in RDB$RELATION_FIELDS even if a dummy
domain was created. OK. Here's what you can do - though it's not lovely.
First, update the domain. Then, force the update through to the fields.

update rdb$relation_fields
set rdb$default_value =
(select rdb$default_value
from rdb$fields f
where f.rdb$field_name = b$relation_fields.rdb$field_source)
where rdb$field_name = <my column>
and rdb$relation_name = <my table>;

update rdb$relation_fields
set rdb$default_source =
(select rdb$default_source
from rdb$fields f
where f.rdb$field_name = rdb$relation_fields.rdb$field_source)
where rdb$field_name = <my column>
and rdb$relation_name = <my table>;

Sigh.

Regards,

Ann
www.ibphoenix.com
We have answers.

SQL> alter domain A set default 45;
SQL> insert into xyzzy (b) values (
SQL> select * from xyzzy;

A B
======= ============

45 32

SQL> alter domain A set default 97;
SQL> insert into xyzzy (b) values (
SQL> select * from xyzzy;

A B
======= ============

45 32
97 42

SQL> alter domain A set default 55;
SQL> insert into xyzzy (b) values (
SQL> select * from xyzzy;

A B
======= ============

45 32
97 42
55 82

SQL> commit;