Условное единственное ограничение

Ответ 1

Добавьте контрольное ограничение, подобное этому. Разница в том, что вы вернете false, если Status = 1 и Count > 0.

http://msdn.microsoft.com/en-us/library/ms188258.aspx

CREATE TABLE CheckConstraint
(
  Id TINYINT,
  Name VARCHAR(50),
  RecordStatus TINYINT
)
GO

CREATE FUNCTION CheckActiveCount(
  @Id INT
) RETURNS INT AS BEGIN

  DECLARE @ret INT;
  SELECT @ret = COUNT(*) FROM CheckConstraint WHERE Id = @Id AND RecordStatus = 1;
  RETURN @ret;

END;
GO

ALTER TABLE CheckConstraint
  ADD CONSTRAINT CheckActiveCountConstraint CHECK (NOT (dbo.CheckActiveCount(Id) > 1 AND RecordStatus = 1));

INSERT INTO CheckConstraint VALUES (1, 'No Problems', 2);
INSERT INTO CheckConstraint VALUES (1, 'No Problems', 2);
INSERT INTO CheckConstraint VALUES (1, 'No Problems', 2);
INSERT INTO CheckConstraint VALUES (1, 'No Problems', 1);

INSERT INTO CheckConstraint VALUES (2, 'Oh no!', 1);
INSERT INTO CheckConstraint VALUES (2, 'Oh no!', 2);
-- Msg 547, Level 16, State 0, Line 14
-- The INSERT statement conflicted with the CHECK constraint "CheckActiveCountConstraint". The conflict occurred in database "TestSchema", table "dbo.CheckConstraint".
INSERT INTO CheckConstraint VALUES (2, 'Oh no!', 1);

SELECT * FROM CheckConstraint;
-- Id   Name         RecordStatus
-- ---- ------------ ------------
-- 1    No Problems  2
-- 1    No Problems  2
-- 1    No Problems  2
-- 1    No Problems  1
-- 2    Oh no!       1
-- 2    Oh no!       2

ALTER TABLE CheckConstraint
  DROP CONSTRAINT CheckActiveCountConstraint;

DROP FUNCTION CheckActiveCount;
DROP TABLE CheckConstraint;

Ответ 2

Вот, отфильтрованный индекс. Из документации (выделено мое):

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

И вот пример, объединяющий уникальный индекс с предикатом фильтра:

create unique index MyIndex
on MyTable(ID)
where RecordStatus = 1;

По сути, это обеспечивает уникальность ID когда RecordStatus равен 1.

_{Примечание. Отфильтрованный индекс был представлен в SQL Server 2008. Для более ранних версий SQL Server см. Этот ответ.}

Ответ 3

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

Ответ 4

Вы можете сделать это по-настоящему взломанным способом...

Создайте представление схемы в вашей таблице.

СОЗДАТЬ ВИД SELECT * FROM Table WHERE RecordStatus = 1

Теперь создайте уникальное ограничение для представления с нужными вами полями.

Одна заметка о представлениях схемы, хотя, если вы измените базовые таблицы, вам придется воссоздать представление. Из-за этого было много ошибок.

Ответ 5

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

Ответ 6

Если вы не можете использовать NULL как RecordStatus, как предложил Билл, вы можете объединить его идею с индексом на основе функций. Создайте функцию, которая возвращает NULL, если RecordStatus не является одним из значений, которые вы хотите рассмотреть в своем ограничении (и в противном случае RecordStatus), и создайте индекс над этим.

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

Я должен сказать, что я вообще не знаю SQL-сервер, но я успешно использовал этот подход в Oracle.