Как реализовать ссылочную целостность в подтипах

Question

Как реализовать ссылочную целостность в подтипах

У меня есть следующие таблицы в реляционной базе данных:

[Sensor]
LocationId [PK / FK -> Location]
SensorNo [PK]

[AnalogSensor]
LocationId [PK/FK -> Sensor]
SensorNo [PK/FK -> Sensor]
UpperLimit
LowerLimit

[SwitchSensor]
LocationId [PK/FK -> Sensor]
SensorNo [PK/FK -> Sensor]
OnTimeLimit

[Reading]
LocationId [PK/FK -> Sensor]
SensorNo [PK/FK -> Sensor]
ReadingDtm [PK]

[ReadingSwitch]
LocationId [PK/FK -> Reading]
SensorNo [PK/FK -> Reading]
ReadingDtm [PK/FK -> Reading]
Switch

[ReadingValue]
LocationId [PK/FK -> Reading]
SensorNo [PK/FK -> Reading]
ReadingDtm [PK/FK -> Reading]
Value

[Alert]
LocationId [PK/FK -> Reading]
SensorNo [PK/FK -> Reading]
ReadingDtm [PK/FK -> Reading]

По сути, ReadingSwitch и ReadingValue являются подтипами Reading, а SwitchSensor и AnalogSensor являются подтипами Sensor. Чтение может быть либо значением SwitchReading, либо ValueReading - оно не может быть и тем, и другим, а Sensor может быть либо AnalogSensor, либо SwitchSensor.

Единственный способ, которым я до сих пор сталкивался, это сделать здесь.

Там, безусловно, должен быть лучший способ сделать такие вещи.

Единственный другой способ, который я могу придумать, - это не иметь подтипы, а полностью расширять все:

[SwitchSensor]
LocationId [PK/FK -> Location]
SensorNo [PK]

[AnalogSensor]
LocationId [PK/FK -> Location]
SensorNo [PK]

[SwitchReading]
LocationId [PK/FK -> SwitchSensor]
SensorNo [PK/FK -> SwitchSensor]
ReadingDtm
Switch

[AnalogReading]
LocationId [PK/FK -> AnalogSensor]
SensorNo [PK/FK -> AnalogSensor]
ReadingDtm
Value

[AnalogReadingAlert]
LocationId [PK/FK -> AnalogReading]
SensorNo [PK/FK -> AnalogReading]
ReadingDtm [PK/FK -> AnalogReading]

[SwitchReadingAlert]
LocationId [PK/FK -> SwitchReading]
SensorNo [PK/FK -> SwitchReading]
ReadingDtm [PK/FK -> SwitchReading]

Что может быть не так уж и плохо, но у меня также есть таблицы, которые ссылаются на таблицу оповещений, поэтому их тоже придется дублировать:

[AnalogReadingAlertAcknowledgement]
...
[AnalogReadingAlertAction]
...
[SwitchReadingAlartAcknowledgement]
...
[SwitchReadingAlartAction]

и т.п.

Имеет ли эта проблема какой-либо смысл для кого-либо??

9

sql database relational-database foreign-key-relationship

Источник

user391455 04 фев '11 в 10:17

2 ответа

Решение

Второй вариант также чреват проблемами - например, для датчиков (и при условии, что SensorNo является суррогатным ключом), поскольку у вас нет базовой таблицы, суррогат SensorNo либо не является уникальным среди таблиц подклассов, если вы не используете Механизм klugey для выдачи ключей по всем таблицам подклассов (или использование guid).

Это усилилось бы, если бы у вас был пользовательский интерфейс, который "комбинирует" различные типы датчиков, например, список, показывающий объединение аналоговых и переключающих датчиков.

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

например, создать процедуры вставки для различных подтаблиц, которые вставляют соответствующие записи базы и подкласса в единицу работы. Вы можете пойти дальше, отменив привилегии INSERT для ЛЮБОЙ из таблиц, что приведет к вставке через SPROC.

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

1

Источник

