clé unique et index

Bonjour

Il y a la contrainte unique et l'index unique.
Les 2 utilisent un index pour s'assurer que les valeurs sont uniques.

La premier (la contrainte) permet en plus d'être référencée par une clef
étrangère (un peu à la manière de la clef primaire qui elle en plus interdit
les NULL).

Bref la différence n'est que très peu significative.
Gardez en tête que quoi qu'il arrive dans les 2 cas un index sera créée sur
le champ, n'en abusez donc pas dans la table si cette dernière subit beaucoup
d'écritures.

Cordialement

Christian Robert
MVP SQL Server
http://blogs.codes-sources.com/christian

bonjour

quand je veux eviter les doublons sur une colone, j'ai deux
possibilités
pour l'index :
- Type = clé unique -> (Est Unique = oui)
- Type = index , Est Unique = oui
quelle différence entre les deux ?

merci par avance pour vos informations

Frédéric

beaucoup d'approximation dans votre prose :

en matière de SGBD relationnel la notion d'index n'existe pas...

En effet on parle de contrainte de _clef_ _primaire_ c'est à dire un
ensemble de colonne non nullable et ayant des valeurs uniques, d'une
part, et d'autre part on parle de contraintes d'_unicité_ c'est à dire
d'un ensemble de colonnes devant contenir des valeurs unique, par
forcément connues (donc nullable), sachant que NULL étant un marqueur
d'absence de valeur, par essence NULL est toujours différent de NULL
(autrement dit il y a respect de l'unicité de la contrainte même si la
colonne UNIQUE contient 200 marqueurs NULL, mais que toutes les valeurs
exprimées sont différentes). Hélas SQL Server ne respecte pas la norme
sur ce point, ce qui pose de multiples problèmes !

L'une et l'autre contrainte (clef primaire ou unicité) peut être
référencée par une table fille à titre de clef étrangère.

Exemples :

CREATE TABLE T_EMPLOYE_EMP
(EMP_ID INTEGER NOT NULL PRIMARY KEY,
EMP_MATRICULE CHAR(6) UNIQUE,
EMP_NNI_SEX CHAR(1),
EMP_NNI_AN CHAR(2),
EMP_NNI_MOIS CHAR(2),
EMP_NNI_DPT CHAR(2),
EMP_NNI_CMN CHAR(3),
EMP_NNI_RANG CHAR(3),
EMP_NOM CHAR(32),
EMP_PRENOM VARCHAR(25),
CONSTRAINT UK_NNI UNIQUE (EMP_NNI_SEX, EMP_NNI_AN, EMP_NNI_MOIS,
EMP_NNI_DPT, EMP_NNI_CMN,EMP_NNI_RANG))

CREATE TABLE T_ARRET_MALADIE_ARM
(ARM_ID INT NOT NULL PRIMARY KEY,
EMP_NNI_SEX CHAR(1) NOT NULL,
EMP_NNI_AN CHAR(2) NOT NULL,
EMP_NNI_MOIS CHAR(2) NOT NULL,
EMP_NNI_DPT CHAR(2) NOT NULL,
EMP_NNI_CMN CHAR(3) NOT NULL,
EMP_NNI_RANG CHAR(3) NOT NULL,
EMP_DATE_ARRET DATETIME NOT NULL,
EMP_DATE_REPRISE DATETIME,
CONSTRAINT FK_NNI FOREIGN KEY (EMP_NNI_SEX, EMP_NNI_AN, EMP_NNI_MOIS,
EMP_NNI_DPT, EMP_NNI_CMN,EMP_NNI_RANG)
REFERENCES T_EMPLOYE_EMP (EMP_NNI_SEX, EMP_NNI_AN, EMP_NNI_MOIS,
EMP_NNI_DPT, EMP_NNI_CMN,EMP_NNI_RANG))


Maintenant les index sont une problématique physique des systèmes de
bases de données. Ce sont des données redondantes, spécialement
organisées dans des structures afin d'obtenir un accès rapide à toute
valeur cherchée.
Une base de données n'a théoriquement aucun besoin d'index et le langage
SQL (la norme) ne connait pas la notion d'index.
Reste que pour assurer un traitement rapide de recherche de valeut
unique, tous les SGBDR créées des index sous jacents aux contraintes
PRIMARY KEY et UNIQUE.

Bref, un petit conseil : commencer par apprendre le langage SQL, car
votre confusion vient à l'évidence de votre méconnaissance de la chose.

Mon livre, comme mon site peuvent vous y aider !

un petit extrait de mon bouquin :
"
3.9 Les index et SQL : définition et création

