Блог

Главная - Вопросы по программированию - Это ошибка при слиянии, из-за которой не удается правильно реализовать ВНЕШНИЙ КЛЮЧ?

Это ошибка при слиянии, из-за которой не удается правильно реализовать ВНЕШНИЙ КЛЮЧ?

#sql-server #sql-server-2008 #sql-server-2008-r2

#sql-server #sql-server-2008 #sql-server-2008-r2

Вопрос:

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

 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 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.

Это ошибка при слиянии или я что-то упускаю?

Комментарии:

1. Похоже, что он передает пустую строку как type . Если я удалю ограничение проверки и добавлю строку INSERT INTO dbo.Vehicles(ID, [Type]) VALUES (3, ''); , то Merge для VALUES(3, 'Some Data');

2. @MartinSmith — хотя это постоянная константа…

3. @JNK — Да, я не говорил, что это правильное поведение (оно кажется явно неправильным), я просто сказал, что это то, что, похоже, происходит.

4. @MartinSmith: Я согласен с вашим выводом, хороший улов!

5. Обходным путем, если вы действительно хотите использовать слияние, было бы иметь обычный тип столбца в cars с not null и ограничение по умолчанию, подобное этому [Type] varchar(5) not null default 'Car' , а также добавить ограничение проверки CONSTRAINT Cars_CHK_ValidType CHECK([Type] = 'Car') . Я думаю, что это привело бы к тому же поведению, что и сохраняемый столбец, и слияние работает просто отлично.

Ответ №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'