|Firebird Docset → Firebird Database Docs → Firebird Null Guide → Altering populated tables|
If your table already contains data, and you want to add a non-nullable column or change the nullability of an existing column, there are some consequences that you should know about. We'll discuss the various possibilities in the sections below.
Suppose you have this table:
Table 8. Adventures table
|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.
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.
Firebird 1.5 (but not 1.0 or 2.0) even allows you to make such a column the primary key!
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
|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.
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.
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:
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.
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.
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
|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.
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.
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.
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.
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.
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).
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.
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.
|Firebird Docset → Firebird Database Docs → Firebird Null Guide → Altering populated tables|