Does SQL Server 2008 Support The Create Assertion Syntax

Does SQL Server 2008 support the CREATE ASSERTION syntax?

No SQL Server 2008 does not support this syntax.

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:

Translate the formal specification into a constraint validation query.
Develop code to maintain transition effects.
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]
Discover a means to optimize the constraint validation query by having the TE query provide values that can be used in the validation query.
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.

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

Is this the correct way to create SQL assertion?

For full details on CREATE ASSERTION see the ISO SQL-92 Standard spec.

The CHECK definition should be in parentheses.

CURRENT_DATE does not have parentheses.

USER and DATE are reserved words.

SQL statements should be terminated with a semicolon character.

SQL Keywords should be in upper case.

Try something more like this:

CREATE ASSERTION assert  
CHECK (0 = (
            SELECT COUNT(*)  
              FROM Video  
             WHERE my_date = CURRENT_DATE  
             GROUP 
                BY my_user
            HAVING COUNT(*) >= 10
           ));

You can test that the syntax is correct using the online Mimer SQL-92 Validator. However, you should also test your logic e.g. CURRENT_DATE is non-deterministic.

Also, I don't think this ASSERTION will ever bite. When the cardinality of the subquery is less than 10 it will return zero rows and 0 = empty set will evaluate to UNKNOWN. When the cardinality of the subquery is 10 or greater then the search condition will evaluate TRUE. SQL-92 Standard states

The assertion is not satisfied if and
only if the result of evaluating the
search condition is false.

Note you can replace your CHECK (0 = (SELECT COUNT(*) FROM...)) construct with CHECK (NOT EXISTS (SELECT * FROM...)), the latter of which I find easier to write.

UPDATE:

How should I write the assertion using
CHECK NOT EXISTS ?

As I said above, your logic appears flawed so it is hard to implement properly ;)

Let's say the rule is to limit Videos to 10 per user per day. Because this involves only a single table, it would be more appropriate to use a table-level CHECK constraint; such a constraint is checked when the table is updated which is sufficient in this case (there's no reason why it couldn't be an ASSERTION, though, which in theory could be checked each time any table in the schema is updated):

ALTER TABLE Video ADD 
   CONSTRAINT video_limit_10_per_user_per_day
      CHECK (NOT EXISTS (
                         SELECT v1.my_user, v1.my_date
                           FROM Video AS V1
                          GROUP BY v1.my_user, v1.my_date
                         HAVING COUNT(*) > 10
                        ));

UPDATE 2:

thanks,now let's say we want to limit
videos to 100 per user per year, in
this case using current_date would be
necessary wouldn't it?

Consider again that a CHECK/ASSERTION will only be checked when data in the table/schema is updated. The problem with using CURRENT_DATE (and other non-determninistic functions) in a constraint is that the business rule can be invalidated simply by the clock ticking over from one time period to the next but if the data hasn't been changed in that period then the data integrity failure will not be detected and the database will contain invalid data.

Another consideration is what is meant by a year in context.

It could be the calendar year (1 Jan to 31 Dec inclusive) or other other fixed dates defined by enterprise (e.g. 1 Apr to 31 Mar inclusive), in which case grouping by year and user then counting is trivial.

A more interesting case is when the rule limits the tally for any 12 month period; extending this to both the past and future avoid the above 'non-deterministic' issue.

Consider a standard approach of using an auxiliary calendar table, containing one row for every day applicable to the enterprise, extended into the past and future only as far as required should still only comprise a few thousand rows. Each row would have the date as a key with a second column for that date plus one year (and if necessary you could fine tune the definition of 'a year' at one-day granularity!) The test for would involve joining to the Calendar table, grouping on the calendar date and user and counting e.g. something like this:

SELECT C1.dt, V1.my_user
  FROM Video AS V1
       INNER JOIN Calendar AS C1
          ON (V1.my_date BETWEEN C1.dt AND C1.dt_plus_one_year)
 GROUP 
    BY C1.dt, V1.my_user
HAVING COUNT(*) > 100;

