Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

NOT NULL constraint is not synchronized after rename column [CORE5896] #6154

Closed
firebird-issue-importer opened this issue Aug 20, 2018 · 7 comments

Comments

@firebird-issue-importer

Submitted by: Van den Wouwer Danny (dannyvdw)

CREATE TABLE TEST (
ID BIGINT NOT NULL
);
alter table TEST
add constraint PK_TEST
primary key (ID);
COMMIT WORK;

-- 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);

COMMIT WORK;
// end of script

// Check rdb$check_constraints
select cc.rdb$constraint_name, rc.rdb$relation_name, cc.rdb$trigger_name
from rdb$check_constraints cc
join rdb$relation_constraints rc on cc.rdb$constraint_name = rc.rdb$constraint_name
left join rdb$relation_fields rf
on rc.rdb$relation_name = rf.rdb$relation_name
and cc.rdb$trigger_name = rf.rdb$field_name
where rc.rdb$constraint_type = 'NOT NULL';

Is this a wrong interpretation from me? I didn't suspected that ID is still present in de rdb$check_constraints system-table present AFTER changing the name to ID2?

I'm still not getting the rdb$relation_constraints either, especially the NOT NULL constraints, most of the time they aren't synced with the NOT NULL on the domain / fields.
After creating databases with a lot of tables and I run this query:

select cc.rdb$constraint_name , rc.rdb$relation_name, rf.rdb$field_name, rf.rdb$field_source
from rdb$check_constraints cc
join rdb$relation_constraints rc on cc.rdb$constraint_name = rc.rdb$constraint_name
left join rdb$relation_fields rf
on rc.rdb$relation_name = rf.rdb$relation_name
and cc.rdb$trigger_name = rf.rdb$field_name
where rc.rdb$constraint_type = 'NOT NULL'
and rf.rdb$field_name is null

I suspect to get no results, but most of the time I do, i dunno if those two phenomenons are related to each other.

Commits: 8f3000e

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 21, 2018

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.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 21, 2018

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.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 24, 2018

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 24, 2018

Modified by: @asfernandes

summary: NOT NULL constraint are not synchronized after using some DML's => NOT NULL constraint is not synchronized after rename column

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 24, 2018

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 4.0 Beta 1 [ 10750 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 26, 2018

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 26, 2018

Commented by: @asfernandes

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment