哪些SQL数据库在CHECK约束中支持子查询? [英] What SQL databases support subqueries in CHECK constraints?
问题描述
哪些SQL数据库(如果有的话)支持CHECK约束中的子查询?
What SQL databases, if any, support subqueries in CHECK constraints?
目前，据我所知，Oracle，MySQL和PostgreSQL还没有.
At present and as far as I know, Oracle, MySQL, and PostgreSQL do not.
编辑
(根据最初的答案进行澄清.)我正在寻找这样的东西:
(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)
)
更新
MS Access和Firebird都支持此功能.
MS Access and Firebird both support this feature.
推荐答案
Access数据库引擎(ACE，Jet等)在CHECK约束中支持子查询，但我不愿意将其称为 SQL DBMS，因为它不支持入门级标准SQL-92和Access CHECK约束，而MS和Access Team几乎没有记录.
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.
例如，我可以证明为受影响的每一行都检查了Access CHECK约束(SQL-92指定应在每个SQL语句之后检查它们)，但这是错误还是我们不知道的功能，因为没有文档可供参考.
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.
这是一个包含子查询的CHECK约束的非常简单的示例.它符合Full SQL-92，并在Access中运行良好.想法是将表限制为最多两行(以下SQL DDL要求
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
)
);
但是，这是可以在Access中创建的另一个SQL-92示例(某些有效的CHECK在Access中失败，并发生严重的崩溃，需要重新启动我的机器:(但不能正常运行)的想法是只允许表格中的两行(或零行:不对空表进行约束测试):
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
)
);
尝试在同一条语句中插入两行，例如(假设表T1至少有一行):
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;
但是，这会导致CHECK咬人.这种(和进一步的测试)意味着CHECK在将每一行添加到表之后都进行了测试，而SQL-92指定在SQL语句级别上对约束进行测试.
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.
当您考虑到Access2010之前它没有任何触发器功能并且某些经常使用的表将没有真正的功能时，Access具有真正的表级CHECK约束也就不足为奇了.键(例如，有效状态时态表中的已排序"键).请注意，Access2010触发器遭受的错误/功能与在行级别(而不是语句级别)进行测试的错误/功能相同.
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.
以下是VBA，用于重现上述两种情况.复制并粘贴到任何VBA/VB6标准.bas模块(例如，使用Excel)中，无需引用.在您的temp文件夹中创建一个新的.mdb，创建表，数据和测试约束是否起作用的提示(提示:设置断点，逐步执行代码，阅读注释):
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
这篇关于哪些SQL数据库在CHECK约束中支持子查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
