FirebirdSQL logo

Adding a NOT NULL field

This is by far the preferred method in general, but it causes some special problems if used on a populated table, as you will see in a moment.First, add the field with the following statement:

alter table Adventures add id int not null

Or, if you want to name the constraint explicitly (this makes it easier if you ever want to drop it later):

alter table Adventures add id int constraint IdNotNull not null

Despite the NOT NULL constraint, the new ID fields that have been added to the existing rows will all be NULL.In this special case, Firebird allows invalid data to be present in a column.It will even write the NULLs to a backup without complaining, but it will refuse to restore them, precisely because of the constraint violation.

Note

Firebird 1.5 (but not 1.0 or 2.0) even allows you to make such a column the primary key!

False reporting of NULLs as zeroes

To make matters worse, Firebird lies to you when you retrieve data from the table.With isql and many other clients, “SELECT * FROM ADVENTURES” will return this dataset:

Table 1. Result set after adding a NOT NULL column
Name Bought Price ID

Maniac Mansion

12-Jun-1995

$ 49,--

0

Zak McKracken

9-Oct-1995

$ 54,95

0

Of course this will make most people think “OK, cool: Firebird used a default value of 0 for the new fields — nothing to worry about”.But you can verify that the ID fields are really NULL with these queries:

  • SELECT * FROM ADVENTURES WHERE ID = 0 (returns empty set)

  • SELECT * FROM ADVENTURES WHERE ID IS NULL (returns set shown above, with false 0’s)

  • SELECT * FROM ADVENTURES WHERE ID IS NOT NULL (returns empty set)

Another type of query hinting that something fishy is going on is the following:

  • SELECT NAME, ID, ID+3 FROM ADVENTURES

Such a query will return 0 in the “ID+3” column.With a true 0 ID it should have been 3.The correct result would be NULL, of course!

If the added NOT NULL column is of type (VAR)CHAR instead of INT, you will see phoney emptystrings ('').With a DATE column, phoney “zero dates” of 17 November 1858 (epoch of the Modified Julian Day).In all cases, the true state of the data is NULL.

What the…​

...is going on here?

When a client application like isql queries the server, the conversation passes through several stages.During one of them — the “describe” phase — the engine reports type and nullability for each column that will appear in the result set.It does this in a data structure which is later also used to retrieve the actual row data.For columns flagged as NOT NULL by the server, there is no way to return NULLs to the client — unless the client flips back the flag before entering the data retrieval stage.Most client applications don’t do this.After all, if the server assures you that a column can’t contain NULLs, why would you think you know better, override the server’s decision and check for NULLs anyway?And yet that’s exactly what you should do if you want to avoid the risk of reporting false values to your users.

Ensuring the validity of your data

Here’s what you should do to make sure that your data are valid when adding a NOT NULL column to a populated table:

  • To prevent the nulls-in-not-null-columns problem from occurring at all, provide a default value when you add the new column:

    alter table Adventures add id int default -1 not null

    Default values are normally not applied when adding fields to existing rows, but with NOT NULL fields they are.

  • Else, explicitly set the new fields to the value(s) they should have, right after adding the column.Verify that they are all valid with a “SELECT …​ WHERE …​ IS NULL” query, which should return an empty set.

  • If the damage has already been done and you find yourself with an unrestorable backup, use gbak's -n switch to ignore validity constraints when restoring.Then fix the data and reinstate the constraints manually.Again, verify with a “WHERE …​ IS NULL” query.

    Important

    Firebird versions up to and including 1.5.0 have an additional bug that causes gbak to restore NOT NULL constraints even if you specify -n.With those versions, if you have backed up a database with NULL data in NOT NULL fields, you’re completely stuck.Solution: install 1.5.1 or higher, restore with gbak -n and fix your data.

Adding a CHECKed column

Using a CHECK constraint is another way to disallow NULL entries in a column:

alter table Adventures add id int check (id is not null)

If you do it this way, a subsequent SELECT will return:

Table 1. Result set after adding a CHECKed field
Name Bought Price ID

Maniac Mansion

12-Jun-1995

$ 49,--

<null>

Zak McKracken

9-Oct-1995

$ 54,95

<null>

Well, at least now you can see that the fields are NULL!Firebird does not enforce CHECK constraints on existing rows when you add new fields.The same is true if you add checks to existing fields with ADD CONSTRAINT or ADD CHECK.

This time, Firebird not only tolerates the presence and the backing up of the NULL entries, but it will also restore them.Firebird’s gbak tool does restore CHECK constraints, but doesn’t apply them to the existing data in the backup.

Note

Even with the -n switch, gbak restores CHECK constraints.But since they are not used to validate backed-up data, this will never lead to a failed restore.

This restorability of your NULL data despite the presence of the CHECK constraint is consistent with the fact that Firebird allows them to be present in the first place, and to be backed up as well.But from a pragmatical point of view, there’s a downside: you can now go through cycle after cycle of backup and restore, and your “illegal” data will survive without you even receiving a warning.So again: make sure that your existing rows obey the new rule immediately after adding the constrained column.The “default” trick won’t work here;you’ll just have to remember to set the right value(s) yourself.If you forget it now, chances are that your outlawed NULLs will survive for a long time, as there won’t be any wake-up calls later on.

Note

The isql command SHOW TABLE lists “CHECK …​ IS NOT NULL” columns as nullable, because the column type is not intrinsically NOT NULL.But it also shows the CHECKs, so you know how things stand.

Likewise, the engine describes these columns as nullable when a query is executed.This accounts for the fact that NULLs are truthfully reported in this case, as you’ve seen in the table above.