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