[DERBY-6668] Truncating a table may silently violate a deferred foreign key.

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.11.1.1
• Fix Version/s: 10.11.1.1
• Component/s: SQL
• Labels: derby_backport_reject_10_10
• Urgency: Blocker
• Issue & fix info: Repro attached
• Bug behavior facts: Data corruption
• Assignee: Richard N. Hillegas
• Reporter: Richard N. Hillegas
• Created: 16/Jul/14 19:27
• Updated: 09/Sep/16 09:53
• Resolved: 17/Jul/14 12:42

Description

If you truncate a table which is referenced by a deferred foreign key, orphaned tuples are left in the foreign table. That is, the foreign key is violated but no exception is raised.

Since table truncation involves changing conglomerate ids, this may be another case of derby-6665. Or this may be a new bug.

The following script shows this behavior:

connect 'jdbc:derby:memory:db;create=true';

create table tunique
(
  a int not null unique
);

create table tref
(
  a int references tunique( a ) initially deferred
);

insert into tunique values ( 1 );
insert into tref values ( 1 );

truncate table tunique;

-- the unique table is empty
select * from tunique;

-- but the table which references it has a row
select * from tref;

Issue Links

• relates to: DERBY-6665 Violation of deferred constraints not detected when conglomerates are erroneously shared
• relates to: DERBY-532 Support deferrable constraints