Механизм базы данных Access (ACE, Jet и т. д.) поддерживает подзапросы в ограничениях CHECK, но я не решаюсь назвать его СУБД SQL, потому что он не поддерживает начальный уровень Standard SQL-92, а ограничения Access CHECK едва задокументировано MS и группой доступа.
Например, я могу продемонстрировать, что ограничения доступа CHECK проверяются для каждой затрагиваемой строки (в SQL-92 указано, что они должны проверяться после каждого оператора SQL), но является ли это ошибкой или функцией, мы не знаем, поскольку нет документации по этому вопросу. Ссылаться на.
Вот очень простой пример ограничения CHECK, содержащего подзапрос. Он совместим с Full SQL-92 и хорошо работает в Access. Идея состоит в том, чтобы ограничить таблицу максимум двумя строками (следующий SQL DDL требует Режим запроса ANSI-92, например, используйте соединение 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.
Неудивительно, что в Access действительно есть ограничения CHECK на уровне таблиц, если учесть, что до Access2010 у него не было никаких функций триггера, а в противном случае некоторые часто используемые таблицы не имели бы истинного ключа (например, ' sequenced' в темпоральной таблице с допустимым состоянием). Обратите внимание, что триггеры 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
person
onedaywhen
schedule
01.06.2011
