Alter Table default value oddity

[Tommi Prami]

Yellow,

Attached 7z archive with FB5 DB ready for testing and Scripts, if want to create.

Table and domains and some test data:


-- STEP 1: Create Domains and Table
CREATE DOMAIN D_INT AS INTEGER;
CREATE DOMAIN D_INTID AS INTEGER CHECK((VALUE IS NOT NULL) AND (VALUE >= 0));
CREATE DOMAIN D_STR AS VARCHAR(50);

CREATE TABLE TEST_TABLE
(
  ID D_INTID NOT NULL,
  NAME D_STR NOT NULL,
  CONSTRAINT PK_TEST_TABLE PRIMARY KEY (ID)
);
COMMIT;

-- STEP 2: Insert Test data
INSERT INTO TEST_TABLE (ID, NAME) VALUES (1, '1');
INSERT INTO TEST_TABLE (ID, NAME) VALUES (2, '2');
INSERT INTO TEST_TABLE (ID, NAME) VALUES (3, '3');
INSERT INTO TEST_TABLE (ID, NAME) VALUES (4, '4');
INSERT INTO TEST_TABLE (ID, NAME) VALUES (5, '5');
COMMIT;

And if I alter the table, by adding following fields, I cant understand why FIELD_3 will not get initialized. I would expect it to do so. 

-- STEP 3: Add fields,
ALTER TABLE TEST_TABLE ADD FIELD_1 D_INT DEFAULT 0 NOT NULL;
ALTER TABLE TEST_TABLE ADD FIELD_2 D_INTID DEFAULT 0 NOT NULL;
ALTER TABLE TEST_TABLE ADD FIELD_3 D_INTID DEFAULT 0; -- WHY THIS WILL NOT GET INITIALIZED to 0
COMMIT;



-tee-

[Dimitry Sibiryakov]

Tommi Prami wrote 23.01.2026 12:22:
> ALTER TABLE TEST_TABLE ADD FIELD_3 D_INTID DEFAULT 0; -- WHY THIS WILL NOT GET
> INITIALIZED to 0

Because NULL is a valid value for it. Check constraints are not considered.

--
WBR, SD.

[Martijn Tonies (Upscene Productions)]

Hi,

 

>And if I alter the table, by adding following fields, I cant understand why FIELD_3 will not get initialized. I would expect it to do so.

>

>

 

>

>-- STEP 3: Add fields,

>

>

ALTER TABLE TEST_TABLE ADD FIELD_1 D_INT DEFAULT 0 NOT NULL;

>

>

ALTER TABLE TEST_TABLE ADD FIELD_2 D_INTID DEFAULT 0 NOT NULL;

>

ALTER TABLE TEST_TABLE ADD FIELD_3 D_INTID DEFAULT 0; -- WHY THIS WILL NOT GET INITIALIZED to 0

This is a nullable column so existing values won’t be touched. Only when a new record is inserted without a value for that column, the DEFAULT will be applied.

 

With regards,

Martijn Tonies
Upscene Productions
https://www.upscene.com

Database Workbench - developer tool for Oracle, SQL Server, PostgreSQL, MySQL, MariaDB, InterBase, SQLite, NexusDB and Firebird.

[Tommi Prami]

Hello,

Thanks for a speedy response.

Sadly don't still understand why :D

I think I need bit more explanation.

Or is it because Domain is not NOT NULL Domain?

-tee-

--
Support the ongoing development of Firebird! Consider donating to the Firebird Foundation and help ensure its future. Every contribution makes a difference. Learn more and donate here:
https://www.firebirdsql.org/donate
---
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion, visit https://groups.google.com/d/msgid/firebird-support/f25f2c64-bccf-433c-a19b-566bf76c13c3%40ibphoenix.com.

[Mark Rotteveel]

