Why don't DBMS's support ASSERTION

Asked 2011-06-16T07:14:59.823

My databases course professor noted that major databases don't support ASSERTION, even though it is in the SQL-92 standard.

Why isn't ASSERTION supported by the vast majority of relational database packages?

Things I have considered:

It would incur significant performance costs
It could be intrinsically hard, ie. the design requirements of SQL databases does not allow it

Researching this question raises some additional, tangential questions:

Are there any database packages that implement it? (possibly an academic/teaching DB)
Why is there so little public discussion? It's not even mentioned in the Wikipedia page for SQL or SQL-92).

sql rdbms database

Edit Report

2 Answers

Concurrency: If you take the "CHECK constraint using a scalar udf with table access" solution for SQL Server, it isn't safe. An assertion would probably be the same in Sybase/SQL Server type engines.

Alexander Kuznetsov: Scalar UDFs wrapped in CHECK constraints are very slow and may fail for multirow updates

Tony Rogerson: Using a UDF in a CHECK constraint to check validity of History Windows (Start - End Date Windows)
Performance: A 10k row insert would require 10k udf-style executions each with table access. Ouch. Given the ASSERTION is per row, this is how it has to operate. If it could work for "all rows in the INSERT", then it's simpler as a trigger.
Design: There are patterns (superkey, subtype tables etc) for maintaining data integrity on related tables using simpler constraints. If you need to check some random table for data integrity, you have something wrong.

answered 2011-06-16T07:39:07.383

Yeah, I asked this question to my instructor in the class a few months ago. It may be in standards but many DBMS vendors do not obey standards at all.

Probably, the reason behind DBMSes to not to support assertions is it is a very expensive operation and nobody is going to use it for this reason.

In order to provide a convenient way, DBMSes implemented TRIGGERs and CHECKs. By this way, nobody needs assertions indeed.

answered 2011-06-16T07:33:25.133

Why don't DBMS's support ASSERTION

2 Answers

Your Answer