Oracle - составной внешний ключ с частичным обнулением

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

Таблица намного больше с точки зрения столбцов, но я думаю, что смог устранить проблему в приведенном ниже примере:

create table quadrant (
  region number(9) not null,
  area number(9) not null,
  caption varchar2(20),
  primary key (region, area)
);

insert into quadrant (region, area, caption) values (10, 123, 'Chicago');
insert into quadrant (region, area, caption) values (10, 125, 'Wisconsin');

create table farm (
  id number(9),
  region_id number(9) not null,
  area_id number(9),
  name varchar2(50),
  constraint fk_region_area foreign key (region_id, area_id)
    references quadrant (region, area)
);

insert into farm (id, region_id, area_id, name) values (5, 10, null, 'farm 1');
insert into farm (id, region_id, area_id, name) values (6, 11, null, 'farm 2');

select * from farm;

Результат:

ID  REGION_ID  AREA_ID  NAME
--  ---------  -------  ------
5   10         <null>   farm 1  <-- Does it point to anything?
6   11         <null>   farm 2  <-- Region 11 doesn't even exist!

Если части внешнего ключа равны нулю, то какое значение это имеет?

  • REGION_ID, AREA_ID = (10, null) на что-то указывает, или это просто бесполезная информация?
  • REGION_ID, AREA_ID = (11, null) указывает на что-нибудь? Я так не думаю.

У меня возникает соблазн добавить ограничение для принудительного применения ALL или NONE с точки зрения нулевых значений внешнего ключа. Имеет ли это смысл?

Но, прежде всего, каков вариант использования этой «функции»?

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

Gordon Linoff 13.09.2018 20:25

Но даже несмотря на то, что region = 11 даже не существует, вставка выполняется успешно.

The Impaler 13.09.2018 20:29

Узнайте, как работают FK. FK соответствует PK в режиме MATCH. По умолчанию SIMPLE означает, что если есть ноль, то есть совпадение. Это часто задаваемые вопросы - очевидно, из его основного характера. Пожалуйста, всегда гуглите много четких, кратких и конкретных версий / формулировок вашего вопроса / проблемы / цели с вашими конкретными строками / именами и без них и читайте много ответов. Добавьте в поисковые запросы релевантные ключевые слова, которые вы обнаружите. Если вы не нашли ответа, отправьте сообщение, используя поиск по одному из вариантов по заголовку и ключевым словам для тегов. См. Текст при наведении курсора мыши на стрелку "против". PS Связанный..

philipxy 13.09.2018 20:40

@philipxy Спасибо за режим MATCH. Я думаю, что Oracle по умолчанию использует «простой», и я не могу найти способ переключить его на «полный». И да, я провел свое исследование, но не знал, как даже назвать проблему.

The Impaler 13.09.2018 20:42

Я не думаю, что это дубликат stackoverflow.com/questions/2366854/… - это касается целых FK с нулевым значением, а не частично обнуляемых.

The Impaler 13.09.2018 20:48

Спасибо @philipxy, в этом не было необходимости.

The Impaler 13.09.2018 20:51

Я не понимаю вашего комментария. (Мой комментарий был создан и опубликован до того, как я увидел дубликаты вашего комментария, хотя я добавил предложение после того, как увидел ваш.) Я не знаю, что вы считаете ненужным. Это очевидный часто задаваемый вопрос и, очевидно, он будет рассмотрен в документации по SQL FK, и существует множество повторяющихся кандидатов, и вы не используете Google эффективно, но, похоже, вы не понимаете всего этого, поэтому я пытаюсь помочь вам, рассказывая ты. Как спросить

philipxy 13.09.2018 20:58
5
8
1 044
5

Ответы 5

Есть много споров об использовании нулей для чего-то. Некоторые утверждают, что null означает, что значение неизвестно или представляет недействительность, другие утверждают, что это само по себе фактическое значение. Я подозреваю, что в данном случае это неизвестно. Предположим, вы документировали расположение ферм в округе за 100 лет до настоящего времени. Используя некоторые книги по местной истории, вы нанесли на карту 70% существующих ферм того периода и их точные границы (или около того), но для оставшихся 30% некоторые из них имеют известные регионы, а некоторые только известны о существовании. В этом случае я бы определенно сказал, что пустой внешний ключ имеет смысл. Это просто неизвестная информация.

Некоторые предположения относительно вашей «особенности»: Может быть, поле площади применимо только к некоторым фермам? Пример: ферма с обозначенной площадью должна платить некоторую дополнительную плату или налог (догадываюсь, так как я не знаю ваших данных)? В этом случае NULL что-то означает (не требуется платить). Может быть, есть фермы, которые существовали до внедрения «площади», и поэтому никогда не были закреплены за ней? В этом случае NULL на самом деле означает NULL, поскольку эта область никогда не существовала и поэтому неизвестна.

