Перекрывающиеся иностранные ключи и каскад при удалении

Question

Допустим, у меня есть 4 таблицы, A,B,C,D.

А имеет первичный ключ ID.
B имеет первичный ключ (ID, A.ID), где A.ID является внешним ключом с CASCADE ON DELETE.
C имеет первичный ключ (ID, A.ID), где A.ID является внешним ключом с CASCADE ON DELETE.
D имеет первичный ключ (ID, A.ID, B.ID, C.ID), где (A.ID, B.ID) и (A.ID, C.ID) являются внешними ключами.

Есть ли какой-нибудь способ CASCADE ON DELETE для обоих внешних ключей D? Попытка каскадирования обоих внешних ключей дает мне ошибку "множественные каскадные пути" из-за каскадного удаления обоих внешних ключей из A.ID, но мне нужно удалить строку из D, если строка B.ID или C.ID будет удалена.

РЕДАКТИРОВАТЬ:

Таблицы DeviceTypes, DeviceInstances, DeviceDataElements, DeviceDataRecords

DeviceTypes имеет первичный ключ ID.

DeviceInstances имеет первичный ключ (ID, DeviceTypes.ID), где DeviceTypes.ID является внешним ключом с CASCADE ON DELETE.

DeviceDataElements имеет первичный ключ (ID, DeviceTypes.ID), где DeviceTypes.ID является внешним ключом с CASCADE ON DELETE.

D имеет первичный ключ (ID, Devices.ID, DeviceElements.ID, DeviceInstances.ID), причем (DeviceTypes.ID, DeviceElements.ID) и (DeviceTypes.ID, DeviceInstances.ID) являются внешними ключами.

т. е. DeviceRecord содержит точку данных для DeviceDataElement определенного DeviceInstance.

Answer 1

Я могу только догадываться о предметной области, но что касается меня, DeviceTypes.ID не должен быть частью первичного ключа в DeviceInstances и DeviceDataElements. Этот столбец не требуется для уникальной идентификационной записи, например, в DeviceInstances. Невозможно иметь DeviceInstance с тем же DeviceInstanceID, но другим DeviceTypeID.

Также я не думаю, что удаления из DeviceTypes должны быть каскадными. Это не обычная (и опасная) операция по удалению из DeviceType. Если вам нужно это реализовать, напишите хранимую процедуру и удалите из связанных таблиц в правильном порядке (DeviceDataRecords, DeviceDataElements, DeviceInstances, DeviceTypes). Другой способ реализовать это - пометить DeviceType как удаленный, а не удалить физически. В этом случае вы никогда не потеряете свои данные.

Точно так же удаление из DeviceDataElements тоже под вопросом. Я не думаю, что DataElements когда-либо изменится для некоторого Типа.

В этом проекте вы должны контролировать со стороны приложения, что DeviceRecord содержит только элементы данных, разрешенные типом экземпляра. Это очень распространенный и естественный способ справиться с этой ситуацией (сделать это на стороне приложения или бизнес-логики). С логической точки зрения DeviceDataRecords содержит тот факт, что в некотором экземпляре есть некоторый элемент DataElement (некоторого значения). Неестественно проверять типы в этой таблице. Не стоит делегировать эти проверки SQL Server. В случае, если на стороне сервера доступна только опция, вы можете рассмотреть триггеры для этой задачи, поэтому на INSERT в записях вы можете проверить это правило (это тоже не очень хороший вариант).

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

CREATE TABLE DeviceTypes(DeviceTypeID int not null identity primary key);
go

CREATE TABLE DeviceInstances (DeviceInstanceID int not null identity primary key, DeviceTypeID int not null)
ALTER TABLE DeviceInstances ADD CONSTRAINT FK_DeviceInstances_DeviceTypes FOREIGN KEY (DeviceTypeID) REFERENCES DeviceTypes(DeviceTypeID) 
go

CREATE TABLE DeviceDataElements (DeviceDataElementID int not null identity primary key, DeviceTypeID int not null)
ALTER TABLE DeviceDataElements ADD CONSTRAINT FK_DeviceDataElements_DeviceTypes FOREIGN KEY (DeviceTypeID) REFERENCES DeviceTypes(DeviceTypeID) 
go

CREATE TABLE DeviceDataRecords (RecordID int not null identity primary key, DeviceInstanceID int not null, DeviceDataElementID int not null)
ALTER TABLE DeviceDataRecords ADD CONSTRAINT FK_DeviceDataRecords_DeviceInstances FOREIGN KEY (DeviceInstanceID) REFERENCES DeviceInstances(DeviceInstanceID) on delete cascade
ALTER TABLE DeviceDataRecords ADD CONSTRAINT FK_DeviceDataRecords_DeviceDataElements FOREIGN KEY (DeviceDataElementID) REFERENCES DeviceDataElements(DeviceDataElementID) on delete cascade