Using SQL aggregate functions within CREATE's CHECK statement

Question

Taking first steps in the world of Database System Design, I am given a task of preparing a hobby-related database using PostgreSQL environment.

One of my tables is going to store some details on the released expansions of a collectable board game. Said expansions are released in numbered waves, a couple of at once. I want to include the wave number as a column of my table and bound it with certain restriction - the number must be:

• Positive ( > 0 );
• No greater than the current maximum found in the table plus one ( <= MAX()+1 ;

So that multiple expansions coming from a single wave can be inserted into the table and the chain of numbered waves cannot be broken. I tried to set these conditions in place using a CHECK statement

CREATE TABLE Expansions
(
    ID INTEGER NOT NULL,
    Name VARCHAR(50) NOT NULL COLLATE PXW_PLK,
    WaveNumber INTEGER NOT NULL,
    ReleaseDate DATE NOT NULL,
    CONSTRAINT Expansions_PK PRIMARY KEY (ID),
    CONSTRAINT Expansions_U UNIQUE (Name),
    CONSTRAINT Expansions_WaveNumber CHECK (WaveNumber BETWEEN 1 and MAX(WaveNumber) + 1)
);

but such approach results in an exception

Message: isc_dsql_prepare failed

SQL Message : -104
Invalid token

Engine Code    : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -104
Invalid aggregate reference

I struggle to determine if:

• my approach is altogether wrong and enforcing my restriction calls for different SQL construct?
• I fail to appropriately place MAX() call within a CHECK statement?

Answer 1

First, this is not directly supported in a check constraint.

Second, perhaps having a separate WaveNumbers table with a serial column would be the best solution. You can have a foreign key constraint. This does not guarantee no gaps. It does guarantee that wave numbers are "valid".

Postgres does offer a solution. You can create a user-defined function that does the validation, and then use this function in the check constraint.

I don't really recommend this approach. There is a lot of overhead to aggregation functions in check constraints. And, I suspect that some tweaks to the data model would allow you to use foreign key constraints.