Altering populated tables

Adding a non-nullable field to a populated table

Suppose you have this table:

Table 8. Adventures table

Name	Bought	Price
Maniac Mansion	12-Jun-1995	$ 49,--
Zak McKracken	9-Oct-1995	$ 54,95

You have already entered some adventure games in this table when you decide to add a non-nullable ID field. There are two ways to go about this, both with their own specific problems.

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 this statement:

alter table Adventures add id int not null

After committing, 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 NOT NULL column. It will also back them up without complaining, but it will refuse to restore them, precisely because of this violation of the NOT NULL constraint.

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 `NULL`s 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 9. 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!

With a (VAR)CHAR column, you would have seen 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.

Explanation

What's 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.

FSQL

Firebird expert Ivan Prenosil has written a free command-line client that works almost the same as isql, but – among other enhancements – reports NULLs correctly, even in NOT NULL columns. It's called FSQL and you can download it here:

http://www.volny.cz/iprenosil/interbase/fsql.htm

Ensuring the validity of your data

This is 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 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 are really up the creek. 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 10. 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.

Adding a non-nullable field using domains

Instead of specifying data types and constraints directly, you can also use domains, e.g. like this:

create domain icnn as int check (value is not null);
alter table Adventures add id icnn;

For the presence of NULL fields, returning of false 0's, effects of default values etc., it makes no difference at all whether you take the domain route or the direct approach. However, a NOT NULL constraint that came with a domain can later be removed; a direct NOT NULL on the column will stay forever.

Making existing columns non-nullable

Making an existing column NOT NULL

You cannot add NOT NULL to an existing column, but there's a simple workaround. Suppose the current type is int, then this:

create domain intnn as int not null;
alter table MyTable alter MyColumn type intnn;

will change the column type to “int not null”.

If the table already had records, any NULLs in the column will remain NULL, and again Firebird will report them as 0 to the user when queried. The situation is almost exactly the same as when you add a NOT NULL column (see Adding a NOT NULL field). The only difference is that if you give the domain (and therefore the column) a default value, this time you can't be sure that it will be applied to the existing NULL entries. Tests show that sometimes the default is applied to all NULLs, sometimes to none, and in a few cases to some of the existing entries but not to others! Bottom line: if you change a column's type and the new type includes a default, double-check the existing entries – especially if they “seem to be” 0 or zero-equivalents.

Adding a CHECK constraint to an existing column

There are two ways to add a CHECK constraint to a column:

alter table Stk add check (Amt is not null)

alter table Stk add constraint AmtNotNull check (Amt is not null)

The second form is preferred because it gives you an easy handle to drop the check, but the constraints themselves function exactly the same. As you might have expected, existing NULLs in the column will remain, can be backed up and restored, etc. etc. – see Adding a CHECKed column.

Making non-nullable columns nullable again

If you used a CHECK constraint to make the column non-nullable, you can simply drop it again:

alter table Stk drop constraint AmtNotNull

If you haven't named the constraint yourself but added the CHECK directly to the column or table, you must first find out its name before you can drop it. This can be done with the isql “SHOW TABLE” command (in this case: SHOW TABLE STK).

In the case of a NOT NULL constraint, if you know its name you can just drop it:

alter table Stk drop constraint NN_Amt

If you don't know the name you can try isql's “SHOW TABLE” again, but this time it will only show the constraint name if it is user-defined. If the name was generated by the engine, you have to use this SQL to dig it up:

select rc.rdb$constraint_name
from   rdb$relation_constraints rc
       join rdb$check_constraints cc
       on rc.rdb$constraint_name = cc.rdb$constraint_name
where  rc.rdb$constraint_type   = 'NOT NULL'
       and rc.rdb$relation_name = '<TableName>'
       and cc.rdb$trigger_name  = '<FieldName>'

Don't break your head over some of the table and field names in this statement; they are illogical but correct. Make sure to uppercase the names of your table and field if they were defined case-insensitively. Otherwise, match the case exactly.

If the NOT NULL constraint came with a domain, you can also remove it by changing the column type to a nullable domain or built-in datatype:

alter table Stk alter Amt type int

Any concealed NULLs, if present, will now become visible again.

No matter how you removed the NOT NULL constraint, commit your work and close all connections to the database. After that, you can reconnect and insert NULLs in the column.