Является ли это ошибкой в MERGE, не имея возможности правильно использовать FOREIGN KEY?

Я использую следующие таблицы для реализации подтипов, что является очень распространенным подходом:

CREATE TABLE dbo.Vehicles(
    ID INT NOT NULL, 
    [Type] VARCHAR(5) NOT NULL,
    CONSTRAINT Vehicles_PK PRIMARY KEY(ID),
    CONSTRAINT Vehicles_UNQ_ID_Type UNIQUE(ID, [Type]),
    CONSTRAINT Vehicles_CHK_ValidTypes CHECK([Type] IN ('Car', 'Truck'))
);
GO

CREATE TABLE dbo.Cars(ID INT NOT NULL,
    [Type] AS CAST('Car' AS VARCHAR(5)) PERSISTED,
    OtherData VARCHAR(10) NULL,
    CONSTRAINT Cars_PK PRIMARY KEY(ID),
    CONSTRAINT Cars_FK_Vehicles FOREIGN KEY(ID, [Type])
        REFERENCES dbo.Vehicles(ID, [Type])
);
GO
-- adding parent rows
INSERT INTO dbo.Vehicles(ID, [Type]) 
VALUES(1, 'Car'),
(2, 'Truck');

У меня нет проблем с добавлением дочерней строки через INSERT, как показано ниже:

INSERT INTO dbo.Cars(ID, OtherData)
VALUES(1, 'Some Data');

DELETE FROM dbo.Cars;

Удивительно, что MERGE не может добавить одну дочернюю строку:

MERGE dbo.Cars AS TargetTable
    USING 
        ( SELECT    1 AS ID ,
                    'Some Data' AS OtherData
        ) AS SourceData
    ON  SourceData.ID = TargetTable.ID
    WHEN NOT MATCHED 
        THEN INSERT (ID, OtherData)
        VALUES(SourceData.ID, SourceData.OtherData);

Msg 547, Level 16, State 0, Line 1
The MERGE statement conflicted with the FOREIGN KEY constraint "Cars_FK_Vehicles". The conflict occurred in database "Test", table "dbo.Vehicles".
The statement has been terminated.

Является ли это ошибкой в MERGE или мне что-то не хватает?

Ответ 1

Похож на определенную ошибку в MERGE для меня.

План выполнения имеет оператор Clustered Index Merge и должен выводить [Cars].ID,[Cars].Type для проверки на таблицу Vehicles.

Эксперимент показывает, что вместо передачи значения "Car" в качестве значения Type он передает пустую строку. Это можно увидеть, удалив ограничение проверки на транспортных средствах, а затем вставив

INSERT INTO dbo.Vehicles(ID, [Type]) VALUES (3, '');

Следующий оператор теперь работает

MERGE dbo.Cars AS TargetTable
    USING 
        ( SELECT    3 AS ID ,
                    'Some Data' AS OtherData
        ) AS SourceData
    ON  SourceData.ID = TargetTable.ID
    WHEN NOT MATCHED 
        THEN INSERT (ID, OtherData)
        VALUES(SourceData.ID, SourceData.OtherData);

Но конечным результатом является то, что он вставляет строку, нарушающую ограничение FK.

Автомобили

ID          Type  OtherData
----------- ----- ----------
3           Car   Some Data

Транспортные средства

ID          Type
----------- -----
1           Car
2           Truck
3

Проверка ограничений сразу после

DBCC CHECKCONSTRAINTS  ('dbo.Cars')

Показывает оскорбительную строку

Table         Constraint          Where
------------- ------------------- ------------------------------
[dbo].[Cars]  [Cars_FK_Vehicles]  [ID] = '3' AND [Type] = 'Car'

Ответ 2

Причина, по которой кажется, что итератор Merge выводит пустую строку для столбца Type, интересен. Оптимизатор распознает, что Type является постоянным значением и применяет переписывание, которое удаляет этот столбец из потока, добавляя его позже в виде Scute Scute. Вы можете увидеть это в действии, добавив предложение OUTPUT в оператор MERGE, чтобы исправить значение inserted.[Type]. Без предложения OUTPUT нет необходимости в вычислении Scalar, поэтому он оптимизирован, оставляя нам форму плана, показанную в исходном примере.

Ошибка возникает, когда что-то между итератором Merge (которое также может быть слиянием таблиц, а не кластеризованным счётом индексов) и Scale Scute, требует значения столбца [Type]. Поскольку он был удален из потока (несмотря на то, что он отображается в плане как результат слияния), мы заканчиваем ссылкой на то, что не существует, и это порождает пустую строку. В другом мире SQL Server вместо этого утверждал бы что-то вроде нарушения нулевого указателя, но это другая история.

Эта проблема немного исправлена в SQL Server 2012, но все еще есть ошибка. Я говорю "немного" исправлено, потому что переписывание, которое удаляет столбец с постоянным значением, отключается (так что проверка FK получает реальное значение для поиска, а не пустую строку), но Scute Scute Comp, который добавляет строку "Car" назад в поток все еще появляется, если добавлен OUTPUT inserted.[Type]. В идеальном мире план просто выводит значение, предоставленное слиянием, а не переустанавливает константу. Во всяком случае, это не все, что важно (он просто показывает, что реализация по-прежнему немного шелушится по краям), но по-прежнему существует ошибка, связанная с удалением ссылки на столбец:

Он воспроизводится только в SQL 2012 с табличной переменной (все остальные типы таблиц в порядке), но воспроизводит все выпущенные версии SQL Server с любым типом табличного объекта:

DECLARE @Bug TABLE
(
    id INTEGER PRIMARY KEY, 
    data AS 'X' PERSISTED,
    CHECK (data = 'A')
)

MERGE @Bug AS b USING (VALUES(1)) AS u (id) ON u.id = b.id
WHEN NOT MATCHED THEN INSERT (id) VALUES (u.id)
OUTPUT INSERTED.data;

Дело в том, что ограничение CHECK пропускается - оператор Assert, который проверяет его, добавляется в план, но затем оптимизируется, когда оптимизатор видит столбец с постоянным значением и применяет его переписывание. Удаление Assert позволяет добавить значение "X" в таблицу, даже если оно нарушает ограничение CHECK. Как я уже сказал, вы можете воспроизвести это в 2008 R2 и ранее с реальными и временными таблицами - в 2012 году он будет только ошибок с табличными переменными.

Пол