Clé primaire ou index unique ?

Question

Au travail, nous avons une grande base de données avec des index uniques au lieu de clés primaires et tout fonctionne bien.

Je suis en train de concevoir une nouvelle base de données pour un nouveau projet et j'ai un dilemme :

Dans la théorie de la BD, la clé primaire est l'élément fondamental, c'est OK, mais dans les projets réels, quels sont les avantages et les inconvénients des deux ?

Qu'utilisez-vous dans vos projets ?

EDIT : ...et qu'en est-il des clés primaires et de la réplication sur MS SQL server ?

Answer 1

Qu'est-ce qu'un indice unique ?

Un index unique sur une colonne est un index sur cette colonne qui applique également la contrainte selon laquelle vous ne pouvez pas avoir deux valeurs égales dans cette colonne sur deux lignes différentes. Exemple :

CREATE TABLE table1 (foo int, bar int);
CREATE UNIQUE INDEX ux\_table1\_foo ON table1(foo);  -- Create unique index on foo.

INSERT INTO table1 (foo, bar) VALUES (1, 2); -- OK
INSERT INTO table1 (foo, bar) VALUES (2, 2); -- OK
INSERT INTO table1 (foo, bar) VALUES (3, 1); -- OK
INSERT INTO table1 (foo, bar) VALUES (1, 4); -- Fails!

Duplicate entry '1' for key 'ux\_table1\_foo'

La dernière insertion échoue car elle viole l'index unique sur la colonne foo lorsqu'il tente d'insérer la valeur 1 dans cette colonne pour la deuxième fois.

Dans MySQL, une contrainte unique autorise plusieurs NULL.

Il est possible de créer un index unique sur plusieurs colonnes.

Clé primaire et index unique

Les choses qui sont les mêmes :

• Une clé primaire implique un index unique.

Les choses qui sont différentes :

• Une clé primaire implique également NOT NULL, mais un index unique peut être nullable.
• Il ne peut y avoir qu'une seule clé primaire, mais il peut y avoir plusieurs index uniques.
• Si aucun index groupé n'est défini, la clé primaire sera l'index groupé.

Answer 2

Vous pouvez le voir comme ceci :

Une clé primaire est unique

Une valeur unique ne doit pas nécessairement être la représentation de l'élément.

Une clé primaire est utilisée pour identifier l'élément, si vous avez une "personne", vous voudriez avoir un numéro d'identification personnel (SSN ou autre) qui est primaire pour votre personne.

D'autre part, la personne peut avoir une adresse électronique qui est unique, mais qui ne l'identifie pas.

J'ai toujours des clés primaires, même dans les tables de relation (table intermédiaire / table de connexion). Pourquoi ? Eh bien, j'aime suivre une norme lors du codage, si la "personne" a un identifiant, la voiture a un identifiant, eh bien, alors la personne -> voiture devrait avoir un identifiant aussi !

Answer 3

Les clés étrangères fonctionnent avec des contraintes uniques, tout comme les clés primaires. De Books Online :

Une contrainte FOREIGN KEY n'a pas besoin de être liée uniquement à une contrainte PRIMARY KEY PRIMARY KEY d'une autre table ; elle peut également être définie pour faire référence aux colonnes d'une contrainte UNIQUE dans une autre table

Pour la réplication transactionnelle, vous avez besoin de la clé primaire. De Books Online :

Les tables publiées pour la réplication transactionnelle doivent avoir une clé primaire. Si une table se trouve dans une publication publication de réplication transactionnelle, vous ne pouvez pas désactiver les index qui sont associés aux colonnes de clé primaire. Ces index sont requis par la réplication. Pour désactiver un index, vous devez d'abord supprimer la table de la publication.

Les deux réponses sont pour SQL Server 2005.

Answer 4

Le choix de l'utilisation d'une clé primaire de substitution par rapport à une clé naturelle est délicat. Les réponses telles que "toujours ou jamais" sont rarement utiles. Je trouve que cela dépend de la situation.

A titre d'exemple, j'ai les tableaux suivants :

CREATE TABLE toll_booths (
    id            INTEGER       NOT NULL PRIMARY KEY,
    name          VARCHAR(255)  NOT NULL,
    ...
    UNIQUE(name)
)

CREATE TABLE cars (
    vin           VARCHAR(17)   NOT NULL PRIMARY KEY,
    license_plate VARCHAR(10)   NOT NULL,
    ...
    UNIQUE(license_plate)
)

CREATE TABLE drive_through (
    id            INTEGER       NOT NULL PRIMARY KEY,
    toll_booth_id INTEGER       NOT NULL REFERENCES toll_booths(id),
    vin           VARCHAR(17)   NOT NULL REFERENCES cars(vin),
    at            TIMESTAMP     DEFAULT CURRENT_TIMESTAMP NOT NULL,
    amount        NUMERIC(10,4) NOT NULL,
    ...
    UNIQUE(toll_booth_id, vin)
)

Nous avons deux tables d'entités ( toll_booths et cars ) et un tableau de transactions ( drive_through ). Le site toll_booth utilise une clé de substitution car elle n'a pas d'attribut naturel dont l'évolution n'est pas garantie (le nom peut facilement être modifié). Le site cars utilise une clé primaire naturelle car elle possède un identifiant unique non changeant ( vin ). Le site drive_through utilise une clé de substitution pour faciliter l'identification, mais possède également une contrainte unique sur les attributs dont l'unicité est garantie au moment de l'insertion de l'enregistrement.

http://database-programmer.blogspot.com a d'excellents articles sur ce sujet particulier.

Answer 5

Les clés primaires ne présentent pas d'inconvénients.

Pour ajouter quelques informations aux réponses de @MrWiggles et @Peter Parker, lorsque la table n'a pas de clé primaire par exemple, vous ne pourrez pas modifier les données dans certaines applications (elles finiront par dire qqch comme cannot edit / delete data without primary key). Postgresql permet à plusieurs valeurs NULL d'être dans une colonne UNIQUE, PRIMARY KEY ne permet pas les NULLs. De plus, certains ORM qui génèrent du code peuvent avoir des problèmes avec les tables sans clé primaire.

UPDATE :

Pour autant que je sache, il n'est pas possible de répliquer des tables sans clé primaire dans MSSQL, du moins sans problème ( détails ).