Issue Details (XML | Word | Printable)

Key: CORE-3692
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Pavel Zotov
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Firebird Core

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

Created: 06/Dec/11 12:37 PM   Updated: 27/Mar/14 01:16 PM
Component/s: Engine
Affects Version/s: 2.1.0, 2.1.1, 2.0.5, 2.1.2, 2.1.3, 3.0 Initial, 2.0.6, 2.5.0, 2.1.4, 2.5.1
Fix Version/s: 2.5.2, 3.0 Alpha 1

Time Tracking:
Not Specified

Issue Links:
Relate
 

Planning Status: Unspecified


 Description  « Hide
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.

 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 06/Dec/11 01:13 PM
A database must be restored from a backup or recreated from a script in order to benefit from this bug fix.