A proprement parler les index ne font pas partie du langage SQL.
Rappelons que SQL en tant que langage ne s'occupe nullement des aspects
physiques ou cosmétiques de la problématique des SGBDR. Or les index
sont des organisations physiques de données en vue d'accélérer certains
traitements comme les recherches ou les comparaisons. Tout comme les
espaces de stockage des données (fichiers) SQL passe volontairement sous
silence ce concept.
Mais leur importance est telle dans les SGBDR qu'il ne serait pas
correct de les passer sous silence sous le prétexte qu'ils ne font pas
partie intégrante de SQL !

3.9.1 Définition de l'index

Un index est une construction de données, dont la structure est
optimisée afin que les opérations de recherche soient les plus efficaces
possible.
Nous utilisons tous les jours des index dans la vie courante : notre
répertoire téléphonique, la liste des mots clefs par ordre alphabétique
en fin d'un ouvrage technique, les numéros des immeubles dans une rue,
sont des index. Il y a fort à parier que vous allez mettre plus de temps
à retrouver le numéro de téléphone de votre amie Zoé, si vous avez écrit
chaque numéro de téléphone sur un petit bout de papier et rangé le tout
dans une boîte en carton, que si vous faites usage d'un annuaire
téléphonique personnel, qui, nous l'espérons, est organisé par ordre
alphabétique des noms !

A proprement parler SQL n'a pas besoin d'index pour fonctionner.
Rechercher une valeur dans une colonne ou vérifier l'unicité d'une
occurrence n'est qu'un problème technique qui peut être réglé par une
lecture séquentielle de toutes les informations. Seules des
considérations de performance sur des volumes importants de données
peuvent entrer en jeu.

De manière générale, un index peut être compris comme une collection
d'information organisée physiquement de telle manière que la recherche
d'une entrée de données soit la plus rapide possible. Comme dans une
base de données, les données sont organisées par lignes, il convient que
l'index inclut à la fois la valeur de l'occurrence et la référence à la
ligne de la table à laquelle elle dépend.
Il existe plusieurs structures aujourd'hui bien connues pour organiser
les index, mais chaque SGBDR possède souvent quelques petits secrets de
fabrication !

3.9.2 Organisation des index

Les structures d'index les plus courantes sont les suivantes :
séquentiel, arbre, cluster, hachage, bitmap.

L'index séquentiel (généralement appelé ISAM pour Indexed Sequentiel
Access Method, mis au point dans les années 60 par IBM) consiste ni plus
ni moins à organiser les données à la queue leu leu dans un fichier dans
l'ordre de tri voulu (alphabétique, numérique, ascendant,
descendant...). Lorsque l'on veut rajouter une valeur intermédiaire,
comme il n'est pas possible de le faire directement à l'intérieur sans
un coût exorbitant, on prévoit une zone en fin de fichier dite de
débordement. Lorsque l'on recherche une information, l'algorithme de
dichotomie permet d'aller chercher la valeur au bon endroit avec un coût
logarithmique. Si l'information n'est pas trouvée par dichotomie on la
recherche par lecture séquentielle de la zone de débordement.
Pour obtenir de bonnes performances avec un tel index, il est nécessaire
de mettre à jour régulièrement l'index en faisant disparaître la zone de
débordement dès qu'elle commence à prendre de l'importance, par un tri
physique des données associée à une reconstruction du fichier.
L'administration courante d'un tel type d'index impose donc de veiller à
ce que la zone de débordement reste minime.
Il est devenu rare en matière de bases de données relationnelles, car
d'autres techniques sont plus performantes.

L'index organisé en arbre consiste à chaîner les données sous la forme
d'une arborescence qui peut être à branche binaire ou multivaluée. La
plupart du temps les SGBDR utilisent la technique de l'arbre équilibré
(B-Trees en anglais, pour Balanced Trees) qui est un bon compromis entre
les problématiques de recherches et celles découlant de la mise à jour.
Dans un arbre équilibré, chaque noeud est une page de données dont la
taille est optimisée pour le stockage et les flux de données. Par
exemple sous plateforme WinTel, les meilleures performances en
entrées/sorties de disque sont faites par paquet de 8 kilo octets, d'où
il ressort que l'espace de stockage de l'arbre (et plus généralement la
granule de stockage de la base entière) est une page de données de 8192
octets. Bien évidemment dans cette page on peut mettre un nombre divers
d'occurrences de données dépendant de la taille de la donnée et de la
densité que l'on souhaite (occurrence de la donnée et référence à la
ligne). Dans chaque page, les données sont ordonnées, et chaque page est
chaînée avec d'autres pages dans l'arbre en tentant à chaque branche de
respecter l'ordre établi et un équilibre suffisant pour présenter les
données sous la forme d'une pyramide. Toute l'astuce des arbres
équilibrés consiste à trouver le bon compromis entre vitesse et
équilibrage et les études algorithmiques sur le sujet sont devenues de
grands classiques. L'avantage de l'arbre équilibré est que le temps de
recherche est constant quelque soit l'occurrence recherchée, puisque la
profondeur de recherche est la même pour toutes les données.

L'index en cluster est un raffinement de l'arbre équilibré qui consiste
à placer dans l'arbre la totalité du contenu de chaque ligne de la table
dans l'ordre des colonnes composant l'index. Ce raffinement semble
mineur, mais il est essentiel pour deux raisons : les lignes de la table
sont physiquement pré triées dans l'ordre de l'index et il n'y a pas de
doublons. Ces deux considérations font que le choix d'un index en
cluster est optimal pour une clef primaire (ou une contrainte d'unicité
composée de colonnes NOT NULL), et au sommet de toute performance
lorsque cette clef est un auto incrément continu (séquence dans le temps
: 1, 2, 3 ...) En effet, dans ce cas, les insertions de données dans
l'index en cluster se font en fin de structure ce qui minimise le
traitement. Seul inconvénient, il ne peut y avoir qu'un seul index
organisé en cluster par table puisque c'est l'ordre physique des données
qui est pris en compte.

L'index en hachage consiste à trouver une fonction capable de "prédire"
un emplacement le plus probable possible de la donnée que l'on souhaite
indexer. Si la donnée ne se trouve pas à l'emplacement prévu (ou s'il y
a des doublons), on utilise alors une fonction de hachage secondaire qui
recherche dans une zone de débordement prévue pour le cas des
téléscopages ou des manques de premier niveau. Si ce n'est toujours pas
le cas, on réitère avec une troisième fonction de hachage et ainsi de suite.
L'index en hachage peut être intéressant dans deux cas : lorsque
certaines mêmes données sont très fréquemment sollicitées en recherche
et d'autres très rarement, et lorsque la dispersion des données est très
forte et sans doublons.

L'index bitmap consiste à recenser les occurrences des différentes
valeurs d'une colonne et d'y associer une matrice binaire. Il s'agit en
quelque sorte d'une "table de vérité" intégrée à chaque ligne de chaque
colonne de la table, l'occurrence qui représente la donnée. Un index
bitmap est intéressant lorsque le nombre de valeurs que peut prendre
l'occurrence est faible ou fermé et le nombre de lignes est fort. Par
exemple pour spécifier le sexe d'une liste d'animaux (mâle, femelle,
asexué, hermaphrodite, NULL...).

L'index textuel consiste à découper l'information en mots en
sélectionnant les mots pertinents et en éliminants ceux qui n'apporte
pas de sens sémantique au contenu des données. Ainsi dans un index
textuel des mots comme "le", "la", "les", "un", "une", "des" ne sont pas
pris en compte. Associée à des transformations et des valuations de mots
ainsi qu'à la cartographies des mots dans les occurrences de données,
cette technique permet de retrouver de manière très efficace des phrases
ou des expressions mêmes mal orthographiées. Les mécanismes d'indexation
textuels sont de plus en plus intégrés aux SGBDR et sont le fondement de
moteurs de recherches sur le web comme Google.
Il est à noter que SQL:1999, dans son unité "MM" (SQL Multimedia and
Application Packages) a introduit la notion de "full-text" qui permet
d'indexer textuellement des littéraux et d'effectuer des recherches par
mots, phrases, paragraphes... ainsi que par contexte ou prononciation
phonétique. Le type SQL de données FULLTEXT est le réceptacle de
l'information textuelle et le prédicat CONTAINS permet de chercher, dans
les colonnes ainsi indexées, des motifs littéraux complexes.
Exemple :

