Since SQL-92, what new kinds of RDBMS constraints have been invented and which RDBMS implement them?

Question

Backstory: I'm having to do some computer archeology for a data integration project, involving getting data out a multiple-decades-old old Progress, now OpenEdge, database application for AS/400.

While reading through their documentation for their implementation of SQL-92 from 30 years ago, I saw they supported the exact same kind of RDMBS constraints that we still use today: PRIMARY KEY, FOREIGN KEY, UNIQUE, NULL/NOT NULL and CHECK. As of 2024, this is the same set of constraints that MySQL, MSSQL, Oracle, and Db2 support. Postgres is the only exception, as it supports all of the above and EXCLUDE constraints - but nothing else.

I coughed-up $200 for a legit copy of the current (2023) ISO/IEC 9075-2 specification confirmed that no other kinds of constraints are defined.

...and I find this surprising: I'd have thought that over the past 30 years we'd have other kinds of constraints by now because having to compromise a normalized DB design is still a major pain-point when designing a DB today, and it's easy to come up with ideas for other kinds of constraints - for example, I would really benefit from an "EXISTS" constraint which would be just like a FOREIGN KEY constraint today, but lets you reference a non-unique column in the target table - or a GLOBALLY UNIQUE constraint to ensure that GUID values really are globally unique over all tables.

---

So far I'm only aware of Postgres' EXCLUDE constraints. What other kinds of constraints exist in other, lesser-known, RDBMS? Have their vendors attempted to have them standardised?

Answer 1

Oracle are apparently looking at implementing ASSERTIONs - these are "cross-row constraints, or multi-table check constraints." They are very difficult to reason about and if Oracle can pull it off, hats off to them.

Firebird (a descendant of Interbase c. 2000) allows SQL in CHECK CONSTRAINTs, but as you will see, it doesn't work very well. Something like this should demonstrate (all of this part is available at the fiddle here):

CREATE TABLE test
(
  i INT CHECK ((SELECT SUM(i) FROM test)  < 30)
);

So, then I populate it:

INSERT INTO test VALUES (10);
INSERT INTO test VALUES (10);

and then (for the pièce de résistance), we do the following:

INSERT INTO test VALUES (530);  -- SUM(i) goes to 550

the INSERT works a treat!! Or doesn't...

So, then we try:

INSERT INTO test VALUES (1); 

and it fails on the constraint (via a TRIGGER by the looks of it). So, what's apparently happening (how intuitive is this?), it will let you go to any value above 30 (see fiddle). However, any subsequent INSERT of any value (even negative to bring it < 30 - see fiddle) will then fail! Wow!!

I sent a message to the Firebird lists and received this beauty as a reply (paraphrasing) - "Why on earth would you want to do that?", to which I replied "It's functionality which "works" or, at least, is accepted by the server, you advertised it on your website and why the hell do you allow people to do this if it's completely bug-ridden?" - I didn't receive a reply! :-)

There is another form of CONSTRAINT and that is DOMAINS which is a CONSTRAINT on a type as opposed to a given field as follows (see fiddle here):

CREATE DOMAIN PROBABILITY 
  NUMERIC NOT NULL CHECK (VALUE >= 0.0 AND VALUE <= 1.0);

and then we CREATE two tables:

• CREATE TABLE s (p PROBABILITY);

• CREATE TABLE t (p PROBABILITY);

So, we can use the PROBABILITY type anywhere in our database! Or can we?

Well, yes and no, but (am trying to find the SQL now) if you perform an INSERT via a JOIN the CONSTRAINT isn't CHECKed! Here we go again - I sent an email to the PostgreSQL lists and was told that it wasn't a bug and that I could do something like:

• CREATE TABLE u (p PROBABILITY) CHECK (value >= 0.0 ANd value <= 1.0);

but then I asked "Well, what's the point in having DOMAINs if we have to redeclare the CHECK every time" - can't recall exactly, but I have a vague memory of it "being in the standard" - at this point, I lost the will to live and argue, so that's where the story lies AFAIK! Oracle has them - not sure about any bugs - couldn't test.

Be great to get them ASSERTIONs though - I'm a big believer in DDL being the last bastion of defence for one's data - Oracle (especially) and Firebird (very long way to go) seem to making some steps in the area, but we're not there yet! +1 for a thought-provoking question.