Внешний ключ и ограничение проверки целостности

Я создаю систему, которая является центральным репозиторием для хранения данных из ряда других систем. Процесс синхронизации необходим для обновления центрального репозитория при обновлении данных других систем. Там будет таблица sync_action, чтобы определить, с какой системой необходимо синхронизировать центральное репо, и тип требуемой синхронизации. Есть набор определенных действий, которые вряд ли будут изменены. Ниже представлена ​​упрощенная система.

На мой взгляд, я могу подойти к этому двумя способами:

Вариант 1). Имейте Action таблицу, в которой доступны 3 действия. Имейте sync_action таблицу, которая использует внешний ключ для ссылки на необходимые действия.

Таблица: Система

ID Description
 1 Slave System 1
 2 Slave System 2

Таблица: Действие

ID  Description
 1  Insert
 2  Update
 3  Delete

Таблица: Sync_action

ID  Action  System
 1     1       1
 2     2       1

Вариант 2). Вместо внешнего ключа используйте ограничение проверки для столбца sync_action.action, чтобы можно было вставить только действия Insert/Update/Delete.

Таблица: Sync_action

ID  Action  System
1   Insert    1
2   Update    1

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

Ваше здоровье


constraints database-design referential-integrity
person Jeremy Power    schedule 19.02.2012    source источник
comment
Я обычно предпочитаю FK и отношения между таблицами, потому что: они более заметны (например, отображаются на диаграммах отношений), и они более расширяемы (зависят от данных, а не от условия, и могут содержать дополнительную информацию), и они часто более сопоставимы (чтобы какой бы ORM вы ни выбрали). В подобных случаях я также рассматриваю возможность использования несуррогатного PK (на самом деле это статическая таблица данных), возможно, только одного значимого символа, такого как I, U, D. Если в другой таблице, то можно также есть столбцы самодокументируемых этикеток.   -  person    schedule 19.02.2012
comment
Добро пожаловать в StackOverflow: если вы публикуете код, XML или образцы данных, пожалуйста выделите эти строки в текстовом редакторе и нажмите кнопку образцов кода ({ }) на панели инструментов редактора, чтобы красиво отформатировать и выделить синтаксис !   -  person marc_s    schedule 19.02.2012
comment
Согласен с pst - а если нужно добавить четвертое, пятое действие ?? Если у вас есть отдельная Action таблица, это так же просто, как добавить строку. Если у вас есть проверочные ограничения, вам нужно отказаться от них и воссоздать их - это больше работы и больше хлопот. Я не вижу хороших аргументов против наличия отдельной Action таблицы и обеспечения ссылочной целостности с использованием ограничения FK - базы данных хороши в этом! (это их основной бизнес!)   -  person marc_s    schedule 19.02.2012

Ответы (2)


arrow_upward
8
arrow_downward

Комментаторы, кажется, единодушно соглашаются:

Обычно лучше иметь ограничение FOREIGN KEY для (более или менее статической) справочной таблицы. Причины:

  • Ограничение легко «расширить». Чтобы добавить или удалить параметр, вам нужно только добавить или удалить строку из таблицы ссылок. Вам не нужно отказываться от ограничения и воссоздавать его. Более того, если у вас есть такое же ограничение в аналогичных столбцах в других таблицах.

  • Вы можете прикрепить дополнительную информацию (больше столбцов), которая может быть прочитана приложениями при необходимости.

  • ORM могут лучше справляться с этими ограничениями. Им просто нужно прочитать таблицу, а не метаданные.

  • Если вы хотите изменить коды действий, каскадные эффекты позаботятся об изменениях в других (возможно, многих) таблицах. Нет необходимости писать запросы UPDATE.

  • Одна конкретная СУБД еще не реализовала CHECK ограничений (позор), хотя у нее есть ограничения FK.

Как упоминалось в @pst (и я очень предпочитаю этот подход), вы можете использовать разумный код вместо суррогатного целочисленного идентификатора. Итак, ваша таблица может быть:

Таблица: Система

SystemID Description
 1        Slave System 1
 2        Slave System 2

Таблица: Действие

ActionCode Description
 I          Insert
 U          Update
 D          Delete

Таблица: SyncAction

ID  ActionCode  SystemID
 1     I          1
 2     U          1
person ypercubeᵀᴹ    schedule 19.02.2012
comment
мы разработали подобную систему на моем текущем рабочем месте, и она работает довольно хорошо. тип первичного ключа указывает, является ли таблица справочными данными (нецелое число pk, обновляется вручную техническими специалистами) или обычными данными (целочисленный суррогат pk, может управляться приложениями). - person araqnid; 19.02.2012

arrow_upward
4
arrow_downward

Я думаю, вы путаете разницу между ограничением внешнего ключа и ограничением проверки.

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

Если мы рассмотрим таблицу users со столбцами user_id, user_name, address_id, join_date, active, last_active_month; Я понимаю, что это не обязательно лучший способ делать что-то, но он послужит тому, что я пытаюсь донести.

В этом случае совершенно нелепо иметь address_id в качестве ограничения. Этот столбец может иметь любое количество значений. Однако active, если мы хотим, чтобы логическое значение y/n могло иметь только два возможных значения, а last_active_month может иметь только 12 возможных значений. В обоих случаях иметь внешний ключ совершенно нелепо. Существует только определенное количество значений, и по определению данных, которые вы включаете, эти значения не могут измениться.

В вашем случае, хотя вы можете использовать ограничение проверки, если вы не можете быть абсолютно уверены, что число actions никогда не изменит внешний ключ Это правильный путь.

По немного другому вопросу, и, как упоминал @pst, я вижу, что вас съел суррогатный ключевой монстр. Хотя это может привести к повышению производительности, в таблице того размера, который вы предполагаете (3 значения, insert / update / delete) или даже большего размера, все, что он служит, - это скрыть то, чего вы пытаетесь достичь. .

На это непросто смотреть

ID  Action  System
 1     1       1
 2     2       1

и посмотрите, что происходит, но:

ID  Action  System
 1  insert     1
 2  update     1

читать намного легче; вы также можете подумать о том, чтобы сделать то же самое для столбца system - я, вероятно, так и сделал бы, хотя количество возможных значений здесь немного увеличивается. Только мои личные мысли по этому поводу ...

person Ben    schedule 19.02.2012