Czy mogę dodać unikalne ograniczenie, które ignoruje istniejące naruszenia?

Mam tabelę, która ma obecnie zduplikowane wartości w kolumnie.

Nie mogę usunąć tych błędnych duplikatów, ale chciałbym zapobiec dodawaniu dodatkowych, nieunikalnych wartości.

Czy mogę utworzyć taki UNIQUE, który nie sprawdza istniejącej zgodności?

Próbowałem użyć, NOCHECKale nie powiodło się.

W tym przypadku mam tabelę, która wiąże informacje licencyjne z „CompanyName”

EDYCJA: Posiadanie wielu wierszy z tym samym „CompanyName” to złe dane, ale w tej chwili nie możemy usunąć ani zaktualizować tych duplikatów. Jednym z podejść jest INSERTużycie procedury składowanej, która zakończy się niepowodzeniem w przypadku duplikatów ... Gdyby możliwe było samodzielne sprawdzenie unikalności przez SQL, byłoby to preferowane.

Dane te są wyszukiwane według nazwy firmy. W przypadku kilku istniejących duplikatów oznacza to, że wiele wierszy jest zwracanych i wyświetlanych ... Chociaż jest to błędne, w naszym przypadku jest to dopuszczalne. Celem jest zapobieganie temu w przyszłości. Wydaje mi się, że na podstawie komentarzy muszę wykonać tę logikę w procedurach przechowywanych.

Odpowiedź brzmi tak". Możesz to zrobić za pomocą filtrowanego indeksu (patrz dokumentacja tutaj ).

Na przykład możesz wykonać:

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

Tworzy to unikalny indeks, tylko w nowych wierszach, a nie w starych wierszach. Ten konkretny preparat pozwoliłby na duplikaty z istniejącymi wartościami.

Jeśli masz tylko garść duplikatów, możesz zrobić coś takiego:

create unique index t_col on t(col) where id not in (<list of ids for duplicate values here>);

Tak, możesz to zrobić.

Oto tabela z duplikatami:

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

Zignorujmy istniejące i upewnij się, że nie można dodawać nowych duplikatów:

-- 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

Przetestujmy to rozwiązanie:

-- 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'.

Filtrowany unikalny indeks jest genialnym pomysłem, ale ma niewielką wadę - bez względu na to, czy używasz WHERE identity_column > <current value>warunku, czy WHERE identity_column NOT IN (<list of ids for duplicate values here>).

Przy pierwszym podejściu nadal będziesz mógł wstawiać zduplikowane dane w przyszłości, duplikaty istniejących (teraz) danych. Na przykład, jeśli masz teraz (nawet tylko jeden) wiersz CompanyName = 'Software Inc.', indeks nie zabrania wstawiania jeszcze jednego wiersza o tej samej nazwie firmy. Zabrania to tylko, jeśli spróbujesz dwa razy.

W drugim podejściu jest poprawa, powyższe nie zadziała (co jest dobre.) Jednak nadal będziesz mógł wstawić więcej duplikatów lub istniejących duplikatów. Na przykład, jeśli masz teraz (dwa lub więcej) wierszy CompanyName = 'DoubleData Co.', indeks nie zabrania wstawiania jeszcze jednego wiersza o tej samej nazwie firmy. Zabrania to tylko, jeśli spróbujesz dwa razy.

(Aktualizacja) Można to poprawić, jeśli dla każdej zduplikowanej nazwy trzymasz z listy wykluczeń jeden identyfikator. Jeśli, podobnie jak w powyższym przykładzie, są 4 wiersze z duplikatem CompanyName = DoubleData Co.i identyfikatorami 4,6,8,9, lista wykluczeń powinna mieć tylko 3 z tych identyfikatorów.

Przy drugim podejściu kolejną wadą jest kłopotliwy warunek (ile kłopotliwy zależy od liczby duplikatów w pierwszej kolejności), ponieważ SQL Server nie wydaje się obsługiwać NOT INoperatora w WHEREczęści przefiltrowanych indeksów. Zobacz SQL-Fiddle . Zamiast tego WHERE (CompanyID NOT IN (3,7,4,6,8,9))musisz mieć coś takiego WHERE (CompanyID <> 3 AND CompanyID <> 7 AND CompanyID <> 4 AND CompanyID <> 6 AND CompanyID <> 8 AND CompanyID <> 9), że nie jestem pewien, czy z takim stanem mają wpływ na wydajność, jeśli masz setki zduplikowanych nazw.

Innym rozwiązaniem (podobnym do @Aleksa Kuzniecowa) jest dodanie kolejnej kolumny, wypełnienie jej numerami rang i dodanie unikalnego indeksu obejmującego tę kolumnę:

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) ; 

Wstawienie wiersza o zduplikowanej nazwie nie powiedzie się z powodu DEFAULT 1właściwości i unikalnego indeksu. To wciąż nie jest w 100% niezawodne (podczas gdy Alex jest). Duplikaty nadal będą się pojawiać, jeśli Rnjest to jawnie ustawione w INSERTinstrukcji lub jeśli Rnwartości są złośliwie aktualizowane.

SQL-Fiddle-2

Inną alternatywą jest napisanie funkcji skalarnej, która sprawdza, czy wartość już istnieje w tabeli, a następnie wywołanie tej funkcji z ograniczenia sprawdzającego.

To zrobi okropne rzeczy do wykonania.

Szukam tego samego - utwórz niezaufany unikalny indeks, aby istniejące złe dane były ignorowane, ale nowe rekordy nie mogą być duplikatami niczego, co już istnieje.

Czytając ten wątek, przychodzi mi do głowy, że lepszym rozwiązaniem jest napisanie wyzwalacza, który sprawdzi [wstawiony] w tabeli nadrzędnej pod kątem duplikatów, a jeśli między tymi tabelami istnieje jakikolwiek duplikat, ROLLBACK TRAN.