SELECT *
FROM T_UTILISATEUR_USR
WHERE USR_OBSERVATIONS.CONTAINS
('STEMMED FORM OF "utilisateur"
IN SAME SENTENCE AS
SOUNDS LIKE "sot"') = 1

Qui permet de rechercher, dans une colonne de nom USR_OBSERVATION qui
doit avoir été créé avec le type SQL:1999 FULLTEXT, le mot cité
(utilisateur) ou l'une, quelconque de ses formes fléchies
(utilisateurs, utilisatrice, utilisatrices...) avec un homonyme de sot
(sot, seau, seaux, sots, sceau, sceaux, saut, sauts, ... ) à condition
que ces deux lexèmes figurent dans la même phrase.

Pour certaines catégories de données, il existe des index de
classification particulier.
Par exemple la classification Dewey est une méthode d'organisation des
connaissances universellement répandue. La méthode Dewey consiste à
répartir le savoir en dix grandes classes, subdivisées en cent
sous-classes, puis en une multitude de sections et sous-sections. Ainsi,
la côte Dewey 599.2 représente-t-elle les Primates :
500 : sciences de la nature
590 : zoologie
599 : mammifères
Dans le domaine des noms de personnes, une méthode d'organisation
indexée consiste à utiliser une table de Cutter-Sanborn qui permet de
définir une correspondance approximative entre les éléments les plus
pertinents d'un nom (lettre ou groupe de lettres) et un code numérique.
Pour les noms on peut aussi utiliser une correspondance phonétique à
l'aide d'algorithmes aujourd'hui bien connus comme le Soundex ou encore
Soundex2, Metaphone, Phonex... La plupart des éditeurs de SGBDR
intègrent en général une fonction basique comme Soundex, permettant la
mise en oeuvre de ce genre d'index.

3.9.3 Correspondances entre SQL et les index

En principe il n'y a pas de relation entre les éléments de SQL et les
index. La pratique est différente : le moyen le plus sûr et le plus
efficace par lequel le SGBDR assure le fonctionnement d'une contrainte
telle qu'une clef repose sur la mise en oeuvre d'un index. En fait,
sans vous le dire, votre SGBDR décide la plupart du temps de créer un
index à chaque fois que vous créez une clef primaire, et parfois pour
les contraintes d'unicité ou les colonnes référençant une clef étrangère.
Pour la clef de table il s'agit souvent d'un index B-tree en cluster.
Pour une contrainte d'unicité ou une clef étrangère il s'agit la plupart
du temps d'un B-tree.

[...]

"

Ambassadeur kosh a écrit :

bonjour

quand je veux eviter les doublons sur une colone, j'ai deux possibilités
pour l'index :
- Type = clé unique -> (Est Unique = oui)
- Type = index , Est Unique = oui

évitez l'interface graphique de SQL Server. Ele ne sert qu'à introduire
de la confusion et sa traduction est approximative ! De plus aucun
système graphique ne peut montrer toutes les possibilité du langage SQL...

quelle différence entre les deux ?

merci par avance pour vos informations

Frédéric

"

A +


--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*********************** http://www.sqlspot.com *************************

> beaucoup d'approximation dans votre prose :

possible, mais je vous lis texto ce que je vois dans la property grid. si si
si !

Maintenant les index sont une problématique physique des systèmes de bases
de données.

oui, et cette problematique physique est ce qui m'interesse puisqu'elle
induit directement le temps de réponse.
ce que je reconnais, la faute m'echoie, je ne mentionna point dans ma
question.
trop pressé d'avoir une réponse :)

Ce sont des données redondantes, spécialement organisées dans des
structures afin d'obtenir un accès rapide à toute valeur cherchée.

bon, je débute, mais pas tant que ça :)

forcément, il y'a le langage de selection, et ensuite, il y'a une
organisation physique qui ne fait pas partie de sql volontairement pour
laisse au langage son pouvoir de description et la capacité à résoudre de
façon optimale les demandes. dans n'importe quel langage, on retombe la
dessus... definition, implantation, résolution, sémantique operationelle,
dénotationnelle...

ceci dit, pour l'article, il y'a des points synthetiques tres interessants
sur les indexs. je prend, et vous remercie, une fois n'est pas coutume, pour
votre gentillesse et pour tout le temps que vous consacrez à toutes nos
questions

Cordialement

Frédéric

> Bonjour

Il y a la contrainte unique et l'index unique.
Les 2 utilisent un index pour s'assurer que les valeurs sont uniques.

La premier (la contrainte) permet en plus d'être référencée par une clef
étrangère (un peu à la manière de la clef primaire qui elle en plus
interdit les NULL).

Bref la différence n'est que très peu significative.
Gardez en tête que quoi qu'il arrive dans les 2 cas un index sera créée
sur le champ, n'en abusez donc pas dans la table si cette dernière subit
beaucoup d'écritures.

ok.
j'ai pu constater suite à votre remarque qu'effectivement, c'est "cluster"
qui tranche radicalement la chose, et non ça.

merci beaucoup pour vos informations

Cordialement

Frédéric