Вложенные запросы в проверочных ограничений

У меня есть таблица, разработанная в SQL-Server 2008 R2.

У меня есть столбец в этой таблице, который нужно сверить с другой таблицей при вставке данных. 

ALTER TABLE Table1
        WITH CHECK ADD CONSTRAINT CK_Code
        CHECK (MyField in (Select Field From Table2))

Это вызывает ошибку

подзапросы в этом контексте недопустимы. Допускаются только скалярные выражения.

Я рассмотрел этот вопрос о ограничении проверки - подзапросы не допускаются в этом контексте.

Есть ли какой-либо способ достичь этого без с помощью спускового крючка?

4 25
sql sql-server-2008 check-constraints

4 ответа:

Обратите внимание, что на самом деле вам нужно ограничение внешнего ключа. Тем не менее, чтобы получить "запрос" в проверку, вы можете написать функцию, которая содержит запрос и выводит скалярное значение, а затем использовать эту функцию в ограничении проверки.

CREATE FUNCTION myFunction (
    @field DATATYPE(?)
)
RETURNS VARCHAR(5)
AS
BEGIN
    IF EXISTS (SELECT* FROM Table2 WHERE MYFIELD = @field)
        return 'True'
    return 'False'
END

Что-то вроде этого. Не тестировать.

Затем вы можете добавить его в свой чек, как это

ALTER TABLE Table1
    WITH CHECK ADD CONSTRAINT CK_Code
    CHECK (myFunction(MYFIELD) = 'True')
35

Вы не можете иметь подзапросы внутри ограничений проверки. Что вы можете сделать, так это использовать UDF, который возвращает скалярное значение внутри ограничения check.

Шаг 1: создайте таблицу 

USE CTBX
GO

CREATE TABLE RawMaterialByGender 
(
RMGID int primary key identity(1,1),
RMID smallint foreign key references RawMaterialMaster(RMID),
LeveLMasterID smallint foreign key references LevelMaster(LevelTextID),
IsDeleted bit
)

Шаг 2: Создайте UDF, который возвращает скаляр

Create FUNCTION [dbo].[IsValidLevelMasterGender](@LevelMasterID smallint)

    RETURNS bit
    AS
    BEGIN
     DECLARE @count smallint;
     DECLARE @return bit;

     SELECT @count = count(LevelTextID)      
     FROM [LevelMaster]
     WHERE LevelCategoryID = 3 AND IsActive = 1 AND LevelTextID=@LevelMasterID

     IF(@count = 0)
     SET @return = 'false';
     ELSE
     SET @return = 'true';

     RETURN @return;

    END;
    GO

Шаг 3: Измените таблицу, чтобы добавить ограничение проверки

ALTER TABLE RawMaterialByGender 
ADD CONSTRAINT check_LevelMasterID CHECK (dbo.IsValidLevelMasterGender(LeveLMasterID) = 'true')
6
ALTER TABLE Table1
ADD CONSTRAINT FK_Table1_Code FOREIGN KEY (MyField)
REFERENCES Table2 (Field) ;

Ref: http://msdn.microsoft.com/en-us/library/ms190273.aspx
Примечание: Я не проверял выше синтаксис.

3

Прежде всего в вашем примере вам явно нужно ограничение FK.

Еще одна возможность-использовать view с WITH CHECK OPTION и предоставлять доступ пользователю через него: 

CREATE TABLE Table1(i INT PRIMARY KEY, CK_Code CHAR(1));
CREATE TABLE Table2(Field CHAR(1));
INSERT INTO Table2(Field) VALUES ('A'),('B'), ('C');
GO

CREATE VIEW v_Table1
AS
SELECT *
FROM Table1
WHERE CK_code IN (SELECT Field FROM Table2)  -- here goes your subquery check
WITH CHECK OPTION;

При попытке вставить данные, нарушающие ваше "ограничение", например: 

INSERT INTO v_Table1(i, CK_Code)
VALUES(10, 'D');

Вы получите:

Попытка вставить или обновить завершилась неудачей, так как целевое представление либо указывает параметр с проверкой, либо охватывает представление, задающее параметр с проверкой Опция и одна или несколько строк, полученных в результате операции did нет подпадают под ограничение Check вариант.

Заявление было прекращено.

LiveDemo

0