Commented by: @mrotteveel

Please add a description in words of the problem. Having to execute your reproduction script and then interpret the results to understand what is going on makes it harder to understand your ticket at a glance. Especially as I may not have the same interpretation as you on the results.

Commented by: Van den Wouwer Danny (dannyvdw)

Mark,

I will try give it a go.

Internally Firebird tracks the relation constraints into the table rdb$relation_constraints.
I discovered while working on my https://github.com/cincuranet/FirebirdDbComparer project that this data was not in sync, especially the NOT_NULL constraint.
I found some constraints that pointed to a none-existing field. The search was on, how did this happen?
After searching some time I discovered that when you change the name of the field, the system tables aren't reflecting anymore the real situation.

And the scripts above are an help to see this situation.
After executing these DML statements:

CREATE TABLE TEST (
ID BIGINT NOT NULL
);

alter table TEST
add constraint PK_TEST
primary key (ID);

You will see this situation in the system tables (the generated names can be different INTEG_xxxx):

1. RDB$RELATION_CONSTRAINTS:

RDB$CONSTRAINT_NAME RDB$CONSTRAINT_TYPE RDB$RELATION_NAME
-------------------------------------- ------------------------------------ ---------------------------------
INTEG_1 NOT NULL TEST
PK_TEST PRIMARY KEY TEST

2. RDB$CHECK_CONSTRAINTS

RDB$CONSTRAINT_NAME RDB$TRIGGER_NAME
-------------------------------------- --------------------------------
INTEG_1 ID

All seems correct !

THEN do the follow thing:
- Rename column from ID to ID2
ALTER TABLE TEST DROP CONSTRAINT PK_TEST;
ALTER TABLE TEST ALTER ID TO ID2;
alter table TEST
add constraint PK_TEST
primary key (ID2);

And again we will the content of the system tables:
1. RDB$RELATION_CONSTRAINTS:

RDB$CONSTRAINT_NAME RDB$CONSTRAINT_TYPE RDB$RELATION_NAME
-------------------------------------- ------------------------------------ ---------------------------------
INTEG_1 NOT NULL TEST
PK_TEST PRIMARY KEY TEST

2. RDB$CHECK_CONSTRAINTS

RDB$CONSTRAINT_NAME RDB$TRIGGER_NAME
-------------------------------------- --------------------------------
INTEG_1 ID

And we see now in RDB$CHECK_CONSTRAINTS a fieldname that is non-existing, nl ID.

I see that this system table isn't updated when using altering statements. And it was this that I saw when testing my comparer with some databases from customers I work for, the comparer reported this problem.

Commented by: @asfernandes

I think it's better if you check RDB$RELATION_CONSTRAINTS.RDB$NULL_FLAG instead.