Sub Queries in Check Constraint

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')

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

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

Using subquery in a Check statement in Oracle

There are three basic ways to solve this sort of problem since CHECK constraints cannot be based on a query.

Option 1: Triggers

The most simplistic approach would be to put a trigger on TANK that queries TANKS and throws an exception if the LEVEL exceeds CAPACITY. The problem with this sort of simplistic approach, though, is that it is nearly impossible to handle concurrency issues correctly. If session 1 decreases the CAPACITY, then session 2 increases the LEVEL, and then both transactions commit, triggers will not be able to detect the violation. This may not be an issue if one or both of the tables are seldom modified, but in general it's going to be an issue.

Option 2: Materialized views

You can solve the concurrency issue by creating an ON COMMIT materialized view that joins the TANK and TANKS table and then creating a CHECK constraint on the materialized view that verifies that the LEVEL <= CAPACITY. You can also avoid storing the data twice by having the materialized view contain just data that would violate the constraint. This will require materialized view logs on both the base tables which will add a bit of overhead to inserts (though less than using triggers). Pushing the check to commit-time will solve the concurrency issue but it introduces a bit of an exception management issue since the COMMIT operation can now fail because the materialized view refresh failed. Your application would need to be able to handle that problem and to alert the user to that fact.

Option 3: Change the data model

If you have a value in table A that depends on a limit in table B, that may indicate that the limit in B ought to be an attribute of table A (instead of or in addition to being an attribute of table B). It depends on the specifics of your data model, of course, but it's often worth considering.

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).

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