Since SQL-92, what new kinds of RDBMS constraints have been invented and which RDBMS implement them?
1
vote
1
answer
86
views
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?
Asked by Dai
(632 rep)
May 26, 2024, 04:58 PM
Last activity: May 26, 2024, 07:42 PM
Last activity: May 26, 2024, 07:42 PM