db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-268) Add Support for truncate table
Date Wed, 08 Sep 2010 18:05:36 GMT

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

Rick Hillegas commented on DERBY-268:
-------------------------------------

Re-using the import/replace code will allow truncate to succeed in the following edge case,
which currently raises an error:

o The truncated table is referenced by a foreign key
o But there are no rows in the referencing table

It seems to me that the proposal may not be appropriate for an issue flagged for a newcomer.
I recommend one of the following:

o Create a new JIRA for the improvement
o Remove the "newcomer" flag and negotiate with Eranda about who will do the work

------------------------------

Here is a script which demonstrates the different edge-case behavior of the truncate and import/replace
code:

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

create table deltriggertest_t1(x int);
create table deltriggertest_t2(y int);
create trigger deltriggertest_tr after delete on deltriggertest_t1 referencing old as old
for each row insert into deltriggertest_t2 values old.x;

create table foreignkey_t1(x int primary key);
create table foreignkey_t2(y int references foreignkey_t1);

insert into deltriggertest_t1( x ) values ( 1 );

insert into foreignkey_t1 values 1,2;
insert into foreignkey_t2 values 2;


call syscs_util.syscs_export_table ( null, 'DELTRIGGERTEST_T1', 'z.dat', null, null, null
);

-- fails because of delete trigger
truncate table deltriggertest_t1;
-- fails because of foreign key
truncate table foreignkey_t1;

-- fails because of delete trigger
call syscs_util.syscs_import_table( null, 'DELTRIGGERTEST_T1', 'z.dat', null, null, null,1
);
-- fails because foreign key is not satisfied
call syscs_util.syscs_import_table( null, 'FOREIGNKEY_T1', 'z.dat', null, null, null,1 );

delete from foreignkey_t2;
delete from foreignkey_t1;
delete from deltriggertest_t1;

-- fails because of delete trigger
truncate table deltriggertest_t1;
-- fails because of foreign key
truncate table foreignkey_t1;

-- fails because of delete trigger
call syscs_util.syscs_import_table( null, 'DELTRIGGERTEST_T1', 'z.dat', null, null, null,1
);
-- succeeds
call syscs_util.syscs_import_table( null, 'FOREIGNKEY_T1', 'z.dat', null, null, null,1 );


> Add Support for truncate table
> ------------------------------
>
>                 Key: DERBY-268
>                 URL: https://issues.apache.org/jira/browse/DERBY-268
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>            Reporter: Lance Andersen
>            Assignee: Eranda Sooriyabandara
>            Priority: Minor
>         Attachments: derby-268-01-ab-enableForInsaneBuilds.diff, Derby-268.diff, tests.diff
>
>
> Adding support for truncate table command will aid to portability

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message