Why Don't Dbms's Support Assertion

Why don't DBMS's support ASSERTION

There are four levels of constraint: column-level, row-level, table-level and schema-level.

A table-level could, for example, involve a target table other than the source table on which it was declared but only gets checked when the source table changes. In theory a schema-level constraint would be checked for every change in every table in the schema but in practise the optimizer would be able to detect changes in a more granular way; consequently, if your DBMS had support for schema-level constraint then you wouldn't find much use for table-level constraints in practise.

No current SQL product supports schema-level constraints i.e. CREATE ASSERTION. Apparently Rdb did support it when it was looked after by DEC but that is no longer the case. -- UPDATE: in a private message I was advised that Sybase's SQL Anywhere supports CREATE ASSERTION but with serious errors that allow such constraints sometimes to be violated!

The only SQL-like product I've used that currently supports subqueries in CHECK constraints, which enables table-level constraints, is the Access database engine (ACE, Jet, whatever). It has issues, though. First, there is not support for SQL-92 functionality (or equivalent) to defer constraint checking. Second, table-level constraints are checked for each row affected, rather than when the statement completes as required by the SQL-92 Standard. Needless to say, the workaround is very clunky e.g. drop the constraint and in doing so lock the table, execute the update, recreate the constraint. Schema-level constraints, arguably achievable by adding the same constraint to all the tables it involves, is virtually unworkable.

Possibly for these reasons, the Access Team have never publicized its CHECK constraint functionality at all beyond the initial announcements for Jet 4.0 (it remains missing from the Access Help, for example). All that said, for intra-table constraints (e.g. a sequenced key in a valid-state 'history' temporal table) the functionality works well, especially when you consider that Access only got trigger-like functionality (not SQL based, though) last year.

SQL of course has UNIQUE constraints and referential integrity constraints that are of course table-level but these are special cases. Therefore, all constraints you will encounter 'in the wild' will be either colum- or row-level.

Do be aware with MySQL that, although using CHECK() in SQL DDL will parse without error, it will have no effect. How users can tolerate a SQL product with no CHECK constraints at all is beyond me! PostgreSQL has a excellent constraints model, hint hint :)

So why are inter-table constraints so rarelt supported? One reason must be due to historical circumstances. As @gbn correctly identifies (under the title Concurrency), the Sybase/SQL Server family of SQL implementations is based on a model that cannot cope with inter-table constraint checking and that's not something that is likely to ever change.

Consider looking at this the other way around: if you were creating a SQL product today, would you include CREATE ASSERTION? If you did, you would certainly have to also implement DEFERRABLE constraints (even though multiple assignment is arguably the better model). But you would be able to draw on a lot more research and experience if you went down the route of building a 'traditional' optimizer. And perhaps you'd find there is no commercial demand for schema-level constraints (if MySQL can get anyway without CHECK constraints...) If PostgreSQL doesn't do it, I don't think anyone ever will.

I think the real show stopper is that most industrial-strength products have already developed trigger functionality that allows users to write 'constraints' of arbitrary complexity (plus can a lot more e.g. send an email to tell something happened). Sure, they are procedural rather than declarative, the coder has to do a lot of extra work that the system would take care of with true constraints, and the performance tends to be not so great. But the fact is they existing in real products today and do provide a 'get out of jail free card' card for vendors. Why should they bother implementing worthy features if customers are not banging the table for them?

As regards academic/teaching langauges, as @Damien_The_Unbeliever correctly identifies, a Tutorial D CONSTRAINT are always 'schema'-level, hence allow for global constraints of arbitrary conplexity by definition. If you are looking to design your own DBMS(!!) with this kind of functionality, you should consider implementing the D specification while using an existing SQL DBMS for storage, as Dataphor have done.

---

A question has been bothering me: given that existing 'industrial strength' SQL DBMSs support triggers, why don't they simply map declarative CREATE ASSERTION to a trigger under the covers? I've long suspected the answer is because they know that performance would be appalling given their legacy technology.

A more satisfying answer is provided in Applied Mathematics for Database Professionals  By Lex de Haan, Toon Koppelaars, chapter 11. They define various execution models to use when using triggers to enforce multi-tuple constraints. The most sophisticated (though still highly doable) model, which they call EM6, involves the following steps:

1. Translate the formal specification into a constraint validation query.
2. Develop code to maintain transition effects.
3. Devise transition effect (TE) queries that ensure the constraint validation query is only run when necessary [e.g. Can I limit the check to only the updated rows? Can a DELETE ever violate this constraint? Are there only specific columns that an UPDATE must involve to require the constraint to be checked? etc]
4. Discover a means to optimize the constraint validation query by having the TE query provide values that can be used in the validation query.
5. Devise and add a serialization strategy to the data integrity (DI) code. [i.e. solve the concurrecy problem where a transaction cannot read the 'bad' data another transaction is writing].

They then assert (no pun intended!):

Because we believe that it is not possible for a DBMS vendor to
program an algorithm that accepts an arbitrarily complex predicate and
then computes efficient transition effect (TE) queries, a minimal
validation query, and optimal serialization code to implement
execution model EM6, we should not expect full support for multi-tuple
constraints—in a practical, usable and acceptable way—from these
vendors in the future. The best we can hope for is that database
researchers first come up with more common classes of constraints and
develop convenient shorthands for these. The DBMS vendors, in their
turn, should then provide us with new declarative constructs,
consistent with these shorthands, to state these common classes of
constraints easily to the DBMS. Given such a common class declaration,
the DBMS vendor should be able to program an algorithm that provides
us with an EM6-like execution model under the covers to implement the
constraint.

