Jak wdrożyć flagę „domyślną”, którą można ustawić tylko w jednym wierszu

Na przykład z tabelą podobną do tej:

create table foo(bar int identity, chk char(1) check (chk in('Y', 'N')));

Nie ma znaczenia, czy flaga jest zaimplementowana jako a char(1), bitczy cokolwiek innego. Chcę tylko móc wymusić ograniczenie, które można ustawić tylko w jednym wierszu.

SQL Server 2008 - Filtrowany unikalny indeks

CREATE UNIQUE INDEX IX_Foo_chk ON dbo.Foo(chk) WHERE chk = 'Y'

SQL Server 2000, 2005:

Możesz skorzystać z faktu, że tylko jeden null jest dozwolony w unikalnym indeksie:

create table t( id int identity, 
                chk1 char(1) not null default 'N' check(chk1 in('Y', 'N')), 
                chk2 as case chk1 when 'Y' then null else id end );
create unique index u_chk on t(chk2);

w 2000 roku możesz potrzebować SET ARITHABORT ON(dzięki @gbn za te informacje)

Wyrocznia:

Ponieważ Oracle nie indeksuje wpisów, w których wszystkie indeksowane kolumny są puste, możesz użyć unikalnego indeksu opartego na funkcjach:

create table foo(bar integer, chk char(1) not null check (chk in('Y', 'N')));
create unique index idx on foo(case when chk='Y' then 'Y' end);

Indeks ten indeksuje tylko najwyżej jeden wiersz.

Znając ten fakt indeksu, możesz również nieco zaimplementować kolumnę bitową:

create table foo(bar integer, chk char(1) check (chk ='Y') UNIQUE);

Tutaj możliwe wartości dla kolumny chkto Yi NULL. Tylko jeden wiersz może mieć tę wartośćY.

Myślę, że jest to przypadek prawidłowej strukturyzacji tabel w bazie danych. Aby bardziej konkretnie, jeśli masz osobę z wieloma adresami i chcesz, aby jeden był domyślny, myślę, że powinieneś przechowywać identyfikator adresu domyślnego adresu w tabeli osób, a nie mieć domyślnej kolumny w tabeli adresów:

Person
-------
PersonID
Name
etc.
DefaultAddressID (fk to addressID)

Address
--------
AddressID
Street
City, State, Zip, etc.

Możesz ustawić DefaultAddressID na wartość zerową, ale w ten sposób struktura wymusza ograniczenie.

MySQL:

create table foo(bar serial, chk boolean unique);
insert into foo(chk) values(null);
insert into foo(chk) values(null);
insert into foo(chk) values(false);
insert into foo(chk) values(true);

select * from foo;
+-----+------+
| bar | chk  |
+-----+------+
|   1 | NULL |
|   2 | NULL |
|   3 |    0 |
|   4 |    1 |
+-----+------+

insert into foo(chk) values(true);
ERROR 1062 (23000): Duplicate entry '1' for key 2
insert into foo(chk) values(false);
ERROR 1062 (23000): Duplicate entry '0' for key 2

Sprawdź ograniczenia są ignorowane w MySQL więc mamy do rozważenia nulllub falsejako fałszywe i truejak prawdziwe. Może mieć maksymalnie 1 wierszchk=true

Można to poprawa dodać wyzwalacz do zmiany rozważyć falsew truesprawie Insert / aktualizacji jako obejście braku przymusu wyboru - IMO nie jest to poprawa choć.

Miałem nadzieję, że będę w stanie użyć char (0), ponieważ to

jest również całkiem przyjemny, gdy potrzebujesz kolumny, która może przyjmować tylko dwie wartości: Kolumna zdefiniowana jako CHAR (0) NULL zajmuje tylko jeden bit i może przyjmować tylko wartości NULL i ''

Niestety, przynajmniej z MyISAM i InnoDB

ERROR 1167 (42000): The used storage engine can't index column 'chk'

--edytować

nie jest to jednak dobre rozwiązanie, ponieważ na MySQL booleanjest synonimemtinyint(1) , a zatem dopuszcza wartości inne niż zero od 0 lub 1. Możliwe, że bitbyłby to lepszy wybór

