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