Суб-запросы в контрольном ограничении

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

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

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

Это приведет к ошибке

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

Я рассмотрел этот вопрос о Проверить ограничение. Подзапросы в этом контексте запрещены.

Есть ли способ достичь этого без использования триггера?

Ответ 1

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

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')

Ответ 2

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

Шаг 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 [email protected]

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

     RETURN @return;

    END;
    GO

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

ALTER TABLE RawMaterialByGender 
ADD CONSTRAINT check_LevelMasterID CHECK (dbo.IsValidLevelMasterGender(LeveLMasterID) = 'true')

Ответ 3

ALTER TABLE Table1
ADD CONSTRAINT FK_Table1_Code FOREIGN KEY (MyField)
REFERENCES Table2 (Field) ;

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

Ответ 4

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

Другая возможность - использовать представление с 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');

Вы получите:

Не удалось выполнить попытку вставки или обновления, потому что целевой вид либо указывает WITH CHECK OPTION, либо охватывает представление, которое указывает WITH CHECK ОПЦИЯ и одна или несколько строк, полученных в результате операции, не подпадают под ограничение CHECK OPTION.

Оператор завершен.

LiveDemo