哪些SQL数据库支持CHECK约束中的子查询？

目前,据我所知,Oracle,MySQL和PostgreSQL都没有.

编辑

(根据初步答案澄清.)我正在寻找这样的事情：

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和Firebird都支持此功能.

解决方法

例如,我可以证明对每个受影响的行检查Access CHECK约束(SQL-92指定在每个SQL语句之后应该检查它们)但是这是一个bug还是我们不知道的功能,因为没有文档到参考.

这是一个包含子查询的CHECK约束的一个非常简单的例子.它符合Full SQL-92,在Access中运行良好.我们的想法是将表限制为最多两行(以下SQL DDL需要ANSI-92 Query Mode,例如使用ADO连接,例如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
                        )
            );

但是,这是另一个例子,即SQL-92,可以在Access中创建(一些有效的CHECK在Access中失败,需要重新启动我的机器的可怕崩溃:(但是无法正常运行.这个想法只是为了允许表中恰好有两行(或零行：不测试空表的约束)：

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

尝试在同一语句中插入两行,例如(假设表T1至少有一行)：

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

但是,这导致CHECK咬人.这(以及进一步测试)意味着在每行添加到表之后测试CHECK,而SQL-92指定在SQL语句级别测试约束.

当您考虑到Access2010之前它没有任何触发功能并且某些经常使用的表没有真正的密钥(例如”时,Access确实具有表级CHECK约束,这不应该太令人意外.在有效状态时态表中对密钥进行排序.请注意,Access2010触发器遇到与在行级别而不是在语句级别测试它们相同的错误/功能.

以下是VBA重现上述两种情况.复制并粘贴到任何VBA / VB6标准.bas模块(例如使用Excel),不需要引用.在临时文件夹中创建一个新的.mdb,创建约束工作/不起作用的表,数据和测试(提示：设置断点,单步执行代码,阅读注释)：

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!