On 23/01/2026 12:27, Martijn Tonies (Upscene Productions) wrote:
> Hi,
> >And if I alter the table, by adding following fields, I cant
> understand why FIELD_3 will not get initialized. I would expect it to do
> so.
> >
> >
>
> >
> >-- STEP 3: Add fields,
> >
> >
> ALTER TABLE TEST_TABLE ADD FIELD_1 D_INT DEFAULT 0 NOT NULL;
> >
> >
> ALTER TABLE TEST_TABLE ADD FIELD_2 D_INTID DEFAULT 0 NOT NULL;
> >
> ALTER TABLE TEST_TABLE ADD FIELD_3 D_INTID DEFAULT 0; -- WHY THIS WILL
> NOT GET INITIALIZED to 0
>
> This is a nullable column so existing values won’t be touched. Only when
> a new record is inserted without a value for that column, the DEFAULT
> will be applied.

This *adds* a field, it doesn't add a default to an existing field.

In any case, according to the SQL standard, this is the wrong behaviour:
ISO/IEC 9075-2:2023 specifies in 11.11 <add column definition>, General
Rule 2(b)(ii):

"""
Otherwise, every value in C is the default value for C.
"""

And given Firebird can use format versions to add defaults, it shouldn't
need to touch existing rows.

I suggest creating a ticket for this.

Mark
--
Mark Rotteveel

[Tommi Prami]

Hello

If I created the domains as follows, it works the way I expected it to.

-- STEP 1: Create Domains and Table
CREATE DOMAIN D_INT AS INTEGER;

CREATE DOMAIN D_INTID AS INTEGER NOT NULL CHECK(VALUE >= 0);

CREATE DOMAIN D_STR AS VARCHAR(50);

And about the Ticket, I am not sure what it should be about?

Was thinking that could this fail

CREATE DOMAIN D_INTID AS INTEGER CHECK((VALUE IS NOT NULL) AND (VALUE >= 0));

Because it has check that does not fire. I mean " (VALUE IS NOT NULL)   " Engine could give user error that you have check that is not logical and never ever has a effect.

As I see it

CREATE DOMAIN D_INTID AS INTEGER CHECK((VALUE IS NOT NULL) AND (VALUE >= 0));

and
CREATE DOMAIN D_INTID AS INTEGER CHECK(VALUE >= 0);

Are both basically exactly the same. For sure there can be some other case when the " (VALUE IS NOT NULL) AND  " part could make some sense though, I dunno. 

-tee-

--
Support the ongoing development of Firebird! Consider donating to the Firebird Foundation and help ensure its future. Every contribution makes a difference. Learn more and donate here:
https://www.firebirdsql.org/donate
---
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion, visit https://groups.google.com/d/msgid/firebird-support/d47d6fa7-8ecc-4c37-9b2d-2a7f2b05b5ed%40lawinegevaar.nl.

[Mark Rotteveel]

On 23/01/2026 12:50, Tommi Prami wrote:
> Hello
>
> If I created the domains as follows, it works the way I expected it to.
>
> -- STEP 1: Create Domains and Table
> CREATE DOMAIN D_INT AS INTEGER;
> CREATE DOMAIN D_INTID AS INTEGER NOT NULL CHECK(VALUE >= 0);
> CREATE DOMAIN D_STR AS VARCHAR(50);
>
> And about the Ticket, I am not sure what it should be about?

That adding a column with a DEFAULT but without a NOT NULL constraint
should also set the default for existing columns; not just when there is
a NOT NULL constraint

> Was thinking that could this fail
>
> CREATE DOMAIN D_INTID AS INTEGER CHECK((VALUE IS NOT NULL) AND (VALUE >=
> 0));
>
> Because it has check that does not fire. I mean " (VALUE IS NOT NULL)
> " Engine could give user error that you have check that is not logical
> and never ever has a effect.

As Dimitry said, the engine doesn't evaluate CHECK constraints when
adding a column, only NOT NULL constraints. Funnily enough, I can't find
a reference in the standard whether they should be evaluated or not when
adding columns, but in that regard the standard can be a real pain in
the ass.

> As I see it
>
> CREATE DOMAIN D_INTID AS INTEGER CHECK((VALUE IS NOT NULL) AND (VALUE >=
> 0));
> and
> CREATE DOMAIN D_INTID AS INTEGER CHECK(VALUE >= 0);
>
> Are both basically exactly the same. For sure there can be some other
> case when the " (VALUE IS NOT NULL) AND  " part could make some
> sense though, I dunno.

They may be the same to you, but definitionally they are different things.

Mark
--
Mark Rotteveel