Contraintes uniques de Postgres et index : quand devriez-vous utiliser lesquels ?

Contraintes et index uniques Postgres

Présentation

Les contraintes et index uniques sont tous deux utilisés pour garantir l'unicité des données dans les tables de la base de données. Il existe cependant quelques différences essentielles entre les deux approches.

Contraintes et index uniques dans Postgres

Contrainte unique

• Explicitement défini dans la définition de la table à l'aide du mot-clé CONSTRAINT.
• Appliquer l'unicité en empêchant l'insertion de valeurs en double dans les colonnes spécifiées dans la contrainte.
• peut être utilisé pour créer des clés primaires.
• S'applique toujours à toutes les lignes du tableau.

Index

• Créé à l'aide de l'instruction CREATE INDEX.
• Accélérez les requêtes en fournissant un moyen plus rapide de localiser les données dans une table.
• Peut être unique ou non unique.
• Peut être appliqué à toutes les lignes ou uniquement à un sous-ensemble de lignes (index partiel).

Méthode recommandée

Selon la documentation Postgres, l'utilisation de ALTER TABLE ... ADD CONSTRAINT est le moyen privilégié pour ajouter des contraintes uniques à une table. En effet, la création d'index uniques spécifiquement pour renforcer l'unicité est considérée comme un détail d'implémentation et ne doit pas être accessible directement.

Résultats pratiques

Performances : Généralement, les contraintes uniques et les index uniques fonctionnent de la même manière en termes de vitesse de requête. Toutefois, les index partiels peuvent améliorer les performances des requêtes qui accèdent uniquement à un sous-ensemble de données.

Clés étrangères : Les contraintes uniques peuvent être référencées par des clés étrangères, mais pas les index uniques.

Surcharge de contraintes : Les contraintes uniques ont une légère surcharge par rapport aux index uniques car elles nécessitent des opérations de maintenance supplémentaires lors de l'insertion ou de la mise à jour des données.

Exemple

Pour illustrer la différence entre une contrainte unique et un index, créons une table avec à la fois une contrainte unique et un index unique :

<code class="language-sql">CREATE TABLE foo (
    id SERIAL PRIMARY KEY,
    code INTEGER,
    label TEXT,
    CONSTRAINT foo_uq UNIQUE (code, label)
);
CREATE UNIQUE INDEX foo_idx ON foo (code, label);</code>

La contrainte unique (foo_uq) et l'index unique (foo_idx) renforceront l'unicité de la combinaison (code, étiquette). Cependant, vous devriez préférer utiliser des contraintes uniques car c'est l'approche recommandée dans Postgres.

Index partiel

Pour créer un index partiel, utilisez la clause CREATE INDEX dans l'instruction WHERE :

<code class="language-sql">CREATE UNIQUE INDEX foo_partial_idx ON foo (code) WHERE label IS NOT NULL;</code>

Cet index s'applique uniquement aux lignes où label n'est pas NULL .

Ajouter des contraintes à l'aide d'index

Vous ne pouvez pas ajouter de contraintes uniques à l'aide d'index partiels. Cependant, vous pouvez créer une contrainte unique en utilisant un index unique existant :

<code class="language-sql">ALTER TABLE foo ADD CONSTRAINT foo_partial_uq UNIQUE USING INDEX foo_partial_idx;</code>

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!