can not delete NOT NULL constraint on field which is included also in UNIQUE
----------------------------------------------------------------------------
Key: CORE-3692
URL: http://tracker.firebirdsql.org/browse/CORE-3692
Project: Firebird Core
Issue Type: Bug
Affects Versions: 2.5.1
Reporter: Pavel Zotov
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.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Cloud Services Checklist: Pricing and Packaging Optimization
This white paper is intended to serve as a reference, checklist and point of
discussion for anyone considering optimizing the pricing and packaging model
of a cloud services business. Read Now!
http://www.accelacomm.com/jaw/sfnl/114/51491232/
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
