Utilisation de la clause UNIQUE en SQLITE3

Citation:

Envoyé par santuD

Je cherche à ce que un des champs de ma table soit unique.
Mais ca ne marche pas ou je ne comprend pas la synthaxe SQLITE3.

Bonsoir,

Regarde ici : http://fr-sqlite.isuisse.com/lang_createtable.html
J'ai commencé la traduction de la doc, cela peut t'aider.

Voici un exemple de création d'une table avec une clé unique.
Allons au plus simple, imaginons que l'on crée un logiciel pour gérer sa cave à vin (à consommer blah blah blah...) :

Voici la table des appellations :
Code:

1 2 3 4 5 CREATE TABLE Apl ( IDApl INTEGER NOT NULL PRIMARY KEY, IDReg INTEGER NOT NULL, LibApl VARCHAR(50) );
Nous avons une clé unique IDApl qui va s'auto incrémenter si on ne force pas sa valeur. C'est l'identifiant de la table.

Voici la table des régions :
Code:

1 2 3 4 5 CREATE TABLE Reg ( IDReg INTEGER NOT NULL PRIMARY KEY, CodReg VARCHAR(10), LibReg VARCHAR(50) );
On remarque là encore un identifiant, celui de la région : IDReg
C'est d'ailleurs une clé étrangère de la première table.

Dans cet exemple, j'ai volontairement créé un code région (CodReg) qui sera unique. Pour créer cette unicité, je met en place un index sur cette colonne :

Code:

CREATE UNIQUE INDEX IF NOT EXISTS idxCodReg ON Reg (CodReg);

Dès lors, je ne peux plus insérer deux régions avec le même CodReg.

Si je veux en plus contrôler qu'une appellation est bien liée à une région existante, je met en place un trigger :
Code:

1 2 3 4 5 6 7 8 9 CREATE TRIGGER fki_AplReg BEFORE INSERT ON Apl FOR EACH ROW BEGIN SELECT CASE WHEN ((SELECT IDReg FROM Reg WHERE IDReg = NEW.IDReg) IS NULL) THEN RAISE(ABORT, 'fki_AplReg: Aucun enregistrement connexe dans la table Reg pour le champ IDReg.') END; END;
A présent, c'est impossible de créer une appellation sans mettre un identifiant valable de région.
Dans le même genre, il faut gérer la suppression d'une région : supprimer en cascade les appellations, la modification d'une appellation : vérifier que la nouvelle région existe bien... à toi de créer ces triggers si tu veux poursuivre...

Allez, on entre quelques lignes :
Code:

1 2 3 4 5 6 7 8 9 INSERT INTO "Reg" VALUES(1, 'ALSAC-VT', 'Alsace'); INSERT INTO "Reg" VALUES(2, 'BORDE-KV', 'Bordeaux'); INSERT INTO "Reg" VALUES(3, 'BOURG-QO', 'Bourgogne'); INSERT INTO "Apl" VALUES(1, 2, 'Bordeaux Supérieur'); INSERT INTO "Apl" VALUES(2, 2, 'Haut-Médoc'); INSERT INTO "Apl" VALUES(3, 2, 'Graves');
Tout ce passe bien.

Je tente deux doublons : erreur, puis un identifiant de région qui n'existe pas : erreur, enfin je tente un doublon de code région : erreur.
Code:

1 2 3 4 5 6 7 8 9 -- un doublon INSERT INTO "Apl" VALUES(3, 2, 'Graves de Vayres'); -- identifiant de région qui n'existe pas INSERT INTO "Apl" VALUES(4, 5, 'Moulis en Médoc'); -- un code région en double INSERT INTO "Reg" VALUES(4, 'BOURG-QO', 'Jura et Savoie');
Voilà

Si tu rejoues ces script dans l'ordre avec un exécutable SQLite (version 3.4 et supérieures) tu comprendras le mécanisme des clés primaires, index uniques et contraintes d'intégrité avec SQLite.

a+

PS: voici le résultat du script pour les plus septiques :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 SQLite version 3.5.8 Enter ".help" for instructions sqlite> CREATE TABLE Apl ( ...> IDApl INTEGER NOT NULL PRIMARY KEY, ...> IDReg INTEGER NOT NULL, ...> LibApl VARCHAR(50) ...> ); sqlite> CREATE TABLE Reg ( ...> IDReg INTEGER NOT NULL PRIMARY KEY, ...> CodReg VARCHAR(10), ...> LibReg VARCHAR(50) ...> ); sqlite> CREATE UNIQUE INDEX IF NOT EXISTS idxCodReg ON Reg (CodReg); sqlite> CREATE TRIGGER fki_AplReg ...> BEFORE INSERT ON Apl ...> FOR EACH ROW BEGIN ...> SELECT CASE ...> WHEN ((SELECT IDReg FROM Reg WHERE IDReg = NEW.IDReg) IS NULL) ...> THEN RAISE(ABORT, 'fki_AplReg: Aucun enregistrement connexe dans la table Reg pour le champ IDReg.') ...> END; ...> END; sqlite> INSERT INTO "Reg" VALUES(1, 'ALSAC-VT', 'Alsace'); sqlite> INSERT INTO "Reg" VALUES(2, 'BORDE-KV', 'Bordeaux'); sqlite> INSERT INTO "Reg" VALUES(3, 'BOURG-QO', 'Bourgogne'); sqlite> sqlite> sqlite> INSERT INTO "Apl" VALUES(1, 2, 'Bordeaux Supérieur'); sqlite> INSERT INTO "Apl" VALUES(2, 2, 'Haut-Médoc'); sqlite> INSERT INTO "Apl" VALUES(3, 2, 'Graves'); sqlite> -- un doublon sqlite> INSERT INTO "Apl" VALUES(3, 2, 'Graves de Vayres'); SQL error: PRIMARY KEY must be unique sqlite> -- identifiant de région qui n'existe pas sqlite> INSERT INTO "Apl" VALUES(4, 5, 'Moulis en Médoc'); SQL error: fki_AplReg: Aucun enregistrement connexe dans la table Reg pour le ch amp IDReg. sqlite> -- un code région en double sqlite> INSERT INTO "Reg" VALUES(4, 'BOURG-QO', 'Jura et Savoie'); SQL error: column CodReg is not unique sqlite>