2017-04-25 11:24 GMT-03:00 Svein Erling Tysvær setys...@gmail.com [firebird-support] <firebird-support@yahoogroups.com>:
> > > 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