Subject RE: Primary keys not declared as NOT NULL
Author Maya Opperman
>I have a Firebird 1.5 database, which somehow does not have NOT NULL ticked for the primary key field of a few tables.

>When I restore the database on Firebird 2.5, it just silently drops the primary keys.

>I have found and corrected 2, but suspect there may be more, which could cause some nasty headaches further down the line.

>Is there any way I can see which primary keys were problematic, and therefore dropped?

An update here, I seem to have come right. This is what I did:

I suspect this problem stems from a data corruption they experienced back in 2009, which was fixed by someone, not sure who or how. Strangely enough they have been working quite happily on FB 1.5 since then.

Most of the records in rdb$relation_fields have a NULL value in the ff.rdb$null_flag field.

The follow SQL will update it to 1, if it is a primary key. Removing the comment and the update part will give you a list of all the dodgy primary keys.

update rdb$relation_fields ff set ff.rdb$null_flag = 1 where ff.rdb$field_source in (

--c.rdb$index_name, c.rdb$relation_name, i.rdb$field_name, f.rdb$null_flag,

from rdb$relation_constraints c

left outer join rdb$index_segments i on i.rdb$index_name = c.rdb$index_name

left outer join rdb$relation_fields f on f.rdb$field_name = i.rdb$field_name and f.rdb$relation_name = c.rdb$relation_name

where c.rdb$constraint_type = 'PRIMARY KEY'
and f.rdb$null_flag is null )

Just thought I'd let you know in case someone else experiences the same problem changing from Firebird 1.5 to 2.5