Which RDBMS supports the CREATE ASSERTION statement?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rsocol@gmail.com - 25 Apr 2005 08:53 GMT

In a number of messages, Joe Celko wrote:

"The CREATE ASSERTION statement in SQL-92 will allow the database to
enforce conditions on the entire database as a whole. An ASSERTION is
not like a CHECK() clause, but the difference is subtle. A CHECK()
clause is executed when there are rows in the table to which it is
attached. If the table is empty then all CHECK() clauses are
effectively TRUE. Thus, if we wanted to be sure that the Inventory
table is never empty, and we wrote:

CREATE TABLE Inventory
( ...
CONSTRAINT inventory_not_empty
CHECK ((SELECT COUNT(*) FROM Inventory) > 0), ... );

it would not work. However, we could write:

CREATE ASSERTION Inventory_not_empty
CHECK ((SELECT COUNT(*) FROM Inventory) > 0);

and we would get the desired results. The assertion is checked at the
schema level and not at the table level."

Is there any commercially-available RDBMS (preferably, one that runs on
x86 platforms) that supports the CREATE ASSERTION statement ? It seems
to me that the following RDBMS-s do not support it: Microsoft SQL
Server, MySQL, IBM DB2, Oracle, Informix, PosgreSQL (did I miss any
important one?)

Razvan

Reply to this Message

Troels Arvin - 25 Apr 2005 09:44 GMT

On Mon, 25 Apr 2005 00:53:36 -0700, rsocol wrote:

> Is there any commercially-available RDBMS (preferably, one that runs on
> x86 platforms) that supports the CREATE ASSERTION statement ?

Not that I know of.

You may find the following article interesting, though:
http://www.dbazine.com/oracle/or-articles/tropashko8

Signature

Greetings from Troels Arvin, Copenhagen, Denmark

Reply to this Message

--CELKO-- - 25 Apr 2005 15:00 GMT

RDB from DEC had it, but they got bought by Oracle when DEC went out of
business. Most products follow the rule that constraint names have to
be unique in the schema, not just a table, so that they can add CREATE
ASSERTION Later.

The ability to add subquery predicates to CHECK() at the tabel level is
probably more important and a bit easier to do, since products already
have a trigger mechanism that could be used.

Reply to this Message

DA Morgan - 25 Apr 2005 16:21 GMT

> In a number of messages, Joe Celko wrote:
>
[quoted text clipped - 26 lines]
>
> Razvan

Important? Sybase. But I am not aware of any RDBMS that has such a
statement. Two things should be noted however, the first is that
the business rule you describe can be easily enforced with a
trigger and second one should not throw around the word "database"
without defining what it means. A database in SQL Server, for example,
referes to an entirely different concept than does a database in Oracle.

Signature

Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

Reply to this Message

ak_tiredofspam@yahoo.com - 25 Apr 2005 16:43 GMT

"the business rule you describe can be easily enforced with a
trigger". In some databases, yes, easily with a trigger, but to my best
knowledge not that easily in Oracle, and better not with a trigger.
Agree?

Reply to this Message

DA Morgan - 25 Apr 2005 21:49 GMT

> "the business rule you describe can be easily enforced with a
> trigger". In some databases, yes, easily with a trigger, but to my best
> knowledge not that easily in Oracle, and better not with a trigger.
> Agree?

Disagree. Easily in Oracle with a trigger. The optimal solution
is product dependent.

Signature

Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

Reply to this Message

--CELKO-- - 25 Apr 2005 18:15 GMT

I forgot about Sybase and table level constraints! I am getting old.

>> the business rule you describe can be easily enforced with a trigger

<<

There is a serious problem with that approach. If the table level
constraint is in a trigger, then it is in PROCEDURAL code ; if it is in
a CHECK() clause then it is in DECLARATIVE code. The optimizer can
easily put the constraint into the optimizer to improve the execution,
but it cannot do that with procedural code.

Reply to this Message

DA Morgan - 25 Apr 2005 21:49 GMT

> I forgot about Sybase and table level constraints! I am getting old.
>
[quoted text clipped - 7 lines]
> easily put the constraint into the optimizer to improve the execution,
> but it cannot do that with procedural code.

