I blogged here about a problem with SQL Server check constraints and Jamie Collins made a comment that Jet also suffers from the same problem.
He goes on to say:
"The main drawback for me, however, is that Jet (in common with SQL Server) lacks the S[Q]L-92 feature of deferrable constraints which I feel is a must have to make CHECK constraints workable."
I'm glad someone mentioned this because it gives me an opportunity to say why I disagree with it! It seems that ANSI/ISO SQL's concept of deferrable constraints is poor. I don't believe that concept is necessary to make constraints workable and I hope that support for it won't be added to SQL Server any time soon (but thank you for your comment Jamie).
In SQL terms, a deferred constraint isn't checked until a transaction is committed. If the constraint is not met at commit time then the transaction is rolled back. That means multiple statements in a transaction can violate deferred constraints without error and execution will continue as normal until the commit statement is reached. But the results of any offending statements are persisted in the database for at least the duration of the transaction and therefore may affect subsequent statements in the same transaction scope. In other words, within the transaction scope the database will appear to be in an inconsistent state. This can cause very similar problems to the one I already demonstrated in SQL Server. Both cases can give rise to incorrect results and in a sense the deferred constraint situation is more serious because it allows update DML statements to happen rather than just erroneously preventing a valid result.*
In the example that I referred to in my previous post no deferred constraint checking is necessary. All that is required is for SQL Server to check the constraint properly - i.e. at statement level rather than row level. PRIMARY KEY, FOREIGN KEY and UNIQUE constraints are already checked at statement level; CHECK constraints should be too.
The actual use-case for deferrable constraints is different. Deferrable constraints enable SQL to support transactions that require multiple statements in order to return the database to a consistent state. For example, tables A and B may be constrained to have the same number of rows:
CREATE ASSERTION foo
CHECK ( (SELECT COUNT(*) FROM a) = (SELECT COUNT(*) FROM b) );
How do you then insert rows to both tables, given that an INSERT statement can only target a single table at once?
What I would like to see is a better alternative to SQL's "deferred" syntax. I'd propose the option to have a transaction treated as a single unit of work, so that both the constraints and the DML take effect only at commit time - exactly as if the whole transaction were a single statement. The advantage is that the constraint need never be allowed to be violated. Example:
BEGIN DEFERRED TRAN;
PRINT 'a';
INSERT INTO a VALUES (1);
PRINT 'b';
INSERT INTO b VALUES (1);
COMMIT TRAN;
Result:
a
b
(2 row(s) affected)
Notice that using this hypothetical syntax, DEFERRED is a property of the transaction, not the constraint(s). For sure there are some obstacles to making this a reality but I still believe it is preferable to SQL's deferrable constraint model.
Another possible solution would be to add support for multiple assignment in SQL. The subject of just whether multiple assigment (or even single assignment!) is supportable within the SQL data model and syntax is worthy of a whole essay in itself. I'm not going to attempt it right now...
*Note: The above is influenced by Chris Date who examines this problem in his book, Database In Depth (in Chapter 6, "Why Database Constraint Checking Must Be Immediate"). He proposes a solution which is syntactically different to what I am suggesting but in my opinion the effect is the same.