SQL Server:

Jak to zrobić:

1. Najlepszym sposobem jest filtrowany indeks. Wykorzystuje DRI
   SQL Server 2008+

2. Kolumna obliczeniowa o wyjątkowości. Wykorzystuje DRI
   Patrz odpowiedź Jacka Douglasa. SQL Server 2005 i wcześniejsze wersje

3. Indeksowany / zmaterializowany widok, który jest jak filtrowany indeks. Wykorzystuje DRI
   Wszystkie wersje.

4. Wyzwalacz. Używa kodu, a nie DRI.
   Wszystkie wersje

Jak tego nie robić:

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

PostgreSQL:

create table foo(bar serial, chk char(1) unique check(chk='Y'));
insert into foo default values;
insert into foo default values;
insert into foo(chk) values('Y');

select * from foo;
 bar | chk
-----+-----
   1 |
   2 |
   3 | Y

insert into foo(chk) values('Y');
ERROR:  duplicate key value violates unique constraint "foo_chk_key"

--edytować

lub (znacznie lepiej) użyj unikalnego indeksu częściowego :

create table foo(bar serial, chk boolean not null default false);
create unique index foo_i on foo(chk) where chk;
insert into foo default values;
insert into foo default values;
insert into foo(chk) values(true);

select * from foo;
 bar | chk
-----+-----
   1 | f
   2 | f
   3 | t
(3 rows)

insert into foo(chk) values(true);
ERROR:  duplicate key value violates unique constraint "foo_i"

Ten rodzaj problemu jest kolejnym powodem, dla którego zadałem to pytanie:

Ustawienia aplikacji w bazie danych

Jeśli w bazie danych znajduje się tabela ustawień aplikacji, może istnieć wpis odwołujący się do identyfikatora jednego rekordu, który ma być uważany za „specjalny”. Następnie po prostu sprawdź, jaki jest identyfikator z tabeli ustawień, w ten sposób nie potrzebujesz całej kolumny, aby ustawić tylko jeden element.

Możliwe podejścia z wykorzystaniem szeroko wdrażanych technologii:

1) Odwołaj uprawnienia „pisarza” na stole. Utwórz procedury CRUD, które zapewniają wymuszanie ograniczenia na granicach transakcji.

2) 6NF: upuść CHAR(1)kolumnę. Dodaj tabelę odwołań ograniczoną, aby jej liczność nie mogła przekraczać jednego:

alter table foo ADD UNIQUE (bar);

create table foo_Y
(
 x CHAR(1) DEFAULT 'x' NOT NULL UNIQUE CHECK (x = 'x'), 
 bar int references foo (bar)
);

Zmień semantykę aplikacji, aby rozważanym „domyślnym” był wiersz w nowej tabeli. Ewentualnie użyj widoków, aby zamknąć tę logikę.

3) Upuść CHAR(1)kolumnę. Dodaj seqkolumnę całkowitą. Umieść wyjątkowe ograniczenie seq. Zmień semantykę aplikacji, tak aby uważany za „domyślny” był wiersz, w którym seqwartością jest jedna lub seqwartość największa / najmniejsza lub podobna. Ewentualnie użyj widoków, aby zamknąć tę logikę.

Dla tych, którzy używają MySQL, oto odpowiednia procedura przechowywana:

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;

Oto wyzwalacz, który również pomaga:

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;

Spróbuj !!!

W SQL Server 2000 i nowszych można używać widoków indeksowanych do implementowania złożonych (lub wielostołowych) ograniczeń, takich jak to, o które prosisz.
Oracle ma również podobną implementację dla widoków zmaterializowanych z ograniczeniami odroczonego sprawdzania.

Zobacz mój post tutaj .

Standardowy przejściowy SQL-92, szeroko implementowany np. SQL Server 2000 i nowsze wersje:

Odwołaj uprawnienia „pisarza” ze stołu. Tworzenie dwóch widoków WHERE chk = 'Y'i WHERE chk = 'N'odpowiednio, w tym WITH CHECK OPTION. Do WHERE chk = 'Y'widoku dołącz warunek wyszukiwania, aby jego liczność nie mogła przekroczyć jednego. Przyznaj uprawnienia „pisarza” do widoków.

