What SQL Databases Support Subqueries in Check Constraints

What SQL databases support subqueries in CHECK constraints?

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

Sub queries in check constraint

Note, what you really want is a foreign key constraint. That said, to get a "query" into a check you can write a function that contains the query and outputs a scalar value, then use that function in the check constraint.

CREATE FUNCTION myFunction (
    @field DATATYPE(?)
)
RETURNS VARCHAR(5)
AS
BEGIN
    IF EXISTS (SELECT* FROM Table2 WHERE MYFIELD = @field)
        return 'True'
    return 'False'
END

Something like that. Not tested.

Then you can add it to your check like so

ALTER TABLE Table1
    WITH CHECK ADD CONSTRAINT CK_Code
    CHECK (myFunction(MYFIELD) = 'True')

Oracle SQL - Can I use sub-query in a CHECK constraint in DDL?

It's better to ask the documentation about that:

Restrictions on Check Constraints

Check constraints are subject to the following restrictions:

• Conditions of check constraints cannot contain the following constructs:
• 
  
  • Subqueries and scalar subquery expressions
• 
  
  • Calls to user-defined functions

SQL Sub queries in check constraint

It is not supported to look beyond the current row in a CHECK constraint.

http://www.postgresql.org/docs/9.1/interactive/sql-createtable.html says:

A check constraint specified as a column constraint should reference
that column's value only, while an expression appearing in a table
constraint can reference multiple columns.

Currently, CHECK expressions cannot contain subqueries nor refer to
variables other than columns of the current row.

There are good reasons for this restriction, but if you like to juggle flaming torches while riding a unicycle through heavy traffic, you can subvert the restriction using functions. The situations in which this will not come back to bite you are rare; you would be much safer to enforce the invariant in trigger code instead.

http://www.postgresql.org/docs/9.1/interactive/triggers.html

Check Constraint - Subqueries are not allowed in this context

SQL Server does not currently support subqueries for CHECK CONSTRAINTs.

As you have discovered, there can be trouble with CHECK constraints involving UDFs when attempting to circumvent the subquery limitation.

The alternative constraint implementation strategies are triggered procedural and embedded procedural. The former is preferred because, in common with declarative constraints, they cannot be circumvented.

Implementing a triggered procedural strategy that is well optimized and handles concurrency issues is non-trivial but still doable. I highly recommend the book Applied Mathematics for Database Professionals By Lex de Haan, Toon Koppelaars, chapter 11 (the code examples are Oracle but can be easily ported to SQL Server).

implementing complex check constraints

You can enforce the "single active record" pattern in two ways:

1. The solution you suggest, which is to create a table that holds only the primary key values of the active records from the multiple-records-allowed table. Those values also serve as a primary key in the active records table.

2. Adding a column to another table that represents the objects that can have only a single active record each. In this case that would mean adding a column active_group_name to systems. This column would be a foreign key to the multiple-records-allowed table.

Which is preferable depends, in part, on whether every section is required to have an active group, whether it's common (but not required) for a section to have an active group, or whether it's only occasionally true that a section has an active group.

In the first case (required), you would use option (2) and the column could be declared NOT NULL, preserving complete normalization. In the second case (common) you would need to make the column NULLable but I'd probably still use that technique for convenience of JOINs. In the third case (occasional), I'd probably use option (1) since it might well improve performance when JOINing to get the active records.

---

Related Topics