Cannot drop a NOT NULL constraint on a field participating in the UNIQUE constraint [CORE3692]

[firebird-automations]

Submitted by: @pavel-zotov

Is related to QA493

STEP 1. Create empty database (mandatory for this test):
isql -q
SQL> create database 'tcfg.fdb'; commit;
SQL> recreate table cset (cname varchar(250) not null); commit;
SQL> SQL> alter table cset add constraint uq_cset unique (cname); commit;

STEP 2. Then run query that retrieves the names of all constraints for table 'CSET' except primary key and not_null`s that are linked with PK-constraint:

with
inp as(select 'cset' nm from rdb$database)
,pk_defs as( -- obtain PK constraint and get fields that assembles it
select
rc.rdb$relation_name rel_name
,rc.rdb$constraint_name pk_name
,rc.rdb$index_name pk_idx
,rs.rdb$field_name fld_name
,rs.rdb$field_position fld_pos
from rdb$relation_constraints rc
join rdb$index_segments rs on rc.rdb$index_name=rs.rdb$index_name
join inp i on rc.rdb$relation_name containing i.nm
where rc.rdb$constraint_type containing 'PRIMARY'
)
-- select * from pk_defs
,chk_list as(
select
rc.rdb$relation_name rel_name
,rc.rdb$constraint_name sub_name
,rc.rdb$constraint_type sub_type
,'alter table '||trim(rc.rdb$relation_name)||' drop constraint '||trim(rc.rdb$constraint_name)||'; -- '||trim(rc.rdb$constraint_type) stt
,ck.rdb$trigger_name
,p.pk_name -- not null ==> field is included in PK, skip it
,decode(rc.rdb$constraint_type, 'UNIQUE', 99, 0) sort_weitgh
from rdb$relation_constraints rc
join inp i on rc.rdb$relation_name containing i.nm
left join rdb$check_constraints ck on rc.rdb$constraint_name=ck.rdb$constraint_name
left join pk_defs p on rc.rdb$relation_name=p.rel_name and ck.rdb$trigger_name=p.fld_name
where
rc.rdb$relation_name not like 'RDB$%'
and rc.rdb$relation_name not like 'MON$%'
and rc.rdb$relation_name not like 'IBE$%'
and rc.rdb$constraint_type not containing 'PRIMARY'
and p.pk_name is null -- ==> this field is NOT included in PK constraint
order by rc.rdb$relation_name, decode(rc.rdb$constraint_type, 'UNIQUE', 99, 0)
)
select cast(stt as varchar(70)) stt from chk_list;

Result of query:

STT

alter table CSET drop constraint INTEG_1; -- NOT NULL
alter table CSET drop constraint UQ_CSET; -- UNIQUE

STEP 3. Copy to clipboard first row ("alter table CSET drop constraint INTEG_1;") and paste it in isql prompt as command to delete not null for UNIQUE constraint (this is ALLOWED by SQL standard):

SQL> alter table CSET drop constraint INTEG_1;
Statement failed, SQLSTATE = 42000
unsuccessful metadata update
-ERASE RDB$RELATION_CONSTRAINTS failed
-action cancelled by trigger (2) to preserve data integrity
-Column used in a PRIMARY constraint must be NOT NULL.

We get error message though there is NO any primary key in the table - only UNIQUE.

Commits: e304d6d 21c71a0

[firebird-automations]

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

[firebird-automations]

Modified by: @dyemanov

status: Open [ 1 ] => In Progress [ 3 ]

[firebird-automations]

Modified by: @dyemanov

Version: 2.1.4 [ 10361 ]

Version: 2.5.0 [ 10221 ]

Version: 2.0.6 [ 10303 ]

Version: 3.0 Initial [ 10301 ]

Version: 2.1.3 [ 10302 ]

Version: 2.1.2 [ 10270 ]

Version: 2.0.5 [ 10222 ]

Version: 2.1.1 [ 10223 ]

Version: 2.1.0 [ 10041 ]

Component: Engine [ 10000 ]

[firebird-automations]

Modified by: @dyemanov

summary: can not delete NOT NULL constraint on field which is included also in UNIQUE => Cannot drop a NOT NULL constraint on a field participating in the UNIQUE constraint

[firebird-automations]

Modified by: @dyemanov

status: In Progress [ 3 ] => Open [ 1 ]

[firebird-automations]

Commented by: @dyemanov

A database must be restored from a backup or recreated from a script in order to benefit from this bug fix.

[firebird-automations]

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

Fix Version: 2.5.2 [ 10450 ]

[firebird-automations]

Modified by: @pcisar

Link: This issue is related to QA493 [ QA493 ]

[firebird-automations]

Modified by: @pavel-zotov

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

QA Status: Done successfully

[firebird-automations]

Commented by: @pcisar

Test created.

[firebird-automations]

Modified by: @pcisar

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