This could be put in a CHECK (NOT EXISTS (... constraint. This could still be a table-level CHECK constraint: because the Calendar table is an auxiliary table it would only be suject to infrequent controlled updates (but again could be an ASSERTION if required).

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.

how to create user defined Type in Sql

CREATE TYPE myType AS TABLE
(
   idx INT,
   CHECK (idx > 100 AND idx < 999)
)

Or you can also create rules and bind them to your type.

CREATE TYPE [dbo].[myType] 
FROM [INT] NOT NULL
GO

CREATE RULE multiplyByTen
AS @myType % 10 = 0
AND @myType > 100
AND @myType < 999

Then Bind Rule

EXEC sp_bindrule 'multiplyByTen', 'myType'

Converting Access SQL Queries to SqlServer

I've never seen anything like [Numbers by Quarter and Bedsize].SizeCategory

This is an example of a dot qualified name. The element to the left of the dot is the correlation name. If you do not provide an explicit correlation name in the from clause, a default correlation name equal to the table name is assumed. For example

SELECT [Numbers by Quarter and Bedsize].SizeCategory
  FROM [Numbers by Quarter and Bedsize];

...is actually parsed as:

SELECT [Numbers by Quarter and Bedsize].SizeCategory
  FROM [Numbers by Quarter and Bedsize] AS [Numbers by Quarter and Bedsize];

You'll see correlation name referred to as an "alias", which is not correct but had sadly become the vernacular.

Arguably it makes the code easier to read is a short correlation name is chosen e.g.

SELECT n.SizeCategory
  FROM [Numbers by Quarter and Bedsize] AS n;

The square brackets around the table name are quoted identifiers. In Standard SQL, the quoted identifier is the double quote ("). The Access database engine (ACE, Jet, whatever) is not compliant with the SQL Standard and uses square brackets. SQL Server can use both the Standard and the proprietary square brackets as quoted identifiers.

Access requires quoted identifiers when a data element would otherwise be illegal e.g. contains or begins with spaces or non-alpha characters. The Access query by example builder thing tends to add quoted identifiers around all data element names regardless of whether they are actually required.

The table name Numbers by Quarter and Bedsize requires quotes identifiers due to the presence of spaces. I suggest you take the port as an opportunity to rename data elements. While a complete revision is probably due (I would question the general quality of the names chosen), some 'low hanging fruit' would be the opportunity to replace space characters with underscores.

Following the above advice, and adding some formatting, the query would become:

SELECT n.SizeCategory, 
       n.Bedsize, 
       n.SumOfNumber_of_CLABSI, 
       n.SumOfCentral_Line_Days,
       Round(1000 * n.SumOfNumber_of_CLABSI / n.SumOfCentral_Line_Days, 2) 
          AS State_CLABSI_Rate, 
       n.SummaryYQ
  FROM Numbers_by_Quarter_and_Bedsize AS n
  ORDER 
     BY SizeCategory, SummaryYQ;

Obviously, the port will involve syntax changes but you also should also look for changes in behaviour. For example, division in Access SQL results in a FLOAT unless one of the values is of type DECIMAL in which case the result is a DECIMAL; the behaviour in SQL Server is different. One of the challenges you will face is the extremely poor documentation for Access e.g. the assertion I just made about division is not stated in the Access help nor any other official documentation from Microsoft; I had to figure it out for myself by trial and error.

I recommend you write comprehensive tests in both Access and SQL Server to ensure your ported SQL does not result in changed behaviour.

Maintaining subclass integrity in a relational database

Here are a couple of possibilities. One is a CHECK in each table that the student_id does not appear in any of the other sister subtype tables. This is probably expensive and every time you need a new subtype, you need to modify the constraint in all the existing tables.

CREATE TABLE athletes (
  student_id INT NOT NULL PRIMARY KEY,
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  CHECK (student_id NOT IN (SELECT student_id FROM musicians 
                      UNION SELECT student_id FROM slackers 
                      UNION ...)) 
);

edit: @JackPDouglas correctly points out that the above form of CHECK constraint is not supported by Microsoft SQL Server. Nor, in fact, is it valid per the SQL-99 standard to reference another table (see http://kb.askmonty.org/v/constraint_type-check-constraint).

SQL-99 defines a metadata object for multi-table constraints. This is called an ASSERTION, however I don't know any RDBMS that implements assertions.

Probably a better way is to make the primary key in the students table a compound primary key, the second column denotes a subtype. Then restrict that column in each child table to a single value corresponding to the subtype represented by the table. edit: no need to make the PK a compound key in child tables.

CREATE TABLE athletes (
  student_id INT NOT NULL PRIMARY KEY,
  student_type CHAR(4) NOT NULL CHECK (student_type = 'ATHL'),
  FOREIGN KEY (student_id, student_type) REFERENCES students(student_id, student_type)
);

Of course student_type could just as easily be an integer, I'm just showing it as a char for illustration purposes.

If you don't have support for CHECK constraints (e.g. MySQL), then you can do something similar in a trigger.

I read your followup about making sure a row exists in some subclass table for every row in the superclass table. I don't think there's a practical way to do this with SQL metadata and constraints. The only option I can suggest to meet this requirement is to use Single-Table Inheritance. Otherwise you need to rely on application code to enforce it.

edit: JackPDouglas also suggests using a design based on Class Table Inheritance. See his example or my examples of the similar technique here or here or here.

What does the Java assert keyword do, and when should it be used?

Assertions (by way of the assert keyword) were added in Java 1.4. They are used to verify the correctness of an invariant in the code. They should never be triggered in production code, and are indicative of a bug or misuse of a code path. They can be activated at run-time by way of the -ea option on the java command, but are not turned on by default.

An example:

public Foo acquireFoo(int id) {
  Foo result = null;
  if (id > 50) {
    result = fooService.read(id);
  } else {
    result = new Foo(id);
  }
  assert result != null;

  return result;
}

How to avoid the divide by zero error in SQL?

In order to avoid a "Division by zero" error we have programmed it like this:

Select Case when divisor=0 then null
Else dividend / divisor
End ,,,

But here is a much nicer way of doing it:

Select dividend / NULLIF(divisor, 0) ...

Now the only problem is to remember the NullIf bit, if I use the "/" key.

Does SQL Server 2008 Support The Create Assertion Syntax