One such common class of database constraint is a foreign key, which is already widely implemented, of course.

what is the difference between triggers, assertions and checks (in database)

Triggers - a trigger is a piece of SQL to execute either before or after an update, insert, or delete in a database. An example of a trigger in plain English might be something like: before updating a customer record, save a copy of the current record. Which would look something like:

CREATE TRIGGER triggerName
AFTER UPDATE
    INSERT INTO CustomerLog (blah, blah, blah)
    SELECT blah, blah, blah FROM deleted

---

The difference between assertions and checks is a little more murky, many databases don't even support assertions.

Check Constraint - A check is a piece of SQL which makes sure a condition is satisfied before action can be taken on a record. In plain English this would be something like: All customers must have an account balance of at least $100 in their account. Which would look something like:

ALTER TABLE accounts 
ADD CONSTRAINT CK_minimumBalance
CHECK (balance >= 100)

Any attempt to insert a value in the balance column of less than 100 would throw an error.

Assertions - An assertion is a piece of SQL which makes sure a condition is satisfied or it stops action being taken on a database object. It could mean locking out the whole table or even the whole database.

To make matters more confusing - a trigger could be used to enforce a check constraint and in some DBs can take the place of an assertion (by allowing you to run code un-related to the table being modified). A common mistake for beginners is to use a check constraint when a trigger is required or a trigger when a check constraint is required.

An example: All new customers opening an account must have a balance of $100; however, once the account is opened their balance can fall below that amount. In this case you have to use a trigger because you only want the condition evaluated when a new record is inserted.

Does SQL Server 2008 support the CREATE ASSERTION syntax?

No SQL Server 2008 does not support this syntax.

getting error: could not find stored procedure while making assertions

SQL Server doesn't support Create Assertion , you can use a Check Constraint or Create Trigger as:

IF OBJECT_ID ('assertion12','TR') IS NOT NULL
   DROP TRIGGER assertion12;
GO
-- This trigger prevents a row from being inserted in the Patients table
-- when current patient's primary doctor already has patients = 4 (max 4)

CREATE TRIGGER assertion12 ON Patient
AFTER INSERT
AS
IF EXISTS ( select count(P.primaryDoctor_SSN)
            from Patient P 
            JOIN inserted AS i 
            ON p.primaryDoctor_SSN = i.primaryDoctor_SSN 
            group by P.primaryDoctor_SSN
            having count(P.primaryDoctor_SSN) =4
          )
BEGIN
RAISERROR ('Primary Doctor Assigned to the Patient already has 4 patients.', 16, 1);
ROLLBACK TRANSACTION;
RETURN 
END;
GO

-- This statement attempts to insert a row into the Patient table
-- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back.

INSERT INTO Patient values (5,1)
GO

DEMO

Constraints and Assertions in PostgreSQL

As @ruakh already cleared up, there is no CREATE ASSERTION in PostgreSQL. Just check the list of SQL commands. It's not there.

You can use triggers that update a count per customer combined with a CHECK constraint, but you have to cover all relevant DML statements: INSERT, UPDATE, DELETE. Could look like this:

Prepare existing customer table:

ALTER TABLE customer ADD COLUMN order_ct integer DEFAULT 0;
UPDATE customer SET order_ct = 0;
ALTER TABLE customer ALTER order_ct SET NOT NULL;
ALTER TABLE customer ADD CONSTRAINT order_ct_max1000 CHECK (order_ct <= 1000);

Create trigger functions and triggers:

CREATE OR REPLACE FUNCTION trg_order_upaft()
  RETURNS trigger AS
$BODY$
BEGIN

IF OLD.customer_num <> NEW.customer_num THEN
    UPDATE customer
    SET    order_ct = order_ct - 1
    WHERE  customer_num = OLD.customer_num;

    UPDATE customer
    SET    order_ct = order_ct + 1
    WHERE  customer_num = NEW.customer_num;
END IF;

RETURN NULL;

END;
$BODY$
  LANGUAGE plpgsql;

CREATE TRIGGER upaft
  AFTER UPDATE ON orders FOR EACH ROW
  EXECUTE PROCEDURE trg_order_upaft();

CREATE OR REPLACE FUNCTION trg_order_insaft()
  RETURNS trigger AS
$BODY$
BEGIN

UPDATE customer
SET    order_ct = order_ct + 1
WHERE  customer_num = NEW.customer_num;

RETURN NULL;

END;
$BODY$
  LANGUAGE plpgsql;

CREATE TRIGGER insaft
  AFTER INSERT ON orders FOR EACH ROW
  EXECUTE PROCEDURE trg_order_insaft();

CREATE OR REPLACE FUNCTION trg_order_delaft()
  RETURNS trigger AS
$BODY$
BEGIN

UPDATE customer
SET    order_ct = order_ct - 1;
WHERE  customer_num = OLD.customer_num;

RETURN NULL;

END;
$BODY$
  LANGUAGE plpgsql;

CREATE TRIGGER delaft
  AFTER DELETE ON orders FOR EACH ROW
  EXECUTE PROCEDURE trg_order_delaft();

I made all those triggers AFTER triggers - that's why it is ok to RETURN NULL. AFTER is preferable to BEFORE in this case. It performs better if any other conditions could cancel DML statements in the middle (like other triggers).

If you have nothing of the sort, then BEFORE triggers may be preferable. Be sure to make the trigger functions RETURN NEW / OLD accordingly in this case.

---

Related Topics