Sample Header Ad - 728x90

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