Przykładowy kod widoków:

CREATE VIEW foo_chk_N
AS
SELECT *
  FROM foo AS f1
 WHERE chk = 'N' 
WITH CHECK OPTION

CREATE VIEW foo_chk_Y
AS
SELECT *
  FROM foo AS f1
 WHERE chk = 'Y' 
       AND 1 >= (
                 SELECT COUNT(*)
                   FROM foo AS f2
                  WHERE f2.chk = 'Y'
                )
WITH CHECK OPTION

Oto rozwiązanie dla MySQL i MariaDB z wykorzystaniem wirtualnych kolumn, które są nieco bardziej eleganckie. Wymaga MySQL> = 5.7.6 lub MariaDB> = 5.2:

MariaDB [db]> create table foo(bar varchar(255), chk boolean);

MariaDB [db]> describe foo;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| bar   | varchar(255) | YES  |     | NULL    |       |
| chk   | tinyint(1)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Utwórz wirtualną kolumnę o wartości NULL, jeśli nie chcesz wymuszać ograniczenia Unique:

MariaDB [db]> ALTER table foo ADD checked_bar varchar(255) as (IF(chk, bar, null)) PERSISTENT UNIQUE;

(W przypadku MySQL należy użyć STOREDzamiast PERSISTENT.)

MariaDB [db]> insert into foo(bar, chk) values('a', false);
Query OK, 1 row affected (0.00 sec)

MariaDB [db]> insert into foo(bar, chk) values('a', false);
Query OK, 1 row affected (0.01 sec)

MariaDB [salt_dev]> insert into foo(bar, chk) values('a', false);
Query OK, 1 row affected (0.00 sec)

MariaDB [db]> insert into foo(bar, chk) values('a', true);
Query OK, 1 row affected (0.00 sec)

MariaDB [db]> insert into foo(bar, chk) values('a', true);
ERROR 1062 (23000): Duplicate entry 'a' for key 'checked_bar'

MariaDB [db]> insert into foo(bar, chk) values('b', true);
Query OK, 1 row affected (0.00 sec)

MariaDB [db]> select * from foo;
+------+------+-------------+
| bar  | chk  | checked_bar |
+------+------+-------------+
| a    |    0 | NULL        |
| a    |    0 | NULL        |
| a    |    0 | NULL        |
| a    |    1 | a           |
| b    |    1 | b           |
+------+------+-------------+

Standardowy PEŁNY SQL-92: użyj podkwerendy w CHECKograniczeniu, nie jest powszechnie implementowane, np. Obsługiwane w Access2000 (ACE2007, Jet 4.0, cokolwiek) i wyżej, gdy jest w trybie zapytania ANSI-92 .

Przykładowy kod: CHECKograniczenia notatek w programie Access są zawsze na poziomie tabeli. Ponieważ CREATE TABLEstwierdzenie w pytaniu wykorzystuje CHECKograniczenie na poziomie wiersza , należy je nieznacznie zmienić, dodając przecinek:

create table foo(bar int identity, chk char(1), check (chk in('Y', 'N')));

ALTER TABLE foo ADD 
   CHECK (1 >= (
                SELECT COUNT(*) 
                  FROM foo AS f2 
                 WHERE f2.chk = 'Y'
               ));

Przeglądałem tylko odpowiedzi, więc mogłem nie zauważyć podobnej odpowiedzi. Chodzi o to, aby użyć wygenerowanej kolumny, która jest albo pk, albo stałą, która nie istnieje jako wartość dla pk

create table foo 
(  bar int not null primary key
,  chk char(1) check (chk in('Y', 'N'))
,  some_name generated always as ( case when chk = 'N' 
                                        then bar 
                                        else -1 
                                   end )
, unique (somename)
);

AFAIK jest to poprawne w SQL2003 (ponieważ szukasz rozwiązania agnostycznego). DB2 na to pozwala, nie wiadomo, ilu innych dostawców to akceptuje.