2017-04-25 11:24 GMT-03:00 Svein Erling Tysvær [email protected]
[firebird-support] <[email protected]>:
>
>
> Hi everyone,
>>
>> I encountered strange behavior droping a column definition with default
>> value.
>>
>> The environment is Ubuntu 16.04 LTS 64 bits, Firebird version
>> LI-V2.5.6.27020 (Firebird 2.5 SuperClassic)
>>
>>
>> I'll try to explain with an example. The statement sequence is:
>>
>> 1. Create the table
>> CREATE TABLE "SCHEMA_AUTHOR" ("ID" integer NOT NULL PRIMARY KEY, "NAME"
>> varchar(255) NOT NULL, "HEIGHT" integer CHECK ("HEIGHT" >= 0))
>>
>> 2. Popultate with some data
>> INSERT INTO SCHEMA_AUTHOR (ID, NAME, HEIGHT) VALUES ('1', 'Anonimus1',
>> NULL);
>>
>> 3. Alter the table
>> ALTER TABLE "SCHEMA_AUTHOR" ADD "SURNAME" varchar(15) DEFAULT 'surname
>> default' NOT NULL
>>
>> After step 3, I have a record with a new column with 'surname default'
>> value into SURNAME field.
>>
>> 4. Drop default definition
>> ALTER TABLE "SCHEMA_AUTHOR" ALTER COLUMN "SURNAME" DROP DEFAULT
>>
>> After step 4, the SURNAME field value is setting to empty string ('')
>>
>> Is that the correct behavior ?
>>
>
> I think you're wrong in assuming that the record after step 3 contains
> 'surname default'. Please try
>
> select iif( "SURNAME" is null, 'I'm empty', 'I contain a value')
> from SCHEMA_AUTHOR
>
After step 3, that select returns "I contain a value".
Kinda confusing don't?
Ok, to add a new NOT NULL field with a DEFAULT definition should not
populate the existing rows.
> after step 3. I don't use Firebird 3 myself, but I'd be greatly surprised
> if they'd changed so that existing records got default values when defining
> the default values. Also, take a look at this old document:
> https://firebirdsql.org/manual/nullguide-alter-pop-tables.html.
>
I'm using Firebird 2.5 (I dont test this in firebird 3).
>
> Basically, what you should do is to add another step after step 3:
>
> UPDATE SCHEMA_AUTHOR
> SET "SURNAME" = 'surname default';
>
>
Yes, this seem like ever we need to run an update to populate the not null
field, despite we set this with a default.
Now it seems a little more clear for me.
Regards
Maxi
