Jak zajistit aby jen jedna hodnota ve sloupci byla unikátní, ostatní ne

lukendo

Zdravím, mám tabulku která má sloupec final typu bool a zajímalo by mě, jak zajistit aby x řádků mohlo mít ten atribut false a pouze jeden true. Napadlo mě, že po každý, když bych chtěl někde nastavit true, tak bych zkontroloval, zda tam už true je, ale spíš bych uvítal kdyby to šlo řešit přímo v databázi nějakým uniqem nebo tak. Děkuji.

GEpic

UNIQUE v databázi je, ale znamená že každá hodnota záznamu ve sloupci musí být unikátní, což by u bool znamenalo maximálně 2 záznamy.

Jednoduché řešení je při nastavování tohoto ‚true‘ jednoduše všechny hodnoty ve sloupci nastavit na ‚false‘.

jiri.pudil

Jde to řešit přímo v databázi, buďto snadno přes unique partial index, který ovšem ne každá databáze umí (PostgreSQL, SQL Server a SQLite ano):

CREATE UNIQUE INDEX only_one_final ON table (final) WHERE final = TRUE;

nebo s troškou víc psaní pomocí triggeru, viz http://dba.stackexchange.com/…nt-for-mysql

lukendo

jiri.pudil napsal(a):

Jde to řešit přímo v databázi, buďto snadno přes unique partial index, který ovšem ne každá databáze umí (PostgreSQL, SQL Server a SQLite ano):
CREATE UNIQUE INDEX only_one_final ON table (final) WHERE final = TRUE;
nebo s troškou víc psaní pomocí triggeru, viz http://dba.stackexchange.com/…nt-for-mysql

používám MySql, takže to udělám přes nastavení na false a pak na true toho jednoho řádku

h4kuna

A kdyby ti false reprezentoval NULL? NULL pod unique klíčem může být vícekrát a budeš to mít ohlídaný v databázi.

Ukázka pro mysql, určitě to jde i pro postgres kde ty triggery půjdou přepsat na jeden

CREATE TABLE `foo` (
  `id` int NOT NULL PRIMARY KEY,
  `bar_bool` tinyint(1) unsigned NULL
) ENGINE='InnoDB';

ALTER TABLE `foo` ADD UNIQUE `bar_bool` (`bar_bool`);

-- přidal bych trigger aby 0 převedl na NULL a jiný číslo != 0 převedl na 1
DELIMITER ;;
CREATE FUNCTION `bool_validate` (`in_bool` smallint) RETURNS tinyint unsigned
BEGIN
IF NULLIF(in_bool, 0) IS NULL THEN
  RETURN NULL;
END IF;
RETURN 1;
END;;
DELIMITER ;

DELIMITER ;;
CREATE TRIGGER `foo_bi` BEFORE INSERT ON `foo` FOR EACH ROW
BEGIN
SET new.bar_bool := bool_validate(new.bar_bool);
END;;
DELIMITER ;

DELIMITER ;;
CREATE TRIGGER `foo_bu` BEFORE UPDATE ON `foo` FOR EACH ROW
BEGIN
SET new.bar_bool := bool_validate(new.bar_bool);
END;;
DELIMITER ;

INSERT INTO foo (id, bar_bool) VALUES (1, NULL), (2, NULL);

UPDATE foo SET bar_bool = 50 WHERE id = 1;

-- Tento update selže
UPDATE foo SET bar_bool = 1 WHERE id = 2;

-- ukázka převodu 0 na NULL
INSERT INTO foo (id, bar_bool) VALUES (3, 0), (4, 0);

Editoval h4kuna (23. 9. 2016 8:17)