[DERBY-6659] The Reference Guide should state how long a SET CONSTRAINTS command is good for

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.11.1.1
• Fix Version/s: 10.11.1.1
• Component/s: Documentation
• Urgency: Normal
• Assignee: Camilla Haase
• Reporter: Richard N. Hillegas
• Created: 11/Jul/14 17:56
• Updated: 09/Sep/16 09:53
• Resolved: 11/Jul/14 20:38

Description

A SET CONSTRAINTS command changes the state of a constraint only until the transaction ends (or another, overriding SET CONSTRAINTS command is issued). Once the transaction ends, the constraint reverts to the default behavior declared for it at CREATE/ALTER TABLE time. However, this behavior is not clear from the reference material on SET CONSTRAINTS: https://builds.apache.org/job/Derby-docs/lastSuccessfulBuild/artifact/trunk/out/ref/rrefsqljsetconstr.html

The following script shows this behavior:

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

autocommit off;

create table tprim
(
    keyCol  int primary key
);
create table tref
(
    refCol  int,
    constraint refcon foreign key( refCol ) references tprim( keyCol ) deferrable
);
insert into tprim( keyCol ) values ( 1 ), ( 2 ), ( 3 );
commit;

set constraints refcon deferred;

-- succeeds because we switched to deferred mode for this transaction
insert into tref( refCol ) values ( 4 );
rollback;

-- but fails the second time because the end of the previous transaction
-- reverted the refcon constraint to its default (immediate) mode
insert into tref( refCol ) values ( 4 );