db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6668) Truncating a table may silently violate a deferred foreign key.
Date Wed, 16 Jul 2014 20:11:06 GMT

    [ https://issues.apache.org/jira/browse/DERBY-6668?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14064013#comment-14064013

Knut Anders Hatlen commented on DERBY-6668:

If I remove "initially deferred" from the fk definition in the repro script, TRUNCATE TABLE
fails with this error:

ERROR XCL48: TRUNCATE TABLE is not permitted on 'TUNIQUE' because unique/primary key constraints
on this table are referenced by enabled foreign key constraints from other tables.

We could raise the same error in the deferred case, I guess.

(Note that we are slightly more liberal than the standard, as we only disallow TRUNCATE TABLE
when referenced from other tables. Self-referencing foreign keys are accepted. This deviation
doesn't cause any integrity issues, though, as far as I can tell.)

> Truncating a table may silently violate a deferred foreign key.
> ---------------------------------------------------------------
>                 Key: DERBY-6668
>                 URL: https://issues.apache.org/jira/browse/DERBY-6668
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions:
>            Reporter: Rick Hillegas
> 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:
> {noformat}
> 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;
> {noformat}

This message was sent by Atlassian JIRA

View raw message