user314291 04 фев '11 в 11:15

Другие вопросы по тегам sql database relational-database foreign-key-relationship

user484814 04 фев '11 в 13:09 2011-02-04 13:09 · Accepted Answer · 2011-02-04 13:09

Ничего из этого не нужно, особенно не удваивая столы. Это чистое безумие.

Вступление

Поскольку стандарт моделирования реляционных баз данных (IDEF1X) широко используется более 25 лет (по крайней мере, в сегменте рынка с высоким качеством и высокой производительностью), я использую эту терминологию. Date & Darwen, ~~несмотря на то~~, ~~что они~~ согласились с большой работой, проделанной ими для подавления реляционной модели, они не знали о IDEF1X до тех пор, пока я не довел об этом до их сведения в 2009 году, и поэтому у нас есть новая терминология ² для стандартной терминологии, которую мы имеем использовал в течение десятилетий. Кроме того, новая терминология охватывает не все случаи, как это делает IDEF1X. Поэтому я использую установленную стандартную терминологию и избегаю новой терминологии.

Даже концепция "распределенного ключа" не может распознать основополагающие обычные отношения PK::FK, их реализацию в SQL и их мощь.
Реляционная, и поэтому IDEF1X, концепция - это Идентификаторы и их Миграция.
Конечно, поставщики не совсем на высоте, и у них есть странные вещи, такие как "частичные индексы" и т. Д., Которые совершенно не нужны, когда поняты основы. Но знаменитые "академики" и "теоретики" придумали неполные новые концепции, когда концепция была стандартизирована и полностью рассмотрена 25 лет назад... это неожиданно и неприемлемо.

Предостережение