Однако 11 ни в коем случае не является допустимым значением и все равно успешно вставляется. Я предполагаю (мое предположение), что при наличии нулей весь FK может считаться «еще не действительным» или «временным». Вроде бы можно хранить, но пока на что-то конкретное не указывает. Опять же, только предположение.

The Impaler 13.09.2018 20:44

Я не знаю, имеет ли это смысл для вашей модели данных, но есть определенные варианты использования частично NULL внешних ключей.

Рассмотрим простую таблицу основных средств (компьютеры, автомобили, здания и т. д. - вещи, которые бухгалтеры будут амортизировать). Предположим, они хотят знать, где используется актив, поэтому у них есть два столбца: company_id и department_id.

Некоторые активы, такие как здания, могут быть разделены между отделами, поэтому я ожидал бы внешнего ключа, такого как (123, null). Я также ожидал бы, что внешний ключ отдельный будет только для таблицы COMPANY на COMPANY_ID.

Смысл такой настройки заключается в том, что company_id должно быть известным значением, а комбинация компания / отдел, если он существует, должна быть известной комбинацией.

ОБНОВИТЬ

Я не уверен, почему вы думаете, что Oracle не может делать то, что я описываю. Вот простой тест:

CREATE TABLE tst_company 
  ( company_id NUMBER NOT NULL PRIMARY KEY );

CREATE TABLE tst_department
  ( company_id NUMBER NOT NULL,
    department_id NUMBER NOT NULL,
    CONSTRAINT tst_department_pk PRIMARY KEY ( company_id, department_id ),
    CONSTRAINT tst_department_f1 FOREIGN KEY ( company_id ) REFERENCES tst_company ( company_id ) );

CREATE TABLE tst_asset
  ( asset_id NUMBER NOT NULL PRIMARY KEY,
    company_id NUMBER NOT NULL,
    department_id NUMBER,
    CONSTRAINT tst_asset_f1 FOREIGN KEY ( company_id ) REFERENCES tst_company ( company_id ),
    CONSTRAINT tst_asset_f2 FOREIGN KEY ( company_id, department_id ) REFERENCES tst_department ( company_id, department_id ) );

INSERT INTO tst_company ( company_id ) VALUES (1);
INSERT INTO tst_department ( company_id, department_id ) VALUES (1, 10);
INSERT INTO tst_asset ( asset_id, company_id, department_id ) VALUES (1001, 1, 10);  -- Department specific asset
INSERT INTO tst_asset ( asset_id, company_id, department_id ) VALUES (1002, 1, NULL);  -- Non-department specific asset

INSERT INTO tst_asset ( asset_id, company_id, department_id ) VALUES (1003, 2, NULL);  -- Bad company - fails
INSERT INTO tst_asset ( asset_id, company_id, department_id ) VALUES (1004, 1, 11);  -- Bad department - fails
INSERT INTO tst_asset ( asset_id, company_id, department_id ) VALUES (1005, 2, 11);  -- Bad company AND department - fails

После прочтения комментария @philipxy кажется, что Oracle не может сделать то, что вы описываете, а именно «режим совпадения ЧАСТИЧНЫЙ». Кажется, что PostgreSQL и MySQL действительно его реализуют (хотя я не проверял). Проще говоря, при наличии любого нуля Oracle сохраняет FK, не проверяя его. Это известно как «режим совпадения ПРОСТОЙ». Спасибо за понимание.

The Impaler 13.09.2018 21:10

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

Matthew McPeak 13.09.2018 21:26

Да, ваш пример хорош, поскольку вы добавили ограничение tst_asset_f1. Это позволяет проверить company_id в отсутствие department_id. Если вы удалите это ограничение, Oracle не сможет выполнить эту проверку изначально (и можно будет вставить несуществующие значения company_id): для этого потребуется режим сопоставления «частичный», который он не реализует. Тем не менее, хороший трюк (очевидный, но я просто не подумал об этом).

The Impaler 13.09.2018 21:44

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

У этого вопроса две стороны: во-первых, что такое частично нулевой внешний ключ средства, а во-вторых, как это реализовано.

Значение частично нулевых внешних ключей

Как отмечает @ agiles231, есть много споров о том, что это означает. NULL может означать:

  • значение - неизвестный.
  • другие говорят, что это означает значение неверный.
  • другие говорят, что NULL сам по себе является добросовестная ценность.

Короче говоря, четкого ответа на его значение пока нет.

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

Реализация частично нулевых внешних ключей

