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“.
Let us model our database tables.
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).
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
Through the ease and usefulness of SQL Server Management Studio (SSMS) built-in Database Diagram tooling, here is our logical model.
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.
/* exec sp_help sp_fkeys */ exec sp_fkeys @pktable_name = 'student' , @pktable_owner = 'deferred'
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“.
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.
Let us quickly see how other Vendors implement the deferrable concept.
In the example below, the def_bug table is created and it’s primary key is created as deferred initially.
create table def_bug ( n number primary key deferrable initially deferred ) ;
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
In this second example, we change the specified constraints to Deferred.
SET CONSTRAINTS emp_job_nn, emp_salary_min DEFERRED;
- To be able to successfully set constraint to deferred or immediate, one would have to initially define the constraint as Deferrable
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:
- Expensive Database Engine Code
- Operational Processing more resource uptake
- Use cases are likely more complex and esoteric
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.
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.
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.
At heart, Database Systems are state machines. The longer one needs to maintain states and hold back adjudicating, the more complex the enterprise is.
Picking up crumbs here; Courtesy of Google, Stackoverflow, and LBushkin & Mirko Klemm.
MSFT likes standards and overarching engineering goals, but like Sam Smith’s interest “Not in that way”
- Deferrable Constraints in SQL Server
Transact SQL – Batches
- Transact SQL – Batches
Oracle/Deferred Constraints – Documentation
Oracle/Deferred Constraints – Blog/QA
- Constraints – Don’t make them DEFERRABLE or NOVALIDATE unless you need to
- Not Deferrable Versus Deferrable Initial Immediate
- DEFERRABLE CONSTRAINTS IN ORACLE 11GR2 MAY LEAD TO CORRUPTED DATA
by Alex FatkulinMarch 15, 2010