Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Deferrable Constraints in SQL Server

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

Since about version 8.0, Oracle has supported deferrable constraints - 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.

like image 811
LBushkin Avatar asked Jun 15 '09 20:06

LBushkin


People also ask

What is a deferrable constraint?

If a constraint is deferrable, this clause specifies the default time to check the constraint. If the constraint is INITIALLY IMMEDIATE, it is checked after each statement, which is the default. If the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction.

What is deferrable SQL?

In SQL Server Enterprise, a corrupted transaction can become deferred if data required by rollback (undo) is offline during database startup. A deferred transaction is a transaction that is uncommitted when the roll forward phase finishes and that has encountered an error that prevents it from being rolled back.

What is the use of check constraint in SQL?

The CHECK constraint is used to limit the value range that can be placed in a column. If you define a CHECK constraint on a column it will allow only certain values for this column. If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

Which of the database constraints Cannot be composite?

A composite unique key cannot have more than 32 columns. You cannot designate the same column or combination of columns as both a primary key and a unique key. You cannot specify a unique key when creating a subview in an inheritance hierarchy. The unique key can be specified only for the top-level (root) view.


1 Answers

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 fro 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.

like image 190
Mirko Klemm Avatar answered Oct 04 '22 17:10

Mirko Klemm