Стандарт SQL-92 определяет (раздел 4.10.2) три различных способа сопоставления составных внешних ключей со значениями, допускающими значение NULL:

  • Match ПРОСТО: если какой-либо столбец составного внешнего ключа имеет значение NULL, то внешний ключ принимается, сохраняется, но не проверяется по указанной таблице. Обычно этот режим предлагается базами данных по умолчанию. В стандарте SQL-92 этот режим описан, но не назван.

  • Match ЧАСТИЧНЫЙ: если какой-либо столбец составного внешнего ключа имеет значение NULL, то каждый столбец, отличный от NULL, сопоставляется с таблицей, на которую указывает ссылка, для проверки наличия хотя бы одной строки, в которой присутствует значение. Я не видел ни одной базы данных, реализующей этот режим.

  • Match ПОЛНЫЙ: Частично нулевые внешние ключи не принимаются. Либо внешний ключ полностью равен нулю, либо полностью не равен нулю. Если значение равно null, проверка по указанной таблице отсутствует. Если он не равен нулю, он полностью проверен по указанной таблице. Это то, что я ожидал по умолчанию (в моем блаженном неведении).

Я проверил, как 10 разных баз данных реализуют эти режимы, и вот что я нашел:

Database Engine  Match SIMPLE  Match PARTIAL  Match FULL
---------------  ------------  -------------  ----------
Oracle 12c1      YES*1         NO             NO
DB2 10.5         YES*1         NO             NO
PostgreSQL 10    YES*1         NO             YES
SQL Server 2014  YES*1         NO             NO
MariaDB 10.3     YES*1         NO*2           NO*2
MySQL 8.0        YES*1         NO*2           NO*2
Sybase ASE 16    YES*1         NO             YES
H2 1.4           YES*1         NO             NO
Derby 10.13      YES*1         NO             NO
HyperSQL 2.3     YES*1         NO             YES

* 1 Это режим по умолчанию.

* 2 Принято при создании таблицы, но игнорируется.

Суммируя:

  • Все протестированные базы данных по умолчанию ведут себя одинаково: они по умолчанию соответствуют SIMPLE.

  • Ни одна из протестированных мной баз данных не поддерживает Match PARTIAL. Думаю, это имеет смысл, поскольку лично я не нахожу в этом особого смысла. Более того, выполнение частичной проверки отдельных столбцов внешнего ключа без создания всех возможных комбинаций индексов в ссылочной таблице может стать чрезмерно дорогостоящим.

  • PostgreSQL реализует Match FULL, а также Sybase ASE. Это замечательные новости! Удивительно, но HyperSQL (эта крошечная база данных) тоже.

Обходной путь для реализации Match FULL

Хорошая новость заключается в том, что существует довольно простой обходной путь для реализации Match FULL, если он вам понадобится, в любой из протестированных баз данных. Просто добавьте ограничение таблицы, которое разрешает либо все нулевые столбцы, либо все ненулевые. Что-то вроде:

create table farm (
  id int,
  region_id int,
  area_id int,
  name varchar(50),
  constraint fk_region_area foreign key (region_id, area_id)
    references quadrant (region, area),
  constraint fkfull_region_area check ( -- here's the workaround
    region_id is null and area_id is null or
    region_id is not null and area_id is not null)
);

insert into farm (id, region_id, area_id, name) values (5, 10, null, 'farm 1'); -- fails

insert into farm (id, region_id, area_id, name) values (6, 11, null, 'farm 2'); -- fails

insert into farm (id, region_id, area_id, name) values (7, 10, 125, 'farm 3'); -- succeeds

insert into farm (id, region_id, area_id, name) values (8, null, null, 'farm 4'); -- succeeds

Работает довольно аккуратно.

Наконец, как очень личное мнение, я ожидал, что Match FULL будет стратегией сопоставления по умолчанию. Возможно, для меня просто разрешение (по умолчанию) внешних ключей, которые не указывают на другие строки, вызывает ошибки в приложениях, использующих базу данных.

Я думаю, что большинство разработчиков легко поймут ПОЛНОЕ по сравнению с ПРОСТОЙ. А PARTIAL намного сложнее и потенциально подвержен ошибкам. Только мое мнение.

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

Carlos López Marí 17.03.2021 11:14

Ответ Мэтью показывает, как это обойти. Что касается того, почему это происходит, помните:

Ограничения отклоняют только те строки, где условие ложно.

Сравнение чего-либо с null => unknown. Таким образом, нулевые значения передают ограничения, если вы специально не проверяете их. Что приводит к бесхозным строкам, которые вы демонстрируете.

Как говорят документы:

If any column of a composite foreign key is null, then the non-null portions of the key do not have to match any corresponding portion of a parent key

В большинстве случаев это ошибка или упущение первоначальных дизайнеров.

Спасибо, прочитав здесь все возможности, я думаю, что это просто упущение. В моем конкретном случае простое решение - сделать area_id not null.

The Impaler 14.09.2018 17:47

Другие вопросы по теме