Блог

У меня есть таблица, которая имеет внешний ключ к другой таблице.
Например: Postion.day REFERENCES weekdays.day это нормально. Однако Position.day может содержать дни недели, где rdo=true .

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

Я подозреваю, что ответ будет выглядеть примерно так:

 ALTER TABLE Postition ADD COLUMN day CHAR(3)
            FOREIGN KEY REFERENCES weekday(shortName) 
            CHECK (weekday.rdo=TRUE);
  

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

Обновить:
итак, у меня есть таблица Name Table (я не назвал ее), у меня есть еще одна таблица weekdays, в которой перечислены все 7 дней недели вместе с некоторой другой информацией. Таблица имен имеет 2 внешних ключа rdo и shortDay. дни недели содержат битовое поле для rdo и битовое поле для короткого дня, указывающее, подходит ли день для этих дней. Поэтому я хочу, чтобы мое поле RDO было внешним ключом для дней недели, но ТОЛЬКО ТАМ, ГДЕ RDO=TRUE.

Первичный ключ дней недели — это короткое имя, состоящее из 3 букв (char(3) ), обозначающее день недели, НАПРИМЕР: Пн, вт, Ср, чт и т. Д

Я думал об этом и вспомнил транзакции SQL.(В настоящее время я держу пари, что SQL Server будет достаточно умен, чтобы откатить успешный вызов ALTER TABLE, таблица позиций уже существует.)

 BEGIN
ALTER TABLE [Name Table] ADD RDO CHAR(3);
ALTER TABLE [Name Table] ADD FOREIGN KEY (RDO) REFERENCES weekdays(shortName);
ALTER TABLE [Name Table] ADD CHECK (TRUE=(SELECT rdo FROM weekdays 
                                                     WHERE shortName=RDO));
ROLLBACK;
  

Который возвращается из базы данных:

Код ошибки 102, состояние SQL S0001: неправильный синтаксис рядом с ‘)’.
Строка 1, столбец 1

Код ошибки 1769, состояние SQL S0001: внешний ключ ‘RDO’ ссылается на недопустимый столбец ‘RDO’ в таблице ссылок ‘Name Table’.
Строка 3, столбец 1

Код ошибки 1046, состояние SQL S0001: Подзапросы в этом контексте не разрешены. Разрешены только скалярные выражения.
Строка 4, столбец 1

Код ошибки 3903, состояние SQL S0001: запрос ТРАНЗАКЦИИ ОТКАТА не имеет соответствующей ТРАНЗАКЦИИ BEGIN.
Строка 5, столбец 1

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

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

 ALTER TABLE [Name Table] ADD RDO CHAR(3)
            FOREIGN KEY REFERENCES weekdays(shortName,rdo=true);
  

это помогает?

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

Если таблица «день недели» содержит только дни недели {‘Пн’, ‘Вт’, ‘Ср’, ‘Чт’, ‘Пт’}, то все, что вам нужно, это внешний ключ.

Если таблица «день недели» содержит что-либо, кроме дней недели {‘Пн’, ‘Вт’, ‘Ср’, ‘Чт’, ‘Пт’}, я бы подумал о создании другой таблицы из пяти фактических дней недели. Используйте любые ограничения целостности, которые вы считаете необходимыми. Таблица «Позиция» может установить ссылку на внешний ключ для этой таблицы из пяти строк.

Я мог бы рассмотреть возможность записи всего ограничения в качестве КОНТРОЛЬНОГО ограничения.

 ALTER TABLE Postition 
ADD COLUMN day CHAR(3)
CHECK (day IN ('Mon', 'Tue', 'Wed', 'Thu', 'Fri');
  

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

Позже . . .

Чтобы установить ограничение внешнего ключа только для тех строк, где RDO=TRUE , вам нужно определить эти дни как строки в таблице. (Внешние ключи предназначены для столбца, а не для столбца, отфильтрованного по значению в другом столбце.) Прямо сейчас у вас есть два логических флага. Рассмотрите возможность создания двух таблиц вместо этого.

 create table rdo (
  day char(3) primary key references weekdays (shortname)
);
insert into rdo values ('Mon');
insert into rdo values ('Tue');
insert into rdo values ('Wed');
insert into rdo values ('Thu');
insert into rdo values ('Fri');

create table shortday (
  day char(3) primary key references weekdays (shortname)
);
insert into shortday values ('Fri');
  

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

Вы получите лучшие ответы, если предоставите DDL и образцы данных в виде инструкций SQL INSERT.

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

Я думал, что FK в отфильтрованном индексе (SQL 2008) или в материализованном представлении может решить проблему, но, по-видимому, ни один из этих подходов не работает: (

В настоящее время я работаю над достижением этого:

• Добавьте дополнительный bit столбец в Table , называемый, например IsRdo .
  • Добавьте check это Table.IsRdo = true . Да, у нас есть столбец, который всегда 1 .
  • Или, я только что заметил, сделайте его постоянным: [IsRdo] AS cast(1 as bit) PERSISTED
• Добавьте составной ключ-кандидат weekdays , состоящий из (shortName, rdo) .
• Сделайте внешний ключ из Table weekdays совпадающим (shortName, rdo) .

Это гарантирует, что Table строка может соответствовать только weekdays строке, где rdo есть true .

Это кажется немного странным (опять же, у нас есть столбец, который всегда 1 ), но я не нашел лучшего способа сделать это.

Если у вас есть контроль над всем процессом (кодом, вызывающим базу данных, и самой базой данных), я бы настоятельно рекомендовал вам использовать хранимые процедуры для выполнения вставки / обновления / удаления.

Затем вы должны реализовать свои проверки в хранимой процедуре перед выполнением действий и вызвать ошибку с соответствующими ошибками при обнаружении проблемы.

Основное преимущество

1) Ваши бизнес-правила инкапсулированы, и их легко понять, когда придет время их менять через 6 месяцев или год

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

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