Comment créer des contraintes d'index uniques sur plusieurs colonnes NULLABLE?

membres

Si je définis un index de clé unique avec des NULLABLEcolonnes, je peux insérer autant de lignes que je le souhaite:

create table routes (
   id bigint(20) NOT NULL AUTO_INCREMENT,
   firstname varchar(255) NOT NULL,
   lastname varchar(255) NOT NULL,
   departure DATE DEFAULT NULL,
   returndate DATE DEFAULT NULL,
   PRIMARY KEY (id),
   UNIQUE KEY uniq (firstname, lastname, departure)
)

Pourquoi? Et plus important: comment puis-je conserver la contrainte unique également sur les colonnes de date, bien qu'elles puissent être nulles (et doivent rester de DATEtype)?

L'instruction SQL suivante peut en fait être exécutée plusieurs fois:

INSERT INTO `routes` (`firstname`, `lastname`, `departure`, `returndate`) 
       VALUES (NULL, 'john', 'doe', NULL, NULL);
Bill Karwin

Pourquoi? Parce que ce NULL = NULLn'est pas vrai.

L'unicité signifie qu'il ne permet pas à une autre ligne d'avoir la même valeur dans une colonne. Mais en SQL, NULL par rapport à toute autre valeur - y compris un autre NULL - est "inconnu". C'est ainsi que la logique booléenne à trois valeurs est définie.

https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-unique dit:

Un index UNIQUE autorise plusieurs valeurs NULL pour les colonnes pouvant contenir NULL.

À quoi vous attendez-vous si vous effectuez deux fois votre INSERT? Doit-il entraîner un conflit de clé en double même si le "doublon" est un NULL?

Vous devrez créer un index fonctionnel (disponible sur MySQL 8.0.13 ou version ultérieure):

mysql> CREATE TABLE `routes` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `firstname` varchar(255) NOT NULL,
  `lastname` varchar(255) NOT NULL,
  `departure` date DEFAULT NULL,
  `returndate` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq` (`firstname`,`lastname`,(coalesce(`departure`, '1900-01-01')))
);

mysql> INSERT INTO `routes` (`id`, `firstname`, `lastname`, `departure`, `returndate`) 
    -> VALUES (NULL, 'john', 'doe', NULL, NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `routes` (`id`, `firstname`, `lastname`, `departure`, `returndate`) 
    ->  VALUES (NULL, 'john', 'doe', NULL, NULL);
ERROR 1062 (23000): Duplicate entry 'john-doe-1900-01-01' for key 'uniq'

Re votre commentaire: Non, coalesce () dans cet index fonctionnel n'affecte pas la valeur des données stockées dans la colonne, il n'affecte que ce qui est indexé .

mysql> select * from routes;
+----+-----------+----------+-----------+------------+
| id | firstname | lastname | departure | returndate |
+----+-----------+----------+-----------+------------+
|  1 | john      | doe      | NULL      | NULL       |
+----+-----------+----------+-----------+------------+

Si vous utilisez MySQL 5.7+, vous pouvez faire quelque chose de similaire, mais vous devez créer une colonne virtuelle à utiliser pour l'index: 

CREATE TABLE `routes` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(255) NOT NULL,
  `lastname` varchar(255) NOT NULL,
  `departure` date DEFAULT NULL,
  `departure_notnull` date GENERATED ALWAYS AS (coalesce(`departure`, '1900-01-01')) VIRTUAL,
  `returndate` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq` (`firstname`,`lastname`,`departure_notnull`)
)

Si vous utilisez MySQL 5.6 ou une version antérieure, vous ne pouvez pas utiliser de colonnes virtuelles ou d'index fonctionnels. Vous devrez rendre la colonne NOT NULL et utiliser une valeur spéciale pour signifier qu'il s'agit d'une non-date.

Articles connexes

Comment créer des contraintes uniques sur AgensGraph

jagger Je veux utiliser la propriété "id" comme clé primaire sur l'étiquette. agens=# create vlabel v; CREATE VLABEL agens=# create (:v{id:1}); GRAPH WRITE (INSERT VERTEX 1, INSERT EDGE 0) agens=# create (:v{id:1}); GRAPH WRITE (INSERT VERTEX 1, INSERT EDGE 0)

Mysql - créer des contraintes uniques sur les deux champs

yoann84 J'utilise mysql (mariadb). J'ai une table "events_ratings", qui contient 4 colonnes CREATE TABLE `event_ratings` ( `id` int(11) NOT NULL AUTO_INCREMENT, `id_event` int(11) NOT NULL, `rating` int(1) NOT NULL, `id_user` char(36) CHARACTER SET utf

Comment créer des contraintes uniques dans laravel 5.2?

Carlos Carucce Comment créer des contraintes uniques à l'aide de laravel 5.2 Schema Builder? Schema::create('my_pivot', function(Blueprint $table){ $table->increments('id'); $table->integer('table1_id')->unsigned(); $table->integer('table2_id')->u

Comment écrire correctement des contraintes sur plusieurs index ?

Joep J'essaie de mettre en œuvre un problème de planification des employés (infirmières) et de demander des conseils sur la façon de mettre en œuvre une contrainte spécifique. Le problème est le suivant : il existe un ensemble d'employés et de jours (tous deux