COLLATION et Contraintes d'unicité

**simard** · 27/02/2014, 16h33

Bonjour.

Ma base de données comporte plusieurs tables de codes du type
DICO(dico_id ENTIER, dico_code VARCHAR, dico_libelle VARCHAR, ...).
Elle comporte aussi plusieurs tables du type
TABLE_1(tbl1_id ENTIER, tbl1_reference VARCHAR, dico_id ENTIER, ...), où dico_id est une clé étrangère référençant la table DICO.
Dans la colonne dico_code , chaque valeur doit être unique, plus précisément on ne peut pas avoir deux valeurs avec des lettres identiques mais une casse différente (si xyz présent alors xYz interdit). La même contrainte existe pour la colonne tbl1_reference.

Quelle est la stratégie systématique à adopter ?
Créer les colonnes avec une collation CASE INSENSITIVE et installer une contrainte UNIQUE
Ou bien par exemple
Assurer l’unicité avec une contrainte CHECK qui fait une vérification qu’il n’existe pas de valeur telle que UPPER(dico_code) = UPPER(le_dico_code_a_inserer). Dans ce cas, lorsqu’on veut insérer une ligne (une_tbl1_reference, un_dico_code, ...) dans la table TABLE_1, il faut utiliser encore UPPER() de la recherche de l’identifiant dico_id pour qu’elle ne tienne pas compte de la casse.

Je prends systématiquement la première stratégie. Par conséquent, j’ai de « nombreuses » tables dans lesquelles il existe une colonne avec une collation CS.
Je ne sais pas s’il y a des contre-indications à faire de cette façon, ou s’il y a une autre façon de faire. Est-il intéressant d’ajouter d’autres paramètres de collation ?
Je vous remercie d’avance de partager avec moi ce que vous faites habituellement.

**SQLpro** · 28/02/2014, 13h43

La contrainte UNIQUE avec collation est la seule réponse possible. En effet une contrainte complexe CHECK portant sur toutes les lignes couterait trop cher, raison pour laquelle peu de SGBDR la supporte !

A +

**simard** · 03/03/2014, 11h33

Merci de cette réponse SQLPRO.

Avec SQL SERVER, je n'ai pas de mal à créer une colonne avec collation.
Avec Postgres, je n'ai pas réussi car les seules collations disponibles dans ma BD sont C, Posix et default. J'ai simplement fini par créer un index UNIQUE sur UPPER(la colonne) et je ne traite pas la problématique sur les accents pour l'instant.

En ce qui concerne les majuscules, est-ce que ma solution dans Postgres est une alternative correcte à la colonne avec collation? Ou bien dois-je aller plus loin? ... ie quel que soit le SGBD, peut-on toujours implanter la solution colonne avec collation?

**punkoff** · 03/03/2014, 11h47

un peu de lecture : http://www.postgresql.org/docs/9.1/s...collation.html

**SQLpro** · 03/03/2014, 18h48

Envoyé par simard

Merci de cette réponse SQLPRO.

Avec SQL SERVER, je n'ai pas de mal à créer une colonne avec collation.
Avec Postgres, je n'ai pas réussi car les seules collations disponibles dans ma BD sont C, Posix et default. J'ai simplement fini par créer un index UNIQUE sur UPPER(la colonne) et je ne traite pas la problématique sur les accents pour l'instant.

En ce qui concerne les majuscules, est-ce que ma solution dans Postgres est une alternative correcte à la colonne avec collation? Ou bien dois-je aller plus loin? ... ie quel que soit le SGBD, peut-on toujours implanter la solution colonne avec collation?

Le support des collation est extrêmement faible dans PostGreSQL comme dans MySQL hélas... Il n'existe même pas dans Oracle qui met en œuvre un imbitable NLS incapable de faire la confusion sur les caractères diacritiques... Il ne faut pas oublier que nos amis américains s'intéressent d'abord a eux et leur langue n'a pas d'accents !

A +

**simard** · 04/03/2014, 09h15

Bonjour,
Je n'ai pas encore bien compris quelle collation est adaptée à mon cas et comment installer une nouvelle collation dans postgresql. Je reposterai si besoin sur le forum postgresql.
Encore merci pour vos réponses.

**skuatamad** · 04/03/2014, 10h22

Envoyé par SQLpro

NLS incapable de faire la confusion sur les caractères diacritiques

Que veut dire la confusion sur les caractères diacritiques ?
Est ce é = e par exemple ?

**SQLpro** · 04/03/2014, 10h26

DIACRITIQUE : dia (deux) et critique (différence), caractères composé généralement de deux symboles.
Exemples : accents, cédille, ligature
é, â, ç È, æ, Œ...

**skuatamad** · 05/03/2014, 14h54

Merci pour l'info, wikipedia m'avait également renseigné, ma question portait plus sur le concept de confusion, est bien de ça qu'il s'agit ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
43
44
45
46
47
48
49
50
51
52
53
54
SQL> host echo %nls_lang%
FRENCH_FRANCE.WE8PC850
 
SQL> drop table t;
 
Table dropped.
 
SQL> create table t (c varchar2(20));
 
Table created.
 
SQL> insert into t values ('Frédéric');
 
1 row created.
 
SQL> insert into t values ('Frédé');
 
1 row created.
 
SQL> insert into t values ('FRED');
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from t where c = 'frederic';
 
no rows selected
 
SQL> alter session set nls_comp=linguistic;
 
Session altered.
 
SQL> alter session set nls_sort = FRENCH_AI;
 
Session altered.
 
SQL> select * from t where c = 'frederic';
 
C
--------------------
Frédéric
 
SQL> select * from t where c like 'fred%';
 
C
--------------------
Frédéric
Frédé
FRED
 
SQL>

**SQLpro** · 09/03/2014, 17h06

Envoyé par skuatamad

Merci pour l'info, wikipedia m'avait également renseigné, ma question portait plus sur le concept de confusion, est bien de ça qu'il s'agit ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
43
44
45
46
47
48
49
50
51
52
53
54
SQL> host echo %nls_lang%
FRENCH_FRANCE.WE8PC850
 
SQL> drop table t;
 
Table dropped.
 
SQL> create table t (c varchar2(20));
 
Table created.
 
SQL> insert into t values ('Frédéric');
 
1 row created.
 
SQL> insert into t values ('Frédé');
 
1 row created.
 
SQL> insert into t values ('FRED');
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from t where c = 'frederic';
 
no rows selected
 
SQL> alter session set nls_comp=linguistic;
 
Session altered.
 
SQL> alter session set nls_sort = FRENCH_AI;
 
Session altered.
 
SQL> select * from t where c = 'frederic';
 
C
--------------------
Frédéric
 
SQL> select * from t where c like 'fred%';
 
C
--------------------
Frédéric
Frédé
FRED
 
SQL>

oui, et vous constaterez qu'il n'est pas possible de faire du AI_CS dans Oracle il me semble; En sus oracle n'utilise pas les index dans ce cas et fait du SCAN (à vérifier...)

Voir aussi pour les ligatures. De tête je pense que le "eztet" allemand n'est pas supporté.

A +