Pytanie warunkowe unikalne ograniczenie


Mam sytuację, w której muszę wymusić unikalne ograniczenie na zestawie kolumn, ale tylko dla jednej wartości kolumny.

Tak na przykład mam tabelę jak Tabela (ID, Nazwa, RecordStatus).

RecordStatus może mieć tylko wartość 1 lub 2 (aktywne lub usunięte) i chcę utworzyć unikalne ograniczenie na (ID, RecordStatus) tylko wtedy, gdy RecordStatus = 1, ponieważ nie obchodzi mnie, czy istnieje wiele usuniętych rekordów z tym samym ID.

Oprócz pisania wyzwalaczy, czy mogę to zrobić?

Używam programu SQL Server 2005.


76
2018-05-14 21:57


pochodzenie


Ten projekt jest powszechnym bólem. Czy rozważałeś zmianę projektu, aby teoretycznie "usunięte" rekordy zostały fizycznie usunięte z tabeli i prawdopodobnie przeniesione do tabeli "archiwum"? - onedaywhen
... ponieważ niemożność napisania ograniczenia UNIQUE w celu wymuszenia prostego klucza należy uznać za "zapach kodu", IMO. Jeśli nie możesz zmienić projektu (SQL DDL), ponieważ wiele innych tabel odwołuje się do tej tabeli, to założę się, że Twój DML SQL również cierpi w wyniku, tj. Musisz pamiętać, aby dodać ... AND Table.RecordStatus = 1 ' do większości warunków wyszukiwania i warunków łączenia z udziałem tej tabeli i doświadczyć subtelnych błędów, gdy nieuchronnie zostanie pominięty przy okazji. - onedaywhen


Odpowiedzi:


Dodaj takie ograniczenie kontroli. Różnica polega na tym, że zwrócisz wartość false, jeśli Status = 1 i 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;

34
2018-05-14 22:06



Spojrzałem na ograniczenia sprawdzania poziomu tabeli, ale nie wygląda na to, że istnieje jakikolwiek sposób przekazać wartości wstawione lub zaktualizowane do funkcji, czy wiesz, jak? - np-hard
Okay, wysłałem przykładowy skrypt, który pomoże ci udowodnić, o czym mówię. Testowałem to i to działa. Jeśli spojrzysz na dwie skomentowane linie, zobaczysz komunikat, który otrzymuję. Nota bene, w mojej implementacji, zapewniam tylko, że nie możesz dodać drugiego elementu z tym samym identyfikatorem, który jest aktywny, jeśli jest już aktywny. Można zmodyfikować logikę tak, że jeśli jest aktywna, nie można dodać żadnej pozycji o tym samym id. Dzięki temu wzorowi możliwości są prawie nieograniczone. - D. Patrick
Wolałbym tę samą logikę w wyzwalaczu. "zapytanie w funkcji skalarnej ... może powodować duże problemy, jeśli ograniczenie CHECK jest zależne od zapytania i jeśli na aktualizację ma wpływ więcej niż jeden wiersz. Co się stanie, to ograniczenie zostanie sprawdzone raz dla każdego wiersza przed zakończeniem instrukcji Oznacza to, że atomowość instrukcji jest zepsuta, a funkcja będzie narażona na działanie bazy danych w niespójnym stanie, a wyniki są nieprzewidywalne i niedokładne. " Widzieć: blogs.conchango.com/davidportas/archive/2007/02/19/... - onedaywhen
Jest to tylko częściowo prawdziwe. Baza danych zachowuje się konsekwentnie i przewidywalnie. Ograniczenie czeku zostanie wykonane po dodaniu wiersza do tabeli i zanim transakcja zostanie zatwierdzona przez DBMS i możesz na to liczyć. Ten blog mówił o całkiem unikalnym problemie, w którym trzeba wykonać ograniczenie na zestawie insertów, a nie tylko jednej wstawce na raz. popiół prosi o ograniczenie na jednej wkładce naraz, a to ograniczenie zadziała dokładnie, przewidywalnie i konsekwentnie. Przepraszam, jeśli to zabrzmiało zwięźle; Brakowało mi postaci. - D. Patrick
Działa to doskonale w przypadku wstawek, ale nie działa w przypadku aktualizacji. NA PRZYKŁAD. Dodanie tego po innych wstawkach działa tutaj, kiedy się tego nie spodziewałem. INSERT INTO CheckConstraint VALUES (1, "No ProblemsA", 2); zaktualizuj CheckConstraint ustaw Recordstatus = 1 gdzie name = 'No ProblemsA' - dwidel


Ujrzeć, filtrowany indeks. Z dokumentacji (podkreślenie moje):

