Re: Foreign superkey support

From: Jon Heggland <jon.heggland_at_idi.ntnu.no>
Date: Thu, 17 Aug 2006 12:40:59 +0200
Message-ID: <ec1h03$sr6$1_at_orkan.itea.ntnu.no>

David Cressey wrote:
> "Jon Heggland" <jon.heggland_at_idi.ntnu.no> wrote in message
> news:ebcmje$slu$1_at_orkan.itea.ntnu.no...

>> What precisely do you mean by a CHECK constraint? The "CHECK" keyword is
>> used for several different kinds of constraints in SQL, with varying
>> degrees of support/implementation.

>
> I was unaware of the differences you describe.

Of course, there are various classifications of constraints, so I use "kinds of constraints" rather informally... Anyway, there's this sort of thing:

create table test (
  a integer primary key constraint c1 check (a > 10),   b integer,
  constraint c2 check (b > a)
);

---i.e. column and row constraints (arguably the same thing)---which both Oracle and SQL Server supports. Then there is this:

create table test2 (
  c integer primary key,
  d integer,
  constraint c3 check (d in (select a from test)) );

---i.e. a row constraint with a subquery---which neither Oracle nor SQL Server allows; and for which the SQL specification is flawed anyway. And there is this sort of thing:

create assertion a1 check (
  not exists (
    select * from test, test2
     where a = d and c < b
  )
);

---which also neither Oracle nor SQL Server supports.

As to the term "CHECK constraint", I was unsure exactly what it meant, but it seems that SQL uses it for the row and column constraints above; but not for NOT NULL or UNIQUE constraints, despite these being shorthand for equivalent CHECK formulations; and also not for assertions, despite the use of the CHECK keyword. :s

> The closest I can come to being specific is the CHECK constraint as
> implemented in Rdb.

Well, which of the constraints above does it support (if it is SQL-based)?

-- 
Jon

Received on Thu Aug 17 2006 - 12:40:59 CEST