PK constraint should be prevented from dropping if there is procedure/trigger which uses it in UPDATE OR INSERT statement (otherwise such SP will work incorrect) -----------------------------------------------------------------------------------------------------------------------------------------------------------------
Key: CORE-5212 URL: http://tracker.firebirdsql.org/browse/CORE-5212 Project: Firebird Core Issue Type: Improvement Components: Engine Affects Versions: 3.0.0, 4.0 Initial, 2.5.5 Reporter: Pavel Zotov Consider script: === set echo on; create or alter procedure sp_test as begin end; recreate table test( u int not null, v int not null, w int not null ); alter table test add constraint test_pk primary key( u ); commit; set term ^; create or alter procedure sp_test(a_u int, a_v int, a_w int) as begin update or insert into test( u, v, w ) values ( :a_u, :a_v, :a_w ); end ^ set term ;^ commit; execute procedure sp_test( 1, 10, 20); execute procedure sp_test( 2, 20, 40); execute procedure sp_test( 1, 11, 22); select * from test; delete from test; commit; alter table test drop constraint test_pk; commit; -- ////////////////////////////////////////////////////// alter table test add constraint test_pk primary key( v ); -- \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\ commit; show table test; /* set term ^; create or alter procedure sp_test(a_u int, a_v int, a_w int) as begin update or insert into test( u, v, w ) values ( :a_u, :a_v, :a_w ); end ^ set term ;^ commit; */ execute procedure sp_test( 1, 10, 20); execute procedure sp_test( 2, 20, 40); execute procedure sp_test( 1, 11, 22); select * from test; === If you will NOT make recreation of SP_TEST after dropping PK (and assign it to another column), this SP will work wrong: it seems like OLD PK definition is stored somewhere. With __NEW__ primary key (column'V') last three statements will produce content of table TEST like with __OLD__ PK = 'U': === execute procedure sp_test( 1, 10, 20); execute procedure sp_test( 2, 20, 40); execute procedure sp_test( 1, 11, 22); select * from test; U V W ============ ============ ============ 1 11 22 2 20 40 === So, one need to empty bodies of all such procedures / triggers before this PK will be dropped, and later to fill them again + recompile. It will be great if this job could be done in the single transaction by engine itself. -- 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 ------------------------------------------------------------------------------ Find and fix application performance issues faster with Applications Manager Applications Manager provides deep performance insights into multiple tiers of your business applications. It resolves application problems quickly and reduces your MTTR. Get your free trial! https://ad.doubleclick.net/ddm/clk/302982198;130105516;z Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel