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 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 `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!

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.

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

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 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 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.

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.

Using domains to add a non-nullable field

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

create domain inn as int not null;
alter table Adventures add id inn;

Or like this:

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

For the presence of NULLs in the added columns, 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. The only difference is that domain-based constraints can't be removed at the column level. So if you ever want to drop the constraint later, you must either switch the column to another domain or built-in type again, or remove the constraint from the entire domain. The latter operation is described in the section Changing the nullability of a domain.

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 most Firebird clients will report them as 0 to the user. 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.

Warning

Some Firebird tools allow you to make an existing column NOT NULL with the click of a button. They do this by poking a value directly into a system table. This technique is neither recommended nor supported by Firebird, and although until now it works in practice, this may not be the case in future versions. It's better to stay safe and use the SQL given above.

Adding a CHECK constraint to an existing column

To add a CHECK constraint to a column, use one of the following syntaxes:

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 may 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

Removing a NOT NULL constraint

If you gave the NOT NULL constraint a name when you created it, you can simply drop it:

alter table Adventures drop constraint IdNotNull

If you forgot the name, you can retrieve it with isql's SHOW TABLE command (i.c. SHOW TABLE ADVENTURES); other clients may have their own provisions to let you find or browse constraint names.

If you didn't name the constraint explicitly, Firebird has created a name for it, but SHOW TABLE won't display it. You have to use this piece of 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 but don't enclose the names in double-quotes like you would do in a regular query. Also don't include the angle brackets (<>). Once you have the constraint name, you can drop it just like in the previous example.

Tip

If the above statement returns an empty set and you are sure that you've correctly filled in the table and field names (including case!), and the constraint did not come from a domain either (this is discussed in the next sections), it may be that a third-party tool has made the column NOT NULL by setting a flag in a system table. In that case it's probably best to remove it again with the same tool. If that is not an option, check the field's NULL flag with:

select rdb$null_flag from rdb$relation_fields
where  rdb$relation_name  = '<TableName>'
       and rdb$field_name = '<FieldName>'

If the flag is NULL or 0, the field is nullable (at least as far as this flag is concerned). If it's 1, clear it with:

update rdb$relation_fields set rdb$null_flag = null    /* or 0 */
where  rdb$relation_name  = '<TableName>'
       and rdb$field_name = '<FieldName>'

followed by a commit.

Important

As soon as you've dropped the NOT NULL constraint – by whichever method – SHOW TABLE will report the column as nullable. Any existing NULLs that were previously illegal and therefore hidden by most clients (see False reporting of NULLs as zeroes) will become visible again.

However, before you can insert NULLs into the column, you must commit your work, close all connections to the database, and reconnect.

Removing a domain-based NOT NULL constraint

If the NOT NULL constraint came with a domain, it is not registered directly with the column. This means you can't DROP it from the column either. Instead, change the column's type to a nullable domain or built-in data type:

alter table MyTable alter MyColumn type int

Even though this time the constraint was not tied directly to the column, you must again close all connections and reconnect before NULL input is accepted.

Removing a CHECK constraint

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). Unlike NOT NULL constraints, CHECKs will also be shown if they were created without a user-defined name.

Note

Dropping a column-based CHECK constraint takes effect immediately. You don't have to disconnect and reconnect to be able to insert values that would have violated the check.

Removing a domain-based CHECK constraint

You can't disable a domain-based CHECK constraint on the column level (you can add an extra CHECK, but the one from the domain will stay in effect as well). So unless you want to remove the CHECK from the entire domain, you'll have to change the column's data type to a domain or built-in type that allows NULLs, e.g.:

alter table Stk alter Amt type bigint