subquery - What SQL databases support subqueries in CHECK constraints?

Question

What SQL databases, if any, support subqueries in CHECK constraints?

At present and as far as I know, Oracle, MySQL, and PostgreSQL do not.

EDIT

(Clarification based on initial answers.) I'm looking for something like this:

CREATE TABLE personnel (
  ...,
department VARCHAR(64) NOT NULL,
salary NUMERIC NOT NULL,
CHECK (salary >= (SELECT MIN(p.salary) FROM payranges p WHERE p.dept = department)
        AND
       salary <= (SELECT MAX(p.salary) FROM payranges p WHERE p.dept = department)
)

UPDATE

MS Access and Firebird both support this feature.

Answer 1

The Access database engine (ACE, Jet, whatever) supports subqueries in CHECK constraints but I hesitate to call it a SQL DBMS because it doesn't support entry level Standard SQL-92 and Access CHECK constraints are barely documented by MS and the Access Team.

For example, I can demonstrate that Access CHECK constraints are checked for each row affected (SQL-92 specifies that they should be checked after each SQL statement) but whether this is a bug or a feature we do not know because there is no documentation to refer to.

---

Here's a very simple example of a CHECK constraint that comprises a subquery. It is compliant with Full SQL-92 and works well in Access. The idea is to restrict the table to a maximum of two rows (the following SQL DDL requires ANSI-92 Query Mode e.g. use an ADO connection such as Access.CurrentProject.Connection):

CREATE TABLE T1 
(
 c INTEGER NOT NULL UNIQUE
);

ALTER TABLE T1 ADD
   CONSTRAINT max_two_rows
      CHECK (
             NOT EXISTS (
                         SELECT 1
                           FROM T1 AS T
                         HAVING COUNT(*) > 2
                        )
            );

However, here is a further example that is SQL-92, can be created in Access (some valid CHECKs fail in Access with a horrid crash that requires my machine to be restarted :( but doesn't function properly. The idea is to only allow exactly two rows in the table (or zero rows: constraints are not tested for an empty table):

CREATE TABLE T2 
( 
 c INTEGER NOT NULL UNIQUE 
);

ALTER TABLE T2 ADD 
   CONSTRAINT exactly_two_rows 
      CHECK ( 
             NOT EXISTS ( 
                         SELECT 1 
                           FROM T2 AS T 
                         HAVING COUNT(*) <> 2 
                        ) 
            );

Attempt to INSERT two rows in the same statement e.g. (assuming table T1 has at least one row):

SELECT DT1.c
  FROM (
        SELECT DISTINCT 1 AS c
          FROM T1
        UNION ALL
        SELECT DISTINCT 2
          FROM T1
       ) AS DT1;

However, this causes the CHECK to bite. This (and further testing) implies that the CHECK is tested after each row is added to the table, whereas SQL-92 specifies that constraints are tested at the SQL statement level.

It shouldn't come as too much of a surprise that Access has truly table-level CHECK constraints when you consider that until Access2010 it didn't have any trigger functionality and certain oft-used tables would otherwise have no true key (e.g. the 'sequenced' key in a valid-state temporal table). Note that Access2010 triggers suffer the same bug/feature that they are tested at the row level, rather than at the statement level.

The following is VBA to reproduce the two scenarios described above. Copy and paste into any VBA/VB6 standard .bas module (e.g. use Excel), no references required. Creates a new .mdb in your temp folder, creates the tables, data and tests that the constraints work/do not work (hint: set a breakpoint, step through the code, reading the comments):

Sub AccessCheckSubqueryButProblem()

  On Error Resume Next
  Kill Environ$("temp") & "DropMe.mdb"
  On Error GoTo 0

  Dim cat
  Set cat = CreateObject("ADOX.Catalog")
  With cat
    .Create _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & _
        Environ$("temp") & "DropMe.mdb"
    With .ActiveConnection

      Dim Sql As String

      Sql = _
      "CREATE TABLE T1 " & vbCr & _
      "( " & vbCr & _
      " c INTEGER NOT NULL UNIQUE " & vbCr & _
      ");"
      .Execute Sql

      Sql = _
      "ALTER TABLE T1 ADD " & vbCr & _
      "   CONSTRAINT max_two_rows " & vbCr & _
      "      CHECK ( " & vbCr & _
      "             NOT EXISTS ( " & vbCr & _
      "                         SELECT 1 " & vbCr & _
      "                           FROM T1 AS T " & vbCr & _
      "                         HAVING COUNT(*) > 2 " & vbCr & _
      "                        ) " & vbCr & _
      "            );"
      .Execute Sql

      Sql = _
      "INSERT INTO T1 (c) VALUES (1);"
      .Execute Sql

      Sql = _
      "INSERT INTO T1 (c) VALUES (2);"
      .Execute Sql

      ' The third row should (and does)
      ' cause the CHECK to bite
      On Error Resume Next
      Sql = _
      "INSERT INTO T1 (c) VALUES (3);"
      .Execute Sql
      MsgBox Err.Description
      On Error GoTo 0

      Sql = _
      "CREATE TABLE T2 " & vbCr & _
      "( " & vbCr & _
      " c INTEGER NOT NULL UNIQUE " & vbCr & _
      ");"
      .Execute Sql

      Sql = _
      "ALTER TABLE T2 ADD " & vbCr & _
      "   CONSTRAINT exactly_two_rows " & vbCr & _
      "      CHECK ( " & vbCr & _
      "             NOT EXISTS ( " & vbCr & _
      "                         SELECT 1 " & vbCr & _
      "                           FROM T2 AS T " & vbCr & _
      "                         HAVING COUNT(*) <> 2 " & vbCr & _
      "                        ) " & vbCr & _
      "            );"
      .Execute Sql

      ' INSERTing two rows in the same SQL statement
      ' should succeed according to SQL-92
      ' but fails (and we have no docs from MS
      ' to indicate whether this is a bug/feature)
      On Error Resume Next
      Sql = _
      "INSERT INTO T2 " & vbCr & _
      "   SELECT c " & vbCr & _
      "     FROM T1;"
      .Execute Sql
      MsgBox Err.Description
      On Error GoTo 0


    End With
    Set .ActiveConnection = Nothing
  End With
End Sub