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

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

Closed
firebird-automations opened this issue Dec 6, 2011 · 11 comments

Comments

@firebird-automations
Copy link
Collaborator

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
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Modified by: @dyemanov

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

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA493 [ QA493 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Done successfully

@firebird-automations
Copy link
Collaborator Author

Commented by: @pcisar

Test created.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

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