SQL IEC/ISO/ANSI едва обрабатывает 3NF Кодда (Date & Darwen's 5NF) адекватно и вообще не поддерживает структуры подтипа типа-типа; Нет никаких декларативных ограничений для этого (и должно быть).

Поэтому, чтобы обеспечить полный набор правил, выраженных в модели данных, как Basetype::Subtype, так и Subtype::Basetype, нам нужно немного поиграться с CHECK CONSTRAINT и т. д. (я избегаю использования триггеров по ряду причин).

Рельеф

Однако я все это учитываю. Для того чтобы я мог эффективно предоставлять услугу моделирования данных в Stackru без необходимости предварять это полным дискурсом, я намеренно предоставляю модели, которые могут быть реализованы способными людьми, использующими существующий SQL и существующие ограничения, в любой степени, в которой они нуждаются. Он уже упрощен и содержит общий уровень исполнения. Если есть какие-либо вопросы, просто спросите, и вы получите.

Мы можем использовать как иллюстративный рисунок в связанном документе, так и вашу полностью совместимую с IDEF1X модель данных датчика.

Читатели, которые не знакомы со стандартом реляционного моделирования, могут найти нотацию IDEF1X полезной. Читателям, которые считают, что база данных может быть сопоставлена с объектами, классами и подклассами, рекомендуется, чтобы дальнейшее чтение могло причинить вред. Это дальше, чем читали Фаулер и Амблер.

Реализация ссылочной целостности для базового типа-подтипа

Существует два типа структур Basetype-Subtype.

Эксклюзивный подтип

Исключительно означает, что для каждой строки базового типа должна быть одна и только одна строка подтипа. В терминах IDEF1X должен быть столбец Discriminator в базовом типе, который идентифицирует существующую для него строку подтипа.

Для более чем двух подтипов это требуется, и я реализую столбец Discriminator.
Для двух подтипов, поскольку это легко получить из существующих данных (например, Sensor.IsSwitch является дискриминатором для Reading), Я не моделирую дополнительный явный столбец Дискриминатор для Reading, Тем не менее, вы можете следовать Стандарту к письму и применять дискриминатор.

Я рассмотрю каждый аспект подробно.

Столбец Дискриминатор нуждается в CHECK CONSTRAINT чтобы убедиться, что он находится в диапазоне значений, например: IN ("B", "C", "D"), IsSwitch это BIT, который равен 0 или 1, так что уже ограничен.
Поскольку PK базового типа определяет его уникальность, будет разрешена только одна строка базового типа; никакая вторая строка базового типа (и, следовательно, никакая вторая строка подтипа) не может быть вставлена.
- Таким образом, избыточный, полностью избыточный, дополнительный ненужный индекс для реализации индекса, такого как (PK, Discriminator) в базовом типе, как рекомендует ваша ссылка. Уникальность в PK, и, следовательно, PK плюс все будет уникальным).
- Для IDEF1X не требуется дискриминатор в таблицах подтипа. В Подтипе, который опять-таки ограничен уникальностью его PK, согласно модели, если Дискриминатор был реализован как столбец в этой таблице, каждая строка в нем будет иметь одинаковое значение для Дискриминатора (каждая Книга будет " Будь очень ReadingSwitch будет IsSwitch). Поэтому абсурдно использовать Дискриминатор как столбец в Подтипе. И снова, полностью избыточный, дополнительный ненужный Индекс для реализации Индекса, такого как (PK, Discriminator) в Подтипе: уникальность находится в PK, и, следовательно, PK плюс что-либо будет уникальным).
- Метод, идентифицированный в ссылке, является неуклюжим и раздутым (массовое дублирование данных без цели) способом реализации ссылочной целостности. Вероятно, есть веская причина, по которой автор не видел эту конструкцию где-либо еще. Это основной недостаток понимания SQL и его эффективного использования. Эти "решения" типичны для людей, которые следуют догме "SQL не может делать..." и, следовательно, не знают, что может делать SQL. Ужасы, вызванные слепыми "методами" Фаулера и Амблера, еще хуже.
Подтип PK - это также FK для Базового типа, то есть все, что требуется для гарантии того, что Подтип не существует без родительского Базового типа.
- Поэтому для любого данного PK, какой бы Базовый тип-Подтип не был вставлен первым, будет успешным; и какой бы Базовый тип-Подтип не был предпринят после этого, произойдет сбой. Поэтому в таблице подтипов не о чем беспокоиться (вторая строка базового типа или вторая строка подтипа для того же ПК запрещены).
  ,
SQL CHECK CONSTRAINT ограничивается проверкой вставленной строки. Нам нужно сравнить вставленную строку с другими строками, либо в той же таблице, либо в другой таблице. Поэтому требуется функция, определяемая пользователем.
- Напишите простой UDF, который проверит наличие PK и Discriminator в базовом типе, и вернет 1, если EXITS или 0, если NOT EXITS, Вам понадобится один UDF для каждого базового типа (не для каждого подтипа).
- В подтипе реализовать CHECK CONSTRAINT это вызывает UDF, используя PK (который является и Базовым типом и Подтипом) и значение Дискриминатора.
- Я реализовал это в десятках больших, реальных мировых баз данных на разных платформах SQL. Вот код функции, определенный пользователем, и код DDL для объектов, на которых он основан.
- Этот конкретный синтаксис и код протестированы на Sybase ASE 15.0.2 (они очень консервативны в отношении соответствия стандартам SQL).
- Мне известно, что ограничения для функций, определенных пользователем, различны для каждой платформы SQL. Тем не менее, это самый простой из простых, и AFAIK каждая платформа позволяет эту конструкцию. (Понятия не имею, что делают не-SQL.)
- да, конечно, эту хитрую маленькую технику можно использовать для реализации любого нетривиального правила данных, которое вы можете нарисовать в модели данных. В частности, чтобы преодолеть ограничения SQL. Обратите внимание на мое предостережение, чтобы избежать двусторонних ограничений (циклические ссылки).
Следовательно CHECK CONSTRAINT в подтипе гарантирует, что в базовом типе существует PK плюс правильный дискриминатор. Это означает, что существует только этот подтип для базового типа (PK).
- Любая последующая попытка вставить другой подтип (то есть нарушить исключающее правило) потерпит неудачу, потому что PK+Discriminator не существует в базовом типе.
- Любая последующая попытка вставить другую строку того же подтипа предотвращается уникальностью ограничения PK.
Единственный пропущенный бит (не упомянутый в ссылке) - это правило "каждый базовый тип должен иметь хотя бы один подтип" не применяется. Это легко описывается в транзакционном коде (я не советую ограничения в двух направлениях или триггеры); используйте правильный инструмент для работы.

Неисключительный подтип

Базовый тип (родительский) может содержать более одного подтипа (дочерний)

Не существует единого подтипа для идентификации.
- Дискриминатор не распространяется на неисключительные подтипы.
- Существование подтипа идентифицируется путем выполнения проверки существования таблицы подтипа с использованием базового типа PK.
Просто исключите CHECK CONSTRAINT это вызывает UDF выше.
- PRIMARY KEY, FOREIGN KEY и обычный диапазон CHECK CONSTRAINT s, адекватно поддерживать все требования для неисключительных подтипов.

Ссылка

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

Заметка

Меня тоже приняли постоянные ссылки СиДжея Дейта и Хью Дарвена на "развитие" реляционной модели. После многих лет взаимодействия, основанного на гору последовательных доказательств, я пришел к выводу, что их работа фактически сводит на нет ее. Они ничего не сделали для дальнейшей плодотворной работы д-ра Э. Ф. Кодда, реляционной модели и всего, что может повредить и подавить ее.
У них есть частные определения для реляционных терминов, что, конечно, серьезно мешает общению. У них есть новая терминология для терминов, которые мы использовали с 1970 года, чтобы показать, что они "изобрели" ее. Типично для мошенников и воров.

Ответ на комментарий

^{Этот раздел может быть пропущен всеми читателями, которые не комментируют.}

К сожалению, некоторые люди настолько учатся делать неправильные вещи (как советуют уроды, которые сдают за "теоретиков" в этом пространстве), при огромных дополнительных затратах, что даже если их направляют четко в правильном направлении, они не могут этого понять. Возможно, именно поэтому правильное образование не может быть заменено форматом вопросов и ответов.

Сэм: Я заметил, что этот подход не мешает кому-то использовать UPDATE изменить значение дискриминатора базового типа. Как это можно предотвратить? FOREIGN KEY + дубликат столбца Дискриминатор в подходе подтипов, кажется, преодолевает это.

Да. Этот метод не мешает кому-то использовать UPDATE изменить ключ или столбец в какой-то не связанной таблице, или головные боли, либо. Он отвечает на конкретный вопрос, и ничего больше. Если вы хотите запретить определенные команды DML или что-то еще, используйте средство SQL, предназначенное для этой цели. Все это выходит за рамки этого вопроса. В противном случае каждый ответ должен касаться каждой не связанной проблемы.

Ответ. Поскольку мы должны использовать Стандарты открытой архитектуры, доступные с 1993 года, все изменения в БД осуществляются только через транзакции ACID. Это означает, что прямой INSERT/UPDATE/DELETE все таблицы запрещены; данные сохраняют целостность и согласованность (терминология ACID). В противном случае, конечно, у вас кровоточащий беспорядок, такой как ваш например. и последствия. Эти уроды не понимают транзакций, они понимают только один файл INSERT/UPDATE/DELETE, Опять же, выходит за рамки. Если вам нужно больше деталей, пожалуйста, откройте новый вопрос, и я отвечу на него подробно.

Кроме того, FK+Duplicate D+Duplicate Index (и огромная стоимость в нем!) Ничего подобного не делает, я не знаю, откуда у вас "кажется".

dtheodor: Этот вопрос касается ссылочной целостности. Ссылочная целостность не означает "проверить, что ссылка действительна при вставке и забыть об этом". Это означает "сохранить действительность ссылки навсегда". Двойной дискриминатор + метод FK гарантирует эту целостность, а ваш подход UDF - нет. Это без сомнения, что UPDATE ы не должны нарушать ссылку.

Проблема здесь двоякая. Во-первых, вам необходимо базовое образование в других областях, касающихся реляционных баз данных и стандартов открытой архитектуры. Во-вторых, вам нужно перепрограммирование, потому что, хотя я дал вам ответ, вы его не понимаете, вы рабски повторяете культовую мантру, что этот конкретный метод не делает того, что делает массово неэффективный метод культа. Если не повторять мою просьбу открыть новый вопрос и, таким образом, дать полный ответ на ту другую область реляционных баз данных, которую вы, очевидно, не понимаете (никто из культа Дейта и Дарвена не понимает основ реляционных баз данных), я действительно не знаю что делать.

Хорошо, короткий ответ, который действительно относится к другому вопросу. Как дискриминатор в эксклюзивных подтипах защищен от недействительного обновления?

Ясность. Да, ссылочная целостность не означает "проверить, что ссылка действительна при вставке, и забыть об этом". Я не говорил, что это тоже значило.
- Ссылочная целостность означает ссылки в базе данных. FOREIGN KEY имеет целостность с PRIMARY KEY что это ссылки.
- Декларативная ссылочная целостность означает заявленные ссылки в базе данных... CONSTRAINT ИНОСТРАННЫЙ КЛЮЧ ... REFERENCES ...
  CONSTRAINT CHECK ...
  поддерживаются платформой СУБД, а не кодом приложения.
- Это также не означает "поддерживать достоверность ссылки навсегда".
Оригинальный Вопрос касается RI для Подтипов, и я ответил на него, предоставляя DRI.
- Необходимо подчеркнуть тот факт, что массово неэффективные структуры и дублированные таблицы, как "учат" ваши лидеры культа, не требуются.
Ваш вопрос не касается RI или DRI.
Ваш вопрос, хотя и задан неправильно, потому что вы ожидаете, что метод предоставит то, что метод не предоставляет, и вы не понимаете, что ваше требование выполняется другими способами, защищен ли дискриминатор в эксклюзивных подтипах от недействительного обновления?
Ответ заключается в том, чтобы использовать стандарты открытой архитектуры, которые мы должны использовать с 1993 года. Это предотвращает все недействительные UPDATE s. Если вам надоело читать связанные документы и понимать их, ваша проблема не является проблемой, она не существует. Это короткий ответ.
Но вы не поняли короткий ответ в первый и второй раз, чтобы вам не пришлось повторять мантру в третий раз, мне придется объяснить это. Вот. Не в том месте.
- Никто не может подойти к базе данных и изменить столбец здесь или значение там. Использование SQL напрямую или приложение, которое использует SQL напрямую. Если это было разрешено, у вас не будет защищенной базы данных, у вас будет проститутка в дешевом борделе.
- Все обновления (в нижнем регистре) базы данных (включая многострочные) INSERT/UPDATE/DELETE) реализованы как транзакции ACID SQL. И ничего кроме Транзакций. Набор транзакций составляет API базы данных, который доступен любому приложению, использующему базу данных.
- SQL имеет ACID транзакции. Номера для SQL не имеют транзакций. Ваш культ любит не SQL. Они абсолютно ничего не знают о транзакциях, пусть алоэ Open Architecture. Их Не-архитектура - монолитный стек. И "база данных", которая подвергается рефакторингу каждый месяц.
- Поскольку единственные транзакции, которые вы пишете, будут вставлять подтип basetype+ в одну транзакцию как единую логическую единицу работы, целостность (целостность данных, а не ссылочная целостность) отношения basetype::subtype поддерживается и поддерживается в пределах база данных. Поэтому все обновления в базе данных будут действительными, недействительных обновлений не будет.
- Так как вы не настолько глупы, чтобы писать код, который UPDATE столбец "Дискриминатор" в одной строке без оператора DELETE Previous_Subtype поместите его в транзакцию и GRANT EXEC разрешение для этого пользователю ROLES, в базе данных не будет недопустимого дискриминатора.