Inflict versions of SQL Server support deferrable constraints (Electricity)?
Since about version 8., Oracle has supported deferrable constraints - constraints which are only examined whenever you commit an argument group, not whenever you place or update individual tables. Deferrable constraints vary from just crippling/enabling constraints, for the reason that the restrictions continue to be active - they're just examined later (once the batch is committed).
The advantage of Electricity is they allow updates that individually could be illegal to become examined that cummulatively create a valid finish condition. A good example is creating circular references inside a table between two rows where each row takes a value to exist. No individual place statement would pass the constraint - however the group can.
To explain transpire, I'm searching to port an ORM implementation in C# to SQLServer - regrettably the implementation depends on Oracle Electricity to prevent computing place/update/remove orders among rows.
To date SQL Server doesn't support them. What's the problem you're fixing?
OT: You will find IMHO a number of things SQL Server doesn't support, but will make sense within an enterprise atmosphere:
- Deferrable constraints as pointed out here
- MARS: Just why must you set a choice for something entirely natural?
- CASCADE Remove constraints: SQL Server does only allow a single cascadation path for any given CASCADE Remove constraint. Again, I do not visit a reason it should not be permitted to cascade on deletion through multiple possible pathways: Ultimately, at that time it truly is performed, there'll always be just one path being really used, so why restriction?
- Protection against parallel transactions on one ADO.Internet connection.
- Forcing of each and every command performed on the connection which has a transaction to become performed in this particular transaction.
- When designing a distinctive index, NULL is treated as though it had been a real value, and permitted to look only one time within the index. SQL's perception of NULL being an "unknown value" would, however, indicate, that NULL values be overlooked altogether when designing the index...
All this make most of the referential integrity and transactional features you realized from the full-sized RDBMS nearly useless in SQL Server. For instance, since deferrable constraints aren't supported, the idea of the "transaction" being an externally consistent Unit Of Labor is partially negated, the only real viable solution - except fro some dirty workarounds - being not to define referential integrity constraints whatsoever. I'd expect, natural behavior of the transaction be that you could work within it in the manner and order of procedures you want, and also the system will make certain it's consistent at that time you commit it. Similar problems arise in the restriction, that the referential integrity constraint with ON Remove CASCADE may be defined in ways that just one single constraint can result in the cascaded deletion of the object. This really does not fit most real-world situations.
I discovered about five different blogs all saying SQLServer (in a variety of versions) doesn't support Deferrable Constraints.
However, I additionally found a publish which tries to mimic this feature by utilizing "persisted computed columns," (scroll towards the last entry), but caveat emptor
It may sound such as the problem you've is the fact that SQL doesn't support what Date and Darwen call 'multiple assignment'. Standard SQL's reaction to it was 'deferrable constraints', which SQL Server doesn't support. A SQL Server FK or CHECK constraint could be flagged with NOCHECK nevertheless its less than exactly the same. For additional particulars see MSDN: ALTER TABLE (Transact-SQL).
Are you currently essentially asking a variant of this question?