Sub-query in tuple constraint DB2

Question

Hi,

In my database course we use a book (Database System - the Complete Book) which says the following is a valid create table statement in standard SQL:

CREATE TABLE Participants (
    meetid INT NOT NULL,
    -- ...
    CONSTRAINT RoomConstraint
        CHECK (1 >= ALL (SELECT num FROM Numbers)
);

But DB2 complains and gives 20 possible explanations for why this statement fails.

So, does DB2 not support sub-queries in tuple-constraints? And if not, is a TRIGGER the only solution for enforcing the sub-query constraint?

Update: I've found this link which states it ain't possible: http://bytes.com/topic/db2/answers/837390-can-constraint-replace-trigger

But again, is a TRIGGER the only way out? (I'm trying to enforce a relationship where a attribute can refer to two different tables (it ain't my database))

Update 2: It does not work without ALL either:

CREATE TABLE Foo (
   meetid INT NOT NULL,
   CHECK (meetid IN (SELECT meetid FROM Foo)));

Update 3: The idea is that I want a foreign key which references two tables like the following:

Table Participants (pid, ...)
Table Rooms (room, ...)
Table People (userid, ...)

Essentially, a pid shall exists in either Rooms (attribute room) or in People (attribute userid). I could a part of this with a row constraint which checks whether pid is in Rooms or in People - but DB2 won't let me. (I know there is a lot of other stuff to constrain for emulating the foreign key)

Answer 1

ALL() is not standard SQL** -- it's a T-SQL extension. DB2 does not support this.

I'm not sure what you are trying to do with your constraint -- it looks like you're trying to ensure that every value of num in the Numbers table is less than or equal to 1. If this is actually the case, you should add a constraint on the Numbers table, not on Participants.

**the SQL92 standard, and I don't believe it was added to SQL99 or SQL2003