[DERBY-532] Support deferrable constraints

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 10.11.1.1
• Component/s: SQL
• Labels: derby_triage10_11
• Urgency: Normal
• Assignee: Dag H. Wanvik
• Reporter: Jörg von Frantzius
• Created: 24/Aug/05 01:18
• Updated: 09/Sep/16 09:52
• Resolved: 05/Jun/14 22:01

Description

In many situations it is desirable to have constraints checking taking place only at transaction commit time, and not before. If e.g. there is a chain of foreign key constraints between tables, insert statements have to be ordered to avoid constraint violations. If foreign key references are circular, the DML has to be split into insert statements and subsequent update statements by the user.

In other words, with deferred constraints checking, life is much easier for the user. Also it can create problems with softwares such as object-relational mapping tools that are not prepared for statement ordering and thus depend on deferred constraints checking.

Issue Links

• incorporates: DERBY-6374 Bulk insert of data with nullable UNIQUE constraint fails to detect duplicates
• is broken by: DERBY-6559 A immediate Fk constraint blows up iff its referenced PK is deferred and we delete a duplicate
• is broken by: DERBY-6576 A immediate Fk constraint blows up iff its referenced PK is deferred and we modify a duplicate key column
• is depended upon by: DERBY-6571 Document deferrable constraints
• is related to: DERBY-6423 The expression syntax in CASE's THEN clause doesn't accept boolean value expression
• is related to: DERBY-6661 dblook does not recognize the deferrability of deferrable constraints
• is related to: DERBY-6668 Truncating a table may silently violate a deferred foreign key.
• is related to: DERBY-6670 Rollback to savepoint allows violation of deferrable constraints
• is related to: DERBY-6672 Allow Derby to rename tables referenced by foreign keys
• is related to: DERBY-6674 Cleanup brittle code in ValidateCheckConstraintResultSet
• is related to: DERBY-6453 Remove dead code in InsertResultSet and flag skipCheckConstraints
• relates to: DERBY-6303 Add ability to defer enforcement of unique indexes until transaction end.

Sub-Tasks

• Make BTree scan honor OPENMODE_LOCK_NOWAIT for row locks.: Closed
• Document deferrable constraints: Closed
• Add privileges checking to SET CONSTRAINTS: Closed