Блог

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

Моя таблица выглядит следующим образом (postgres):

 CREATE TABLE 123.myTable(
some_id integer NOT NULL,
valid_from timestamp without time zone NOT NULL DEFAULT now(),
valid_until timestamp without time zone,
someString character varying)
  

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

Спасибо

В PostgreSQL у вас есть два основных подхода.

1. Используйте 'infinity' вместо null. Тогда ваше уникальное ограничение работает так, как ожидалось. Или если вы не можете этого сделать:
2. CREATE UNIQUE INDEX null_valid_from ON mytable(someid) where valid_until IS NULL

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

В зависимости от базы данных, вы не можете иметь null в первичном ключе (я не знаю обо всех базах данных, но в sql server вы не можете). Самый простой способ обойти это, который я могу придумать, — установить для даты минимальное значение, а затем добавить к нему уникальное ограничение или установить его в качестве первичного ключа.

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

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

Оператор SQL, который проверяет это условие, является:

 SELECT COUNT(*)
FROM TABLE
WHERE valid until IS NULL; 
  

Если количество не равно 1, значит, в вашей таблице проблема.

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

• Найдите строку, в которой допустимое значение until равно NULL
• Обновите действительное значение until до текущей даты или какой-либо другой значимой даты
• Вставьте новую строку с допустимым значением, равным NULL

Я предполагаю, что вы храните записи с действующей датой и также используете действительную дату начала.

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

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

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

1. создайте другой столбец, который всегда содержит фиксированное значение (скажем, ‘0’), включите этот столбец в свой уникальный ключ.

2. Используйте не NULL, а конкретное очень высокое или низкое значение. Во многих случаях это на самом деле проще использовать, чем нулевое значение

3. Создайте уникальный ключ на основе функции, преобразующий дату, включая значение null, в какое-либо другое значение (например, строковое представление для дат и ‘x’ для null)

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

   выберите count (*) cnt из таблицы, где valid_until равно нулю

может работать как оператор select. И контрольное ограничение, ограничивающее значение cnt значениями 0 и 1

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

 CREATE TABLE dbo.TESTTRIGGER
(
    YourDate Date NULL
)


    CREATE TRIGGER DupNullDates
ON dbo.TESTTRIGGER
FOR INSERT, UPDATE
AS 
DECLARE @nullCount int
SELECT @nullCount = (SELECT COUNT(*) FROM TESTTRIGGER WHERE YourDate IS NULL)
IF(@NullCount > 1)
BEGIN
    RAISERROR('Cannot have Multiple Nulls', 16, 1)
    ROLLBACK TRAN
END

GO
  

Ну, если вы используете MS SQL, вы можете просто добавить уникальный индекс в этот столбец. Это допустит только одно значение NULL. Я предполагаю, что если вы используете другую СУБД, это все равно будет функционировать.