哪些 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 团队记录.
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 约束的非常简单的示例.它符合完整的 SQL-92 并且在 Access 中运行良好.这个想法是将表限制为最多两行(以下 SQL DDL 需要 ANSI-92 查询模式 例如使用 ADO 连接，例如 Access.CurrentProject.Connection):
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
)
);
然而，这里是 SQL-92 的另一个示例，可以在 Access 中创建(一些有效的 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)中，无需参考.在您的临时文件夹中创建一个新的 .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屋！
