Regression (3.0 and 4.0): 'ALTER TABLE DROP CONSTRAINT <C_F01>, DROP <F01>' can
not be done when <C_F01> has reference to column <F01>
--------------------------------------------------------------------------------------------------------------------------------------
Key: CORE-5446
URL: http://tracker.firebirdsql.org/browse/CORE-5446
Project: Firebird Core
Issue Type: Bug
Affects Versions: 3.0.1, 4.0 Initial
Reporter: Pavel Zotov
Run following script (of course, one need to replace
"C:\FBTESTING\OLTP-EMUL\src\droptest.fdb" with appropriate string):
===
shell del C:\FBTESTING\OLTP-EMUL\src\droptest.fdb 2>nul;
create database 'localhost:C:\FBTESTING\OLTP-EMUL\src\droptest.fdb';
show version;
set echo on;
----------------------------- 1 ------------------------------
recreate table test(abc int not null);
alter table test
add foo int,
add constraint pkey_for_abc primary key(abc) using index pkey_for_abc
;
alter table test
drop constraint pkey_for_abc
,drop foo
;
commit;
----------------------------- 2 -----------------------------
recreate table test(abc int);
alter table test
add foo int not null,
add constraint pkey_for_foo primary key(foo) using index pkey_for_foo
;
commit;
connect 'localhost:C:\FBTESTING\OLTP-EMUL\src\droptest.fdb';
alter table test
drop constraint pkey_for_foo
,drop foo
;
commit;
----------------------------- 3 ------------------------------
recreate table test(abc int);
alter table test
add foo int not null,
add constraint check_for_foo check(foo>0)
;
commit;
connect 'localhost:C:\FBTESTING\OLTP-EMUL\src\droptest.fdb';
alter table test
drop constraint check_for_foo
,drop foo
;
commit;
----------------------------- 4 ---------------------------------
recreate table test(abc int);
alter table test
add foo int not null,
add bar int,
add constraint pkey_for_foo primary key(foo) using index pkey_for_foo,
add constraint fkey_for_bar foreign key(bar) references test(foo)
;
commit;
connect 'localhost:C:\FBTESTING\OLTP-EMUL\src\droptest.fdb';
alter table test
drop constraint fkey_for_bar
,drop constraint pkey_for_foo
;
commit;
----------------------------- 5 -----------------------------------
recreate table test(abc int);
alter table test
add foo int not null,
add bar int,
add constraint pkey_for_foo primary key(foo) using index pkey_for_foo,
add constraint fkey_for_bar foreign key(bar) references test(foo)
;
commit;
connect 'localhost:C:\FBTESTING\OLTP-EMUL\src\droptest.fdb';
alter table test
drop constraint fkey_for_bar
,drop constraint pkey_for_foo
,drop bar
;
commit;
----------------------------- 6 ----------------------------------
recreate table test(abc int);
alter table test
add foo int not null,
add bar int,
add constraint pkey_for_foo primary key(foo) using index pkey_for_foo,
add constraint fkey_for_bar foreign key(bar) references test(foo)
;
commit;
connect 'localhost:C:\FBTESTING\OLTP-EMUL\src\droptest.fdb';
alter table test
drop constraint fkey_for_bar
,drop constraint pkey_for_foo
,drop foo
;
commit;
===
On 2.5.7.27038 no errors will be.
On 3.0.2.32664 and 4.0.0.494 all 'ALTER TABLE' statements with dropping
constraint which has reference to the field which is ALSO dropped in the same
statement (after constraint) will FAIL.
For example, test #2 will raise:
Statement failed, SQLSTATE = 42000
unsuccessful metadata update
-ALTER TABLE TEST failed
-CONSTRAINT PKEY_FOR_FOO does not exist.
Test #6 will issue:
Statement failed, SQLSTATE = 27000
unsuccessful metadata update
-ALTER TABLE TEST failed
-action cancelled by trigger (1) to preserve data integrity
-Cannot delete PRIMARY KEY being used in FOREIGN KEY definition.
-At trigger 'RDB$TRIGGER_23'
So, one need to 'split' drop such statements (make them run separately), i.e.:
===
alter table test
drop constraint fkey_for_bar
;
alter table test
drop constraint pkey_for_foo
;
alter table test
drop foo
;
===
--
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
------------------------------------------------------------------------------
Developer Access Program for Intel Xeon Phi Processors
Access to Intel Xeon Phi processor-based developer platforms.
With one year of Intel Parallel Studio XE.
Training and support from Colfax.
Order your platform today. http://sdm.link/xeonphi
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
