目前,据我所知,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
解决方法
例如,我可以证明对每个受影响的行检查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
