условное уникальное ограничение

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

Так, например, у меня есть таблица, как таблица(ID, имя, RecordStatus).

RecordStatus может иметь только значение 1 или 2 (активный или удаленный), и я хочу создать уникальное ограничение на (ID, RecordStatus) только тогда, когда RecordStatus = 1, так как мне все равно, есть ли несколько удаленных записей с одним и тем же идентификатором.

помимо написания триггеры, я могу это сделать?

Я использую SQL Server 2005.

6 77
sql sql-server sql-server-2005

6 ответов:

добавить проверочное ограничение, как это. Разница в том, что вы вернете 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;
35

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

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

и вот пример объединения уникального индекса с предикатом фильтра:

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

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

Примечание: отфильтрованный индекс был введен в SQL Server 2008. Более ранние версии SQL Server см. В разделе ответ.

115

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

9

вы можете сделать это в очень hacky образом...

создать вид привязанного к схеме на вашем столе.

создать вид все, что угодно Выберите * из таблицы Где RecordStatus = 1

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

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

3

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

1

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

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

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

1