Таблица с 1 из нескольких FK против нескольких каскадных путей

Question

Рассмотрим следующие 3 таблицы:

create table FOO (
    foo_index Numeric(38, 0)
        primary key clustered
        constraint foo_foo_index_nn not null
        identity(1,1)
)

create table BAR (
    bar_index Numeric(38, 0)
        primary key clustered
        constraint bar_bar_index_nn not null
        identity(1,1),
    foo_index Numeric(38, 0)
        constraint bar_foo_index_nn not null
        constraint bar_foo_index_fk references FOO(foo_index)
        on delete cascade
)

create table BAZ (
    baz_index Numeric(38, 0)
        primary key clustered
        constraint baz_baz_index_nn not null
        identity(1,1),
    foo_index Numeric(38, 0)
        constraint baz_foo_index_fk references FOO(foo_index)
        on delete cascade,
    bar_index Numeric(38, 0)
        constraint baz_bar_index_fk references BAR(bar_index)
        on delete cascade
)

каждый bar требуется ссылаться на foo, Хотя это не очевидно из создания таблицы, цель для каждого baz ссылаться на foo или bar, но никогда не оба.

Если вы попытаетесь создать схему выше, вы получите эту ошибку:
Introducing FOREIGN KEY constraint 'baz_bar_index_fk' on table 'BAZ' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Как только SQL Server обнаруживает несколько каскадных путей (в этом случае baz ссылаясь как на bar и foo bar ссылки и что 'foo` удаляется) вместо того, чтобы пытаться обнаружить каскадные циклы, он просто не может быть безопасным.

Однако этого не произойдет при реальном использовании, поскольку план состоит в том, чтобы только один из двух внешних ключей был ненулевым. Есть ли какой-то способ, которым я могу указать, что ограничение использования для SQL Server, так что это позволит оба FK для удаления на каскад?

Answer 1

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

Мои предложения

1) Измените NUMERIC(38,0) на INT, это все столбцы идентификаторов, и он будет увеличиваться на единицу каждый раз, я не вижу смысла иметь этот столбец как NUMERIC 38, который будет 17-байтовым типом данных. INT даст вам более 2 миллиардов строк в этой таблице, bigint даст вам 2^63 строки, не говоря уже о пределе bigint, если ваша таблица когда-нибудь достигнет 2 миллиардов строк, вы должны рассмотреть ее разделение, не добавляя больший тип данных, чтобы добавить больше ряды, с такой огромной таблицей ваша производительность достигнет дна.

2) Как вы упомянули в таблице BAZ, одна строка будет ссылаться только на BAR или FOO. Я не вижу смысла в наличии двух столбцов, добавьте один столбец для ссылки на любую из таблиц и добавьте другой столбец с наименьшим типом данных (a Хороший кандидатный тип данных Бит), чтобы определить, к какой таблице относятся ссылки.

Я бы тебя создал BAZ стол как то так.....

create table BAZ 
(
    baz_index INT primary key clustered
              constraint baz_baz_index_nn not null identity(1,1),
    bar_Fo_index INT,
    Is_Bar       BIT     --<--  1 is bar 0 is foo      
) 

Каскадная операция

Для каскадирования UPDATES, DELETES или INSERTS используют хранимые процедуры.