Filtrowany indeks jest zoptymalizowanym nieklastrowanym indeksem szczególnie dostosowanym do pokrywania zapytań wybieranych z dobrze zdefiniowanego podzbioru danych. Używa predykatu filtru do indeksowania części wierszy w tabeli. Dobrze zaprojektowany filtrowany indeks może poprawić wydajność zapytań, a także obniżyć koszty konserwacji i przechowywania indeksów w porównaniu z indeksami pełnostanowymi.

Oto przykład łączenia unikalnego indeksu z predykatem filtru:

create wyjątkowy index [MyIndex]
on [MyTable]([ID])
gdzie [RecordStatus] = 1

Zasadniczo wymusza to wyjątkowość ID gdy RecordStatus jest 1.

Uwaga: filtrowany indeks został wprowadzony w SQL Server 2008. Dla wcześniejszych wersji SQL Server, zobacz ta odpowiedź.


112
2018-03-01 00:37



Zauważ, że SQL Server wymaga ansi_padding dla filtrowanych indeksów, więc upewnij się, że ta opcja jest włączona przez wykonanie SET ANSI_PADDING ON przed utworzeniem przefiltrowanego indeksu. - naXa


Możesz przenieść usunięte rekordy do tabeli, która nie ma ograniczenia, i być może użyć widoku z UNION z dwóch tabel, aby zachować wygląd pojedynczej tabeli.


9
2018-05-14 22:01



To właściwie całkiem sprytny Carl. To nie jest odpowiedź na pytanie jako takie, ale jest to dobre rozwiązanie. Jeśli tabela zawiera wiele wierszy, może to również przyspieszyć wyszukiwanie aktywnego rekordu, ponieważ można spojrzeć na aktywną tabelę rekordów. Przyspieszyłoby to także ograniczenie, ponieważ unikalne ograniczenie używa indeksu w przeciwieństwie do ograniczenia kontroli, które napisałem poniżej, które ma wykonać liczbę. Lubię to. - D. Patrick


Możesz to zrobić w naprawdę hackowy sposób ...

Utwórz widok schemabiku na Twoim stole.

UTWÓRZ WIDOK Cokolwiek WYBIERZ * Z tabeli GDZIE RecordStatus = 1

Teraz utwórz unikalne ograniczenie w widoku z polami, które chcesz.

Jedna uwaga dotycząca widoków schematycznych jednak, jeśli zmienisz podstawowe tabele, będziesz musiał odtworzyć widok. Mnóstwo wytworów z tego powodu.


3
2018-05-14 22:43



Jest to całkiem dobra sugestia, a nie "hacky". Oto więcej informacji na ten temat filtrowany alternatywny indeks. - Scott Whitlock
To zły pomysł. Pytanie nie jest to. - FabianoLothor
Kiedyś użyłem widoku schematycznego i nigdy nie powtórzyłem błędu. Mogą być królewskim bólem do pracy. Nie chodzi o to, że musisz ponownie utworzyć widok, jeśli zmienisz tabelę bazową - musisz to zrobić dla wszystkich widoków, przynajmniej na serwerze SQL. Chodzi o to, że nie można zmienić tabeli bez wcześniejszego opuszczenia widoku, czego może nie być w stanie zrobić bez uprzedniego odrzucenia odnośników. Poza tym pamięć może być problematyczna - z powodu miejsca lub ze względu na koszt, który dodaje do wstawienia i aktualizacji. - MattW


Ponieważ pozwolisz na duplikaty, unikalne ograniczenie nie zadziała. Można utworzyć ograniczenie sprawdzające dla kolumny RecordStatus i procedura składowana dla INSERT, która sprawdza istniejące aktywne rekordy przed wstawieniem duplikatów identyfikatorów.


1
2018-05-14 21:59





Jeśli nie możesz użyć NULL jako RecordStatus, jak zasugerował Bill, możesz połączyć swój pomysł z indeksem opartym na funkcjach. Utwórz funkcję zwracającą wartość NULL, jeśli element RecordStatus nie jest jedną z wartości, które chcesz uwzględnić w ograniczeniu (i inaczej: RecordStatus) i utworzyć indeks nad tym.

Ma to tę zaletę, że nie musisz jawnie sprawdzać innych wierszy w tabeli w swoim ograniczeniu, co może spowodować problemy z wydajnością.

Powinienem powiedzieć, że w ogóle nie znam serwera SQL, ale z powodzeniem zastosowałem to podejście w Oracle.


1
2018-05-14 22:48



Dobry pomysł, ale nie ma funkcji opartej na indeksie w serwerze sql dzięki za odpowiedź - np-hard