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?

Gordon Linoff · Accepted Answer · 2018-01-25 21:09:45Z

4

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.

answered Jan 25, 2018 at 21:09

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ryfterek Over a year ago

Thanks! As I was asked not to pump the size of my project unnecessarily with redundant dictionaries of integers, and a user-defined function falls out of the scope I'm dealing with, for now, I will settle for dropping the gap-validation for this field at this stage. Perhaps in the future, I will get back to the topic to see how such constraint can be implemented when really needed, here it was more of a nice to have idea of mine.

Collectives™ on Stack Overflow

Using SQL aggregate functions within CREATE's CHECK statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related