By what do you mean "optimizer"? Much like the word "database" the
word "optimizer" has different meanings in different products.

In Oracle, for example, the optimizer couldn't care less.

Signature

Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

Reply to this Message

--CELKO-- - 26 Apr 2005 22:22 GMT

Query optimizer. When a constraint is expressed as a Boolean
expression, then the same engine that reduces the logic to an execution
plan can grab the expressions in the CHECK() constraints and add them
to the WHERE and ON clauses. Very simple example:

SELECT *
FROM Foobar
WHERE x > 2 -- x is integer
{{ AND CHECK x BETWEEN 1 AND 3}} -- added by optimizer

Becomes

SELECT *
FROM Foobar
WHERE (x = 3); -- SARG!!

>> In Oracle, for example, the optimizer couldn't care less. <<

Thre is more to life than Oracle :) Ingres and DB2 care, and get a lot
of good stuff out of it. Oracle never had an optimizer until the last
few release and it is still inferior to what Ingres had 20+ years ago.
Ingres also did DCL by adding constraints to statements,so the same
"logic engine" did a lot of various declarative tasks.

Reply to this Message

DA Morgan - 26 Apr 2005 23:35 GMT

> Query optimizer. When a constraint is expressed as a Boolean
> expression, then the same engine that reduces the logic to an execution
[quoted text clipped - 19 lines]
> Ingres also did DCL by adding constraints to statements,so the same
> "logic engine" did a lot of various declarative tasks.

Too bad no one wanted to buy it eh.

Signature

Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

Reply to this Message

ak_tiredofspam@yahoo.com - 27 Apr 2005 14:01 GMT

>> Too bad no one wanted to buy it <<

"Although Oracle Corp. holds the lead in the database software market,
it continues to lose ground to competitors IBM and Microsoft, according
to a new report issued by market researcher International Data Corp.
(IDC), Framingham, Mass"

http://www.adtmag.com/article.asp?id=7423

Reply to this Message

Christopher Browne - 27 Apr 2005 16:11 GMT

>>> Too bad no one wanted to buy it <<
>
[quoted text clipped - 4 lines]
>
> http://www.adtmag.com/article.asp?id=7423

People are prepared to buy Oracle Database (tm) (perhaps at diminished
rates, as you observe above); people are prepared to buy DB2(tm) and
Microsoft SQL Server(tm) (evidently at increasing rates, as compared
to Oracle Database(tm)).

Not mentioned in your quote is that people are evidently even prepared
to buy MySQL(tm).

But the thing that "no one wanted to buy" was Ingres, which, it is
easy to observe, wasn't mentioned in your quote.

If people are more willing to buy MySQL(tm) than Ingres, then that
doubtless has some interesting implications about their decisionmaking
processes...

Signature

wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','gmail.com').
http://linuxdatabases.info/info/
The hypochondriac's epitaph: "NOW will you believe me?"

Reply to this Message

--CELKO-- - 27 Apr 2005 19:29 GMT

It really is a shame. Ingres could not market their way out of a wet
paper bag and Oracle was publishing total lies and falsehoods, engaging
in shady business practices, etc. Richard Finkelstein used to have a
slide show he did at DB conferences on Oracle that had the lies in
their ads, criminal charges, technical errors in the results, actual
downtimes, etc.

The classic one was a test done by Fabian Pascal in DATABASE
PROGRAMMING & DESIGN. He wrote the same query seven different ways and
ran them on various SQL products on the same desktop hardware. Ingres
knew they were logically identical and produces the fastest results of
any of the products. Oracle produced different execution plans for
each query and the slowest running one had to be shut down after
grinding for hours instead of seconds. Oracle has gotten better, but
it is still a pig.

I am hoping that CA will be able to do something with Ingres as Open
Source. I am weird in this trade; I like CA. I have had the
experience of calling a company for support and getting a recorded
message that "the number you have reached is no longer in service" when
I needed to get out an end-of-the-month report. I hope they keep
buying up companies that fail in the market so i can get support.

Reply to this Message