Pytanie Ograniczenie kolumny SQL Server, 1 wiersz = 1, wszystkie inne 0


mam bit IsDefault kolumna. Tylko jeden wiersz danych w tabeli może mieć ustawioną tę kolumnę bitową 1wszystkie inne muszą być 0.

Jak mogę to wymusić?


19
2018-01-26 22:09


pochodzenie


Czy rozważałeś alternatywną architekturę? Powiedz, mając odniesienie w innym miejscu, które wskazuje na "domyślny" wiersz. - Anon.
jaka wersja programu SQL Server jest potrzebna? - gbn
Co za świetne pytanie! Piszę to tylko po to, aby wyrazić moje pozostałe +99 punktów wdzięczności. - Andriy M


Odpowiedzi:


Wszystkie wersje:

  • Cyngiel
  • Widok indeksowany
  • Zapisany proc (np. Test przy zapisie)

SQL Server 2008: filtrowany indeks

CREATE UNIQUE INDEX IX_foo ON bar (MyBitCol) WHERE MyBitCol = 1

32
2018-01-26 22:13



Cóż, to z pewnością uniemożliwiłoby im zapisanie wielu rekordów z ustawieniem domyślnym. - NotMe
Indeksy filtrowane wymagają ANSI PADDING ON. Więc wykonaj SET ANSI PADDING ON przed utworzeniem przefiltrowanego indeksu. - naXa
Oba indeksowane widoki i przefiltrowane indeksy zostały wprowadzone w SQL 2008, więc jest to tylko 99% poprawne. - DaveBoltman
Widoczne widoki @DaveBoltman zostały dodane w SQL Server 2000 informit.com/articles/article.aspx?p=25735 lub sqlshack.com/history-sql-server-evolution-sql-server-features lub sqlteam.com/article/indexed-views-in-sql-server-2000 - gbn
@ gbn Dzięki za kształcenie mnie! - DaveBoltman


Zakładając, że twoja PK jest pojedynczą, numeryczną kolumną, możesz dodać kolumnę obliczoną do swojej tabeli:

ALTER TABLE YourTable
  ADD IsDefaultCheck AS CASE IsDefault
     WHEN 1 THEN -1
     WHEN 0 THEN YourPK
  END

Następnie utwórz unikalny indeks w kolumnie obliczeniowej.

CREATE UNIQUE INDEX IX_DefaultCheck ON YourTable(IsDefaultCheck)

17
2018-01-26 22:20



+1 nigdy o tym nie myślał. Rozwiązanie DRI bez przefiltrowanego indeksu - gbn
+ 1 fajne rozwiązanie. - Mitch Wheat
Jest to najlepsze rozwiązanie, moim zdaniem, ponieważ nie wiąże się z żadnymi wyzwalaczami. W rzeczywistości wszystko zawarte w tym rozwiązaniu może zostać prawdopodobnie zaimplementowane w oryginalnej definicji tabeli. - Tripartio


Myślę, że wyzwalacz jest najlepszym pomysłem, jeśli chcesz zmienić stary domyślny rekord na 0 podczas wstawiania / aktualizowania nowego i jeśli chcesz mieć pewność, że jeden rekord zawsze ma tę wartość (np. Jeśli usuniesz rekord z wartością przypiszesz go do innego rekordu). Musiałbyś zdecydować, jakie są zasady. Te wyzwalacze mogą być trudne, ponieważ musisz uwzględnić wiele rekordów we wstawionych i usuniętych tabelach. Więc jeśli 3 rekordy w partii próbują się zaktualizować, aby stać się domyślnym rekordem, który z nich wygrywa?

Jeśli chcesz mieć pewność, że rekord domyślny nigdy się nie zmieni, gdy ktoś inny spróbuje go zmienić, dobrym pomysłem będzie filtrowany indeks.


2
2018-01-26 22:23





Możesz zastosować wyzwalacz Zamiast wkładki i sprawdzić wartość, ponieważ nadchodzi.

Create Trigger TRG_MyTrigger
on MyTable
Instead of Insert
as
Begin

  --Check to see if the row is marked as active....
  If Exists(Select * from inserted where IsDefault= 1)
  Begin
     Update Table Set IsDefault=0 where ID= (select ID from inserted);

     insert into Table(Columns)
     select Columns from inserted
  End

End

Alternatywnie możesz zastosować unikalne ograniczenie w kolumnie.


1
2018-01-26 22:17





Przyjęta odpowiedź na poniższe pytanie jest zarówno interesująca, jak i istotna:

Ograniczenie tylko dla jednego rekordu oznaczonego jako domyślny

"Ale poważni ludzie relacjonujący powiedzą ci tę informację   powinien po prostu być w innym stole. "

Miej oddzielną tabelę z 1 wierszem, która mówi, który rekord jest "domyślny". Anon dotknął tego w swoim komentarzu.

Uważam, że jest to najlepsze podejście - proste, czyste i nie wymaga "sprytnego", ezoterycznego rozwiązania, które może być podatne na błędy lub późniejsze nieporozumienie. Możesz nawet upuścić IsDefualt kolumna.


1
2017-11-21 15:43





Można tu zastosować różne podejścia, ale uważam, że tylko dwie są poprawne. Ale róbmy to krok po kroku.

Mamy stół Hierachy stolik, w którym mamy Korzeń kolumna. Ta kolumna mówi nam, który wiersz jest obecnie punktem wyjścia. Jak pytano, chcemy mieć tylko jeden punkt wyjścia.

simple hierarchy table

Uważamy, że możemy to zrobić za pomocą:

  • Przymus
  • Widok indeksowany
  • Cyngiel
  • Różne tabele i relacje

Przymus

W tym podejściu najpierw musimy stworzyć funkcję, która wykona zadanie.

CREATE FUNCTION [gt].[fnOnlyOneRoot]()
RETURNS BIT
BEGIN
    DECLARE @rootAmount TINYINT
    DECLARE @result BIT
    SELECT @rootAmount=COUNT(1) FROM [gt].[Hierarchy] WHERE [Root]=1

    IF @rootAmount=1 
        set @result=1
    ELSE 
        set @result=0
    RETURN @result
END
GO

A następnie ograniczenie:

ALTER TABLE [gt].[Hierarchy]  WITH CHECK ADD  CONSTRAINT [ckOnlyOneRoot] CHECK  (([gt].[fnOnlyOneRoot]()=(1)))

Niestety podejście jest błędne, ponieważ to ograniczenie nie pozwoli nam zmienić żadnych wartości w tabeli. Musi mieć dokładnie jeden rdzeń oznaczony (wstaw z Root = 1 rzuci wyjątek i zaktualizuje również zbiór Root = 0)

Możemy zmienić fnOnyOneRoot aby umożliwić posiadanie 0 wybranych korzeni, ale nie to, co chcieliśmy.

Indeks

Indeks usunie wszystkie wiersze, które są zdefiniowane w klauzuli where, a na pozostałych danych ustawi unikalne ograniczenie. Mamy tu różne opcje:  - Root może być zerowalny i możemy dodać gdzie Root! = 0 i Root nie ma wartości NULL  - Root musi mieć wartość i możemy dodać tylko tam, gdzie Root! = 0  - i różne kombinacje

CREATE UNIQUE INDEX ix_OnyOneRoot ON [gt].[Hierarchy](Root) WHERE Root !=0 and Root is not null

Takie podejście również nie jest doskonałe. Maksymalnie jeden root zostanie wymuszony, ale minimalnie nie. Aby zaktualizować dane, musimy ustawić poprzednie wiersze na null lub 0.

Cyngiel

Możemy wykonać dwa rodzaje wyzwalaczy, które zachowują się inaczej  - Zapobieganie wyzwalaczowi - który nie pozwoli nam umieścić błędnych danych  - Wyzwalacz DoTheJob - który w tle zaktualizuje dane dla nas

Zapobieganie wyzwalaniu

Jest to w zasadzie to samo, co ograniczenie, jeśli chcemy wymusić tylko jeden root, którego nie możemy zaktualizować ani wstawić.

CREATE TRIGGER tOnlyOneRoot  
ON [gt].[Hierarchy]
AFTER INSERT, UPDATE   
AS
    DECLARE @rootAmount TINYINT
    DECLARE @result BIT
    SELECT @rootAmount=COUNT(1) FROM [gt].[Hierarchy] WHERE [Root]=1

    IF @rootAmount=1 
        set @result=1
    ELSE 
        set @result=0
    IF @result=0 
    BEGIN
    RAISERROR ('Only one root',0,0);  
    ROLLBACK TRANSACTION
    RETURN
    END
GO  

Wyzwalacz DoTheJob

Ten wyzwalacz sprawdzi wszystkie wstawione / zaktualizowane wiersze i jeśli zostanie przekazanych więcej niż jeden Root, zwróci wyjątek. W innym przypadku, więc jeśli jeden nowy katalog główny zostanie zaktualizowany lub wstawiony, wyzwalacz pozwoli to zrobić, a po operacji zmieni wartość katalogu głównego dla wszystkich pozostałych wierszy na 0.

CREATE TRIGGER tOnlyOneRootDoTheJob  
ON [gt].[Hierarchy]
AFTER INSERT, UPDATE   
AS
    DECLARE @insertedCount TINYINT

    SELECT @insertedCount = COUNT(1) FROM inserted WHERE [Root]=1
    if (@insertedCount  > 1)
    BEGIN
        RAISERROR ('Only one root',0,0);  
        ROLLBACK TRANSACTION
    RETURN
    END

    DECLARE @newRootId INT
    SELECT @newRootId = [HierarchyId] FROM inserted WHERE [Root]=1

    UPDATE [gt].[Hierarchy] SET [Root]=0 WHERE [HierarchyId] <> @newRootId

GO  

To jest rozwiązanie, które próbowaliśmy osiągnąć. Zawsze spotyka się tylko jedna zasada główna. (Należy uruchomić dodatkowy czynnik uruchamiający usuwanie)

Różne tabele i relacje

To znaczy, powiedzmy, bardziej znormalizowany sposób. Tworzymy nową tabelę pozwalającą na posiadanie tylko jednego wiersza (przy użyciu opcji opisanych powyżej) i dołączamy.

CREATE TABLE [gt].[HierarchyDefault](
    [HierarchyId] INT PRIMARY KEY NOT NULL,
    CONSTRAINT FK_HierarchyDefault_Hierarchy FOREIGN KEY (HierarchyId) REFERENCES [gt].[Hierarchy](HierarchyId)
    )

Czy uderzy w wydajność?

Z jedną kolumną

SET STATISTICS TIME ON; 
    SELECT [HierarchyId],[ParentHierarchyId],[Root]
    FROM [gt].[Hierarchy] WHERE [root]=1
SET STATISTICS TIME OFF; 

Wynik    Czas procesora = 0 ms, czas, który upłynął = 0 ms.

Z łączeniem:

SET STATISTICS TIME ON; 
    SELECT h.[HierarchyId],[ParentHierarchyId],[Root]
    FROM [gt].[Hierarchy] h
    INNER JOIN [gt].[HierarchyDefault] hd on h.[HierarchyId]=hd.[HierarchyId]
    WHERE [root]=1
 SET STATISTICS TIME OFF; 

Wynik    Czas procesora = 0 ms, czas, który upłynął = 0 ms.

Podsumowanie Będę używał spustu. To trochę magii w stole, ale wykonała całą pracę pod maską.

Łatwe tworzenie tabel:

CREATE TABLE [gt].[Hierarchy](
    [HierarchyId] INT PRIMARY KEY IDENTITY(1,1),
    [ParentHierarchyId] INT NULL,
    [Root] BIT
    CONSTRAINT FK_Hierarchy_Hierarchy FOREIGN KEY (ParentHierarchyId) 
 REFERENCES [gt].[Hierarchy](HierarchyId)
)

0
2018-05-03 10:09