Могу ли я добавить уникальное ограничение, которое игнорирует существующие нарушения?

У меня есть таблица, которая в настоящее время имеет повторяющиеся значения в столбце.

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

Можно ли создать UNIQUE, который не проверяет наличие соответствия?

Я пробовал использовать NOCHECK, но не увенчался успехом.

В этом случае у меня есть таблица, которая связывает лицензионную информацию с именем «CompanyName»

РЕДАКТИРОВАТЬ: Наличие нескольких строк с тем же «CompanyName» - это плохие данные, но мы не можем удалить или обновить эти дубликаты в это время. Один из подходов состоит в том, чтобы INSERT использовал хранимую процедуру, которая не будет работать для дубликатов ... Если бы было возможно, что SQL проверяет уникальность самостоятельно, это было бы предпочтительнее.

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

35 голосов | спросил Matthew 4 J0000006Europe/Moscow 2013, 21:41:59

5 ответов


28

Ответ «да». Вы можете сделать это с помощью отфильтрованного индекса (см. здесь для документации).

Например, вы можете сделать:

create unique index t_col on t(col) where id > 1000;

Это создает уникальный индекс, только на строках new , а не на старых строках. Эта конкретная формулировка позволит дублировать существующие значения.

Если у вас есть только несколько дубликатов, вы можете сделать что-то вроде:

create unique index t_col on t(col) where id not in (<list of ids for duplicate values here>);
ответил Gordon Linoff 4 J0000006Europe/Moscow 2013, 22:28:38
21

Да, вы можете это сделать.

Вот таблица с дубликатами:

CREATE TABLE dbo.Party
  (
    ID INT NOT NULL
           IDENTITY ,
    CONSTRAINT PK_Party PRIMARY KEY ( ID ) ,
    Name VARCHAR(30) NOT NULL
  ) ;
GO

INSERT  INTO dbo.Party
        ( Name )
VALUES  ( 'Frodo Baggins' ),
        ( 'Luke Skywalker' ),
        ( 'Luke Skywalker' ),
        ( 'Harry Potter' ) ;
GO

Не будем игнорировать существующие и не будем добавлять новые дубликаты:

-- Add a new column to mark grandfathered duplicates.
ALTER TABLE dbo.Party ADD IgnoreThisDuplicate INT NULL ;
GO

-- The *first* instance will be left NULL.
-- *Secondary* instances will be set to their ID (a unique value).
UPDATE  dbo.Party
SET     IgnoreThisDuplicate = ID
FROM    dbo.Party AS my
WHERE   EXISTS ( SELECT *
                 FROM   dbo.Party AS other
                 WHERE  other.Name = my.Name
                        AND other.ID < my.ID ) ;
GO

-- This constraint is not strictly necessary.
-- It prevents granting further exemptions beyond the ones we made above.
ALTER TABLE dbo.Party WITH NOCHECK
ADD CONSTRAINT CHK_Party_NoNewExemptions 
CHECK(IgnoreThisDuplicate IS NULL);
GO

SELECT * FROM dbo.Party;
GO

-- **THIS** is our pseudo-unique constraint.
-- It works because the grandfathered duplicates have a unique value (== their ID).
-- Non-grandfathered records just have NULL, which is not unique.
CREATE UNIQUE INDEX UNQ_Party_UniqueNewNames ON dbo.Party(Name, IgnoreThisDuplicate);
GO

Проверим это решение:

-- cannot add a name that exists
INSERT  INTO dbo.Party
        ( Name )
VALUES  ( 'Frodo Baggins' );

Cannot insert duplicate key row in object 'dbo.Party' with unique index 'UNQ_Party_UniqueNewNames'.

-- cannot add a name that exists and has an ignored duplicate
INSERT  INTO dbo.Party
        ( Name )
VALUES  ( 'Luke Skywalker' );

Cannot insert duplicate key row in object 'dbo.Party' with unique index 'UNQ_Party_UniqueNewNames'.


-- can add a new name 
INSERT  INTO dbo.Party
        ( Name )
VALUES  ( 'Hamlet' );

-- but only once
INSERT  INTO dbo.Party
        ( Name )
VALUES  ( 'Hamlet' );

Cannot insert duplicate key row in object 'dbo.Party' with unique index 'UNQ_Party_UniqueNewNames'.
ответил A-K 5 J0000006Europe/Moscow 2013, 01:06:03
15

Отфильтрованный уникальный индекс - блестящая идея, но у него есть небольшой недостаток - независимо от того, используете ли вы WHERE identity_column > <current value> или WHERE identity_column NOT IN (<list of ids for duplicate values here>).

При первом подходе вы все равно сможете вставлять дубликаты данных в будущем, дублировать существующие (сейчас) данные. Например, если у вас есть (хотя бы одна) строка теперь с CompanyName = 'Software Inc.', индекс не запретит вставлять еще одну строку с тем же именем компании. Это будет только запретить, если вы попробуете дважды.

При втором подходе улучшается, выше не работает (что хорошо). Однако вы все равно сможете вставить больше дубликатов или существующих дубликатов. Например, если у вас есть (две или более) строки теперь с CompanyName = 'DoubleData Co.', индекс не будет запрещать вставку еще одной строки с тем же названием компании. Это будет только запретить, если вы попробуете дважды.

(Обновление) Это можно исправить, если для каждого повторяющегося имени вы исключаете из списка исключений один идентификатор. Если, как и в предыдущем примере, существует 4 строки с дубликатом CompanyName = DoubleData Co. и идентификаторы 4,6,8,9, список исключений должен содержать только 3 эти идентификаторы.

При втором подходе другой недостаток - громоздкое условие (насколько громоздко зависит от того, сколько дубликатов там в первую очередь), поскольку SQL-сервер, похоже, не поддерживает оператор NOT IN в WHERE часть отфильтрованных индексов. См. SQL-скрипт . Вместо WHERE (CompanyID NOT IN (3,7,4,6,8,9)), вам нужно будет что-то вроде WHERE (CompanyID <> 3 AND CompanyID <> 7 AND CompanyID <> 4 AND CompanyID <> 6 AND CompanyID <> 8 AND CompanyID <> 9) Я не уверен, условие, если у вас есть сотни дубликатов имен.


Другим решением (аналогичным @Alex Kuznetsov's) является добавление другого столбца, заполнение его номерами рангов и добавление уникального индекса, включая этот столбец:

ALTER TABLE Company
  ADD Rn TINYINT DEFAULT 1;

UPDATE x
SET Rn = Rnk
FROM
  ( SELECT 
      CompanyID,
      Rn,
      Rnk = ROW_NUMBER() OVER (PARTITION BY CompanyName 
                               ORDER BY CompanyID)
    FROM Company 
  ) x ;

CREATE UNIQUE INDEX CompanyName_UQ 
  ON Company (CompanyName, Rn) ; 

Затем вставка строки с дублирующимся именем завершится неудачно из-за свойства DEFAULT 1 и уникального индекса. Это по-прежнему не является 100% надежным (пока Алекс). Дубликаты все равно будут проскальзывать, если код Rn явно установлен в инструкции INSERT или если значения Rn злонамеренно обновлены.

SQL-Fiddle-2

ответил ypercubeᵀᴹ 5 J0000006Europe/Moscow 2013, 01:00:38
-2

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

Это сделает ужасные вещи для производительности.

ответил Greenstone Walker 5 J0000006Europe/Moscow 2013, 05:45:02
-2

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

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

ответил Brad 29 +03002015-10-29T22:08:06+03:00312015bEurope/MoscowThu, 29 Oct 2015 22:08:06 +0300 2015, 22:08:06

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132