Deferrability Delayed

Background

As I studied the “Is not Trusted” setting on Foreign Keys, I found a setting that was very, very new to me.

The setting is “Deferrability“.

 

Model

Let us model our database tables.

DDL

We will create three tables; and to isolate them from other things going on in our Lab DB, we will place them in their own self contained schema (deferred).

 

Schema


if schema_id('deferred') is null
begin

exec('create schema [deferred]
          authorization [dbo]
     ');

end
go

 

Table – deferred.student


if object_id('[deferred].[student]') is null
begin

 create table [deferred].[student]
 (
   [studentID]	bigint not null
         identity(1,1)
  , [studentName] varchar(80) not null

  , constraint [PK_Deferred_Student]
      primary key
     (
	[studentID]
     )

     ,constraint[UQ_Deferred_StudentName] unique
      (
	[studentName]
      )
      with
      (
	IGNORE_DUP_KEY = ON
      )

 )
end
go

 

 

Table – deferred.course


if object_id('[deferred].[course]') is null
begin

  create table [deferred].[course]
  (

     [id]   bigint not null
         identity(1,1)
   , [courseID]	varchar(80) not null
   , [courseName]	varchar(80) not null

   , constraint [PK_Deferred_Course] primary key
	(
		[id]
	)

  , constraint [UQ_Deferred_CourseID] unique
    (
	[courseID]
    )
    with
    (
	IGNORE_DUP_KEY = ON
    )

  ,constraint [UQ_Deferred_CourseName] unique
    (
	[courseName]
    )
    with
    (
	IGNORE_DUP_KEY = ON
    )

 )

end
go

 

Table – deferred.studentCourse


--drop table [deferred].[studentCourse]
if object_id('[deferred].[studentCourse]') is null
begin

  create table [deferred].[studentCourse]
  (
      [studentID]		bigint not null
    , [courseID]		bigint not null

    , constraint [PK_Deferred_StudentCourse]
         primary key
        (
	    [studentID]
	  , [courseID]
	)
	with
	  (
	     IGNORE_DUP_KEY = ON
	  )

  ,constraint [FK_Deferred_StudentCourse_StudentID]
	foreign key
	(
   	   [studentID]
	)
	references [deferred].[student]
	(
	   [studentID]
	)

  , constraint [FK_Deferred_StudentCourse_CourseID]
	foreign key
	(
	   [courseID]
	)
	references [deferred].[course]
	(
	   [id]
	)

  )

end
go

 

Logical Model

Through the ease and usefulness of SQL Server Management Studio (SSMS) built-in Database Diagram tooling, here is our logical model.

modelLogical

 

Instrumentation

Review Foreign Keys

Via Scripting, let us review our Foreign Keys definition.

We will ask for foreign keys that have the student table has the parent.

Sample:


/*
     exec sp_help sp_fkeys
*/
exec sp_fkeys
	  @pktable_name = 'student'
	, @pktable_owner = 'deferred'

Output:

sp_fkeys

Explanation:

Everything looked OK and familiar.  Outside of the last column, Deferrability.

What is Deferrability?

Glad you asked.  Has I said, I had enough problems trying to get my constraints trusted.

As an aside, the reason is that they were earmarked as “Is Not For Replication“.

Stack Overflow

So back to the question, what is deferrability.

The best Q&A came from two good old boys on Stack Overflow:

LBushkin ( Questionnaire )

In 2009, LBushkin asked the question pasted below:

Do any versions of SQL Server support deferrable constraints (DC)?

Since about version 8.0, Oracle has supported deferrable constraints that are only evaluated when you commit a statement group, not when you insert or update individual tables. Deferrable constraints differ from just disabling/enabling constraints, in that the constraints are still active – they are just evaluated later (when the batch is committed).

The benefit of DC is that they allow updates that individually would be illegal to be evaluated that cummulatively result in a valid end state. An example is creating circular references in a table between two rows where each row requires a value to exist. No individual insert statement would pass the constraint – but the group can.

To clarify my goal, I am looking to port an ORM implementation in C# to SQLServer – unfortunately the implementation relies on Oracle DC to avoid computing insert/update/delete orders amongst rows.

 

Mirko Klemm ( Answer )

OT: There are IMHO quite a few things SQL Server does not support, but would make sense in an enterprise environment:

  • Deferrable constraints as mentioned here
  • MARS: Just why do you need to set an option for something entirely natural?
  • CASCADE DELETE constraints: SQL Server does only allow one single cascadation path for a given CASCADE DELETE constraint. Again, I don’t see a reason why it shouldn’t be allowed to cascade on deletion through multiple possible paths: In the end, at the time it really is executed, there will always be only one path being actually used, so why is this restriction?
  • Prevention of parallel transactions on a single ADO.NET connection.
  • Forcing of every command executed on a connection that has a transaction to be executed within this transaction.
  • When creating a UNIQUE index, NULL is treated as if it was an actual value, and allowed to appear only once in the index. SQL’s notion of NULL as an “unknown value” would, however, indicate, that NULL values be ignored altogether when creating the index…

All these little things make many of the referential integrity and transactional features you would expect from a full-sized RDBMS nearly useless in SQL Server. For example, since deferrable constraints are not supported, the notion of a “transaction” as an externally consistent Unit Of Work is partly negated, the only viable solution – except from some dirty workarounds – being to not define referential integrity constraints at all. I would expect, the natural behavior of a transaction be that you can work inside it in the way and order of operations you like, and the system will make sure it is consistent at the time you commit it.

Similar problems arise from the restriction, that a referential integrity constraint with ON DELETE CASCADE may only be defined in a way that only one single constraint can lead to the cascaded deletion of an object. This really doesn’t fit most real-world scenarios.

 

Other DBMS

Let us quickly see how other Vendors implement the deferrable concept.

Oracle

Constraint Creation

In the example below, the def_bug table is created and it’s primary key is created as deferred initially.

Sample

create table def_bug
(
     n number
         primary key
            deferrable initially deferred
)
;

Explanation

The other options are:

  • Not Deferrable
    • The default option is not to include the “Deferrable option” at all
    • Not Deferrable can also be explicitly stated as “Not Deferrable
  • Deferrable Immediate
    • Which states that we will like for the constraints to be initially checked at the statement level, but we reserve the right to issue a change.  And, of course the change will be checks to be performed at transaction commit

 

Constraint Deferred

In this second example, we change the specified constraints to Deferred.

Sample

SET CONSTRAINTS emp_job_nn, emp_salary_min DEFERRED;

Explanation

Quick recap

  • To be able to successfully set constraint to deferred or immediate, one would have to initially define the constraint as Deferrable

 

SQL Server

For those that have worked with SQL Server for a while, its implementation is straightforward as it meets a general consciousness of how Transact SQL works.

Statement Level Transaction Container

By default, each SQL Statement is acted and committed individually.

One can use the “Go Statement” to logically separate submission batches, but still each statement is executed on its own.

 

Voting on Previous Successes\Failures can not be altered

Here is Microsoft’s Official Documentation:

  • The statements that are executed before the statement that encountered the run-time error are not affected.
  • Most run-time errors stop the current statement and the statements that follow it in the batch.
  • Some run-time errors, such as constraint violations, stop only the current statement.  All the remaining statements in the batch are executed.

In essence, each statement is accounted as successful or failed before SQL marks it as completed.

To properly implement “Deferrable Constraints“, voting will have to be quiesced until the transaction is committed.

Transaction Isolation Level

Operating at more stringent Transaction levels, such as Repeatable Read and Serializable, subsequent statements can be down voted if they negate previous successes.

But, keep in mind, that these settings only affect the current and subsequent statements, but not past successes.

 

Why not Deferrability?

There are quite a few sound technical reasons why an astute Database Engine architect might hold back from committing technical resources to the Deferrability Offering.

The reasons include:

  1. Throughput
  2. Expensive Database Engine Code
  3. Operational Processing more resource uptake
  4. Use cases are likely more complex and esoteric

 

Throughput

With advent of NoSQL, World moving more and more towards “Eventual Consistency“.

Depending on the type of data being managed, more distributed systems can exchange 100% acidity for higher throughput.

 

Database Engine Code

To safely introduce Deferrability, the underline database engine’s code is expectedly more expensive.

Bugs in edge use-cases will likely remain and manifest through several Application patches.

 

Operational Expense

The Execution Plan will likely be more more complex.  And, even in cases, where not translucent in “Estimated Execution Plan“, actual executing code will likely use more locks & latches to safeguard results.

 

Use Cases

Since SQL 2000, Cascaded updates and deletes have being available.

And, so in terms of Application’s Code Flow, there is some help that has been available for a while.

Having said that when using more higher level Application Development or Object Relation Mapping ( ORM ) tools , one might have to write more stub code to ensure parent data is available before posting data to dependent tables.

Summary

At heart, Database Systems are state machines.  The longer one needs to maintain states and hold back adjudicating, the more complex the enterprise is.

 

Dedicated

Picking up crumbs here; Courtesy of Google, Stackoverflow, and LBushkin & Mirko Klemm.

 

Listening

MSFT likes standards and overarching engineering goals, but like Sam Smith’s interest “Not in that way

 

References

Deferrable Constraints

  1. Deferrable Constraints in SQL Server
    http://stackoverflow.com/questions/998095/deferrable-constraints-in-sql-server


Transact SQL – Batches

  1. Transact SQL – Batches
    https://technet.microsoft.com/en-us/library/ms175502(v=sql.105).aspx

 

Oracle/Deferred Constraints – Documentation

  1. Set Constraints
    https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10003.htm

 

Oracle/Deferred Constraints – Blog/QA

  1. Constraints – Don’t make them DEFERRABLE or NOVALIDATE unless you need to
    https://richardfoote.wordpress.com/2007/12/14/constraints-dont-make-them-deferrable-or-novalidate-unless-you-need-to/
  2. Not Deferrable Versus Deferrable Initial Immediate
    http://stackoverflow.com/questions/5300307/not-deferrable-versus-deferrable-initially-immediate
  3. DEFERRABLE CONSTRAINTS IN ORACLE 11GR2 MAY LEAD TO CORRUPTED DATA
    by Alex FatkulinMarch 15, 2010
    http://www.pythian.com/blog/deferrable-constraints-in-oracle-11gr2-may-lead-to-logically-corrupted-data/

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s