Jak wymusić, aby jeden rekord miał prawdziwą wartość dla kolumny boolowskiej, a wszystkie inne fałszywą wartość?

Chcę wymusić, aby tylko jeden rekord w tabeli był uważany za „domyślną” wartość dla innych zapytań lub widoków, które mogą uzyskać dostęp do tej tabeli.

Zasadniczo chcę zagwarantować, że to zapytanie zawsze zwróci dokładnie jeden wiersz:

SELECT ID, Zip 
FROM PostalCodes 
WHERE isDefault=True

Jak miałbym to zrobić w SQL?

Edycja: jest to ukierunkowane na SQL Server, zanim poznaliśmy MySQL

Istnieją 4 5 sposobów, aby to zrobić: w kolejności od najbardziej do najmniej pożądane pożądane

Nie wiemy, co to jest RDBMS, jednak nie wszystkie mogą mieć zastosowanie

1. Najlepszym sposobem jest filtrowany indeks. Wykorzystuje DRI do zachowania wyjątkowości.

2. Kolumna obliczeniowa z wyjątkowością (patrz odpowiedź Jacka Douglasa) (dodana przez edycję 2)

3. Indeksowany / zmaterializowany widok, który jest jak filtrowany indeks przy użyciu DRI

4. Wyzwalacz (zgodnie z innymi odpowiedziami)

5. Sprawdź ograniczenie za pomocą UDF. Nie jest to bezpieczne w przypadku izolacji współbieżności i migawek. Zobacz Raz Dwa Trzy Cztery

Należy zauważyć, że wymóg „jeden domyślny” znajduje się w tabeli, a nie w procedurze przechowywanej. Procedura składowana będzie miała takie same problemy z współbieżnością jak ograniczenie sprawdzania z udf

Uwaga: wielokrotnie zadawane w SO:

• /programming/4810615/sql-server-bit-column-constraint-1-row-1-all-others-0/4810654#4810654
• /programming/6088361/the-proper-way-to-implement-unique-constraint-that-allows-multiple-null-values-in/6088397#6088397

Uwaga: Ta odpowiedź została udzielona, ​​zanim stało się jasne, że pytający chciał czegoś specyficznego dla MySQL. Ta odpowiedź jest stronnicza w stosunku do SQL Server.

Zastosuj ograniczenie CHECK do tabeli, która wywołuje UDF i upewnia się, że jego wartość zwrotna wynosi <= 1. UDF może po prostu policzyć wiersze w tabeli GDZIE isDefault = TRUE. Zapewni to, że w tabeli nie będzie więcej niż 1 domyślny wiersz.

Powinieneś dodać mapę bitową lub filtrowany indeks do isDefaultkolumny (w zależności od twojego platforom), aby upewnić się, że ten UDF działa bardzo szybko.

Ostrzeżenia

• Sprawdź ograniczenia mają pewne ograniczenia . Mianowicie są one wywoływane dla każdego zmodyfikowanego wiersza, nawet jeśli wiersze te nie zostały jeszcze zatwierdzone w transakcji. Więc jeśli rozpoczniesz transakcję, ustaw nowy wiersz jako domyślny, a następnie usuń zaznaczenie starego wiersza, twoje ograniczenie sprawdzania będzie narzekać. Dzieje się tak, ponieważ zostanie on wykonany po ustawieniu nowego wiersza jako domyślnego, okaże się, że istnieją teraz dwa domyślne i zakończy się niepowodzeniem. Rozwiązaniem jest wtedy, aby najpierw ustawić domyślny wiersz przed ustawieniem nowego domyślnego, nawet jeśli wykonujesz tę pracę w transakcji.
• Jak wskazał gbn , UDF mogą zwracać niespójne wyniki, jeśli używasz izolacji migawek w SQL Server. Jednak wbudowany UDF nie napotyka tego problemu, a ponieważ UDF, który tylko zlicza, może być wbudowany, nie jest to problemem.
• Siła mocy obliczeniowej silnika bazy danych polega na jego zdolności do pracy na zbiorach danych jednocześnie. Dzięki ograniczeniu sprawdzania wspieranemu przez UDF silnik będzie ograniczony do seryjnego stosowania tego UDF do każdego zmodyfikowanego wiersza. W twoim przypadku użycia jest mało prawdopodobne, abyś przeprowadzał masowe aktualizacje PostalCodestabeli, jednak pozostaje to problem z wydajnością w sytuacjach, w których prawdopodobne jest działanie oparte na zestawie.

Biorąc pod uwagę wszystkie powyższe zastrzeżenia, zalecam skorzystanie z sugestii gbn filtrowanego unikalnego indeksu zamiast ograniczenia sprawdzania.

Oto procedura składowana (MySQL Dialect):

DELIMITER $$
DROP PROCEDURE IF EXISTS SetDefaultForZip;
CREATE PROCEDURE SetDefaultForZip (NEWID INT)
BEGIN
    DECLARE FOUND_TRUE,OLDID INT;

    SELECT COUNT(1) INTO FOUND_TRUE FROM PostalCode WHERE isDefault = TRUE;
    IF FOUND_TRUE = 1 THEN
        SELECT ID INTO OLDID FROM PostalCode WHERE isDefault = TRUE;
        IF NEWID <> OLDID THEN
            UPDATE PostalCode SET isDefault = FALSE WHERE ID = OLDID;
            UPDATE PostalCode SET isDefault = TRUE  WHERE ID = NEWID;
        END IF;
    ELSE
        UPDATE PostalCode SET isDefault = TRUE WHERE ID = NEWID;
    END IF;
END;
$$
DELIMITER ;

Aby upewnić się, że tabela jest czysta i procedura przechowywana działa, zakładając, że domyślnym identyfikatorem jest 200, uruchom następujące kroki:

ALTER TABLE PostalCode DROP INDEX isDefault_ndx;
UPDATE PostalCodes SET isDefault = FALSE;
ALTER TABLE PostalCode ADD INDEX isDefault_ndx (isDefault);
CALL SetDefaultForZip(200);
SELECT ID FROM PostalCodes WHERE isDefault = TRUE;

Zamiast procedury przechowywanej, co powiesz na wyzwalacz?

DELIMITER $$
CREATE TRIGGER postalcodes_bu BEFORE UPDATE ON PostalCodes FOR EACH ROW
BEGIN
    DECLARE FOUND_TRUE,OLDID INT;
    IF NEW.isDefault = TRUE THEN
        SELECT COUNT(1) INTO FOUND_TRUE FROM PostalCode WHERE isDefault = TRUE;
        IF FOUND_TRUE = 1 THEN
            SELECT ID INTO OLDID FROM PostalCode WHERE isDefault = TRUE;
            UPDATE PostalCodes SET isDefault = FALSE WHERE ID = OLDID;
        END IF;
    END IF;
END;
$$
DELIMITER ;

Aby upewnić się, że tabela jest czysta i wyzwalacz działa, zakładając, że domyślnym identyfikatorem jest 200, uruchom następujące kroki:

DROP TRIGGER postalcodes_bu;
ALTER TABLE PostalCode DROP INDEX isDefault_ndx;
UPDATE PostalCodes SET isDefault = FALSE;
ALTER TABLE PostalCode ADD INDEX isDefault_ndx (isDefault);
DELIMITER $$
CREATE TRIGGER postalcodes_bu BEFORE UPDATE ON PostalCodes FOR EACH ROW
BEGIN
    DECLARE FOUND_TRUE,OLDID INT;
    IF NEW.isDefault = TRUE THEN
        SELECT COUNT(1) INTO FOUND_TRUE FROM PostalCode WHERE isDefault = TRUE;
        IF FOUND_TRUE = 1 THEN
            SELECT ID INTO OLDID FROM PostalCode WHERE isDefault = TRUE;
            UPDATE PostalCodes SET isDefault = FALSE WHERE ID = OLDID;
        END IF;
    END IF;
END;
$$
DELIMITER ;
UPDATE PostalCodes SET isDefault = TRUE WHERE ID = 200;
SELECT ID FROM PostalCodes WHERE isDefault = TRUE;

Możesz użyć wyzwalacza, aby zastosować reguły. Gdy instrukcja UPDATE lub INSERT ustawia isDefault na True, SQL w wyzwalaczu może ustawić wszystkie inne wiersze na False.

Stosując to, musisz wziąć pod uwagę inne sytuacje. Na przykład, co powinno się zdarzyć, jeśli więcej niż jeden wiersz i zestawy UPDATE lub INSERT mają wartość domyślną True? Jakie zasady będą obowiązywać, aby uniknąć złamania reguły?

Czy to możliwe, że stan nie ma wartości domyślnej? Co się stanie, gdy aktualizacja ustawi wartość isDefault z True na False.

Po zdefiniowaniu reguł możesz je wbudować w wyzwalacz.

Następnie, jak wskazano w innej odpowiedzi, chcesz zastosować ograniczenie sprawdzania, aby pomóc w egzekwowaniu reguł.

Następujące zestawia przykładową tabelę i dane:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]') AND type in (N'U'))
DROP TABLE [dbo].[MyTable]
GO

CREATE TABLE dbo.MyTable
(
    [id] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
    , [IsDefault] BIT DEFAULT 0
)
GO

INSERT dbo.MyTable DEFAULT VALUES
GO 100

Jeśli utworzysz procedurę składowaną, aby ustawić flagę IsDefault i usuniesz uprawnienia do zmiany tabeli podstawowej, możesz wymusić to za pomocą poniższego zapytania. Wymagałoby to za każdym razem skanowania tabeli.

DECLARE @Id INT
SET @Id = 10

UPDATE
    dbo.MyTable
SET
    IsDefault = CASE WHEN [id] = @Id THEN 1 ELSE 0 END 
GO

W zależności od wielkości tabeli indeks IsDefault (DESC) może spowodować jedno lub oba poniższe zapytania, unikając pełnego skanowania.

DECLARE @Id INT
SET @Id = 10

UPDATE
    dbo.MyTable
SET
    IsDefault = CASE WHEN [id] = @Id THEN 1 ELSE 0 END
FROM
    dbo.MyTable
WHERE
    [id] = @Id
OR  IsDefault = 1

UPDATE
    dbo.MyTable
SET
    IsDefault = CASE WHEN [id] = @Id THEN 1 ELSE 0 END
FROM
    dbo.MyTable
WHERE
    [id] = @Id
OR  ([id] != @Id AND IsDefault = 1)

Jeśli nie możesz usunąć uprawnień z tabeli podstawowej, musisz egzekwować integralność w inny sposób i korzystasz z SQL2008, możesz skorzystać z filtrowanego unikalnego indeksu:

CREATE UNIQUE INDEX IX_MyTable_IsDefault  ON dbo.MyTable (IsDefault) WHERE IsDefault = 1

W przypadku MySQL, który nie ma przefiltrowanych indeksów, możesz wykonać następujące czynności. Wykorzystuje fakt, że MySQL dopuszcza wiele wartości NULL w unikalnych indeksach, i wykorzystuje dedykowaną tabelę i klucz obcy do symulacji typu danych, który może mieć tylko NULL i 1 jako wartości.

Dzięki temu rozwiązaniu zamiast wartości prawda / fałsz użyłbyś po prostu 1 / NULL.

CREATE TABLE DefaultFlag (id TINYINT UNSIGNED NOT NULL PRIMARY KEY);
INSERT INTO DefaultFlag (id) VALUES (1);

CREATE TABLE PostalCodes (
    ID INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    Zip VARCHAR (32) NOT NULL,
    isDefault TINYINT UNSIGNED NULL DEFAULT NULL,
    CONSTRAINT FOREIGN KEY (isDefault) REFERENCES DefaultFlag (id),
    UNIQUE KEY (isDefault)
);

INSERT INTO PostalCodes (Zip, isDefault) VALUES ('123', 1   );
/* Only one row can be default */
INSERT INTO PostalCodes (Zip, isDefault) VALUES ('abc', 1   );
/* ERROR 1062 (23000): Duplicate entry '1' for key 'isDefault' */

/* MySQL allows multiple NULLs in unique indexes */
INSERT INTO PostalCodes (Zip, isDefault) VALUES ('456', NULL);
INSERT INTO PostalCodes (Zip, isDefault) VALUES ('789', NULL);

/* only NULL and 1 are admitted in isDefault thanks to foreign key */
INSERT INTO PostalCodes (Zip, isDefault) VALUES ('789', 2   );
/* ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`PostalCodes`, CONSTRAINT `PostalCodes_ibfk_1` FOREIGN KEY (`isDefault`) REFERENCES `DefaultFlag` (`id`))*/

Myślę, że jedyne, co może pójść nie tak, to to, że ktoś doda wiersz do DefaultFlagtabeli. Myślę, że nie jest to duży problem i zawsze możesz to naprawić za pomocą uprawnień, jeśli naprawdę chcesz być bezpieczny.

SELECT ID, Zip FROM PostalCodes WHERE isDefault=True 

To w zasadzie sprawiało ci problemy, ponieważ umieściłeś pole w niewłaściwym miejscu i to wszystko.

Mają tabelę „Domyślne” z kodem pocztowym, która zawiera identyfikator, którego szukasz. Zasadniczo dobrze jest również nazywać swoje tabele zgodnie z jednym rekordem, więc lepiej byłoby nazwać swoją tabelę początkową „Kod pocztowy”. Domyślne będą tabelą z jednym wierszem, dopóki nie będziesz musiał wyspecjalizować swoich domyślnych ustawień w stosunku do niektórych innych konfiguracji (takich jak historia).

Ostateczne zapytanie, które chcesz, jest następujące:

select Zip from PostalCode p, Defaults d where p.ID=d.PostalCode;