PostgreSQL - CONTRAINTES
Les contraintes sont les règles appliquées aux colonnes de données sur la table. Celles-ci sont utilisées pour empêcher la saisie de données invalides dans la base de données. Cela garantit l'exactitude et la fiabilité des données de la base de données.
Les contraintes peuvent être au niveau de la colonne ou du niveau de la table. Les contraintes au niveau de la colonne ne sont appliquées qu'à une seule colonne tandis que les contraintes au niveau de la table sont appliquées à l'ensemble du tableau. La définition d'un type de données pour une colonne est une contrainte en soi. Par exemple, une colonne de type DATE contraint la colonne à des dates valides.
Voici les contraintes couramment utilisées disponibles dans PostgreSQL.
NOT NULL Constraint - Garantit qu'une colonne ne peut pas avoir la valeur NULL.
UNIQUE Constraint - Garantit que toutes les valeurs d'une colonne sont différentes.
PRIMARY Key - Identifie de manière unique chaque ligne / enregistrement dans une table de base de données.
FOREIGN Key - Contraint les données en fonction des colonnes d'autres tables.
CHECK Constraint - La contrainte CHECK garantit que toutes les valeurs d'une colonne satisfont à certaines conditions.
EXCLUSION Constraint - La contrainte EXCLUDE garantit que si deux lignes sont comparées sur les colonnes ou expressions spécifiées à l'aide du ou des opérateurs spécifiés, toutes ces comparaisons ne renverront pas TRUE.
Contrainte NOT NULL
Par défaut, une colonne peut contenir des valeurs NULL. Si vous ne voulez pas qu'une colonne ait une valeur NULL, vous devez définir une telle contrainte sur cette colonne en spécifiant que NULL n'est plus autorisé pour cette colonne. Une contrainte NOT NULL est toujours écrite comme une contrainte de colonne.
Un NULL n'est pas la même chose qu'aucune donnée; il représente plutôt des données inconnues.
Exemple
Par exemple, l'instruction PostgreSQL suivante crée une nouvelle table appelée COMPANY1 et ajoute cinq colonnes, dont trois, ID et NAME et AGE, spécifient de ne pas accepter les valeurs NULL -
CREATE TABLE COMPANY1(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);Contrainte unique
La contrainte UNIQUE empêche deux enregistrements d'avoir des valeurs identiques dans une colonne particulière. Dans le tableau COMPANY, par exemple, vous souhaiterez peut-être empêcher deux personnes ou plus d'avoir le même âge.
Exemple
Par exemple, l'instruction PostgreSQL suivante crée une nouvelle table appelée COMPANY3 et ajoute cinq colonnes. Ici, la colonne AGE est définie sur UNIQUE, de sorte que vous ne pouvez pas avoir deux enregistrements avec le même âge -
CREATE TABLE COMPANY3(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL UNIQUE,
ADDRESS CHAR(50),
SALARY REAL DEFAULT 50000.00
);Contrainte PRIMARY KEY
La contrainte PRIMARY KEY identifie de manière unique chaque enregistrement dans une table de base de données. Il peut y avoir plus de colonnes UNIQUE, mais une seule clé primaire dans une table. Les clés primaires sont importantes lors de la conception des tables de la base de données. Les clés primaires sont des identifiants uniques.
Nous les utilisons pour faire référence aux lignes du tableau. Les clés primaires deviennent des clés étrangères dans d'autres tables, lors de la création de relations entre tables. En raison d'une «surveillance du codage de longue date», les clés primaires peuvent être NULL dans SQLite. Ce n'est pas le cas avec d'autres bases de données
Une clé primaire est un champ dans une table, qui identifie de manière unique chaque ligne / enregistrement dans une table de base de données. Les clés primaires doivent contenir des valeurs uniques. Une colonne de clé primaire ne peut pas avoir de valeurs NULL.
Une table ne peut avoir qu'une seule clé primaire, qui peut être constituée d'un ou de plusieurs champs. Lorsque plusieurs champs sont utilisés comme clé primaire, ils sont appeléscomposite key.
Si une table a une clé primaire définie sur un ou plusieurs champs, vous ne pouvez pas avoir deux enregistrements ayant la même valeur que ce ou ces champs.
Exemple
Vous avez déjà vu divers exemples ci-dessus où nous avons créé une table COMAPNY4 avec ID comme clé primaire -
CREATE TABLE COMPANY4(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);Contrainte FOREIGN KEY
Une contrainte de clé étrangère spécifie que les valeurs d'une colonne (ou d'un groupe de colonnes) doivent correspondre aux valeurs apparaissant dans une ligne d'une autre table. Nous disons que cela maintient l'intégrité référentielle entre deux tables liées. Elles sont appelées clés étrangères car les contraintes sont étrangères; c'est-à-dire en dehors de la table. Les clés étrangères sont parfois appelées clé de référence.
Exemple
Par exemple, l'instruction PostgreSQL suivante crée une nouvelle table appelée COMPANY5 et ajoute cinq colonnes.
CREATE TABLE COMPANY6(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);Par exemple, l'instruction PostgreSQL suivante crée une nouvelle table appelée DEPARTMENT1, qui ajoute trois colonnes. La colonne EMP_ID est la clé étrangère et fait référence au champ ID de la table COMPANY6.
CREATE TABLE DEPARTMENT1(
ID INT PRIMARY KEY NOT NULL,
DEPT CHAR(50) NOT NULL,
EMP_ID INT references COMPANY6(ID)
);CHECK Contrainte
La contrainte CHECK permet à une condition de vérifier la valeur saisie dans un enregistrement. Si la condition est évaluée à false, l'enregistrement enfreint la contrainte et n'est pas entré dans la table.
Exemple
Par exemple, l'instruction PostgreSQL suivante crée une nouvelle table appelée COMPANY5 et ajoute cinq colonnes. Ici, nous ajoutons une colonne CHECK with SALARY, de sorte que vous ne pouvez pas avoir de SALARY comme zéro.
CREATE TABLE COMPANY5(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL CHECK(SALARY > 0)
);Contrainte EXCLUSION
Les contraintes d'exclusion garantissent que si deux lignes sont comparées sur les colonnes ou expressions spécifiées à l'aide des opérateurs spécifiés, au moins une de ces comparaisons d'opérateurs renverra false ou null.
Exemple
Par exemple, l'instruction PostgreSQL suivante crée une nouvelle table appelée COMPANY7 et ajoute cinq colonnes. Ici, nous ajoutons une contrainte EXCLUDE -
CREATE TABLE COMPANY7(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT,
AGE INT ,
ADDRESS CHAR(50),
SALARY REAL,
EXCLUDE USING gist
(NAME WITH =,
AGE WITH <>)
);Ici, USING gist est le type d'index à construire et à utiliser pour l'application.
Vous devez exécuter la commande CREATE EXTENSION btree_gist , une fois par base de données. Cela installera l'extension btree_gist, qui définit les contraintes d'exclusion sur les types de données scalaires simples.
Comme nous l'avons imposé, l'âge doit être le même, voyons cela en insérant des enregistrements dans la table -
INSERT INTO COMPANY7 VALUES(1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY7 VALUES(2, 'Paul', 32, 'Texas', 20000.00 );
INSERT INTO COMPANY7 VALUES(3, 'Paul', 42, 'California', 20000.00 );Pour les deux premières instructions INSERT, les enregistrements sont ajoutés à la table COMPANY7. Pour la troisième instruction INSERT, l'erreur suivante s'affiche -
ERROR: conflicting key value violates exclusion constraint "company7_name_age_excl"
DETAIL: Key (name, age)=(Paul, 42) conflicts with existing key (name, age)=(Paul, 32).Suppression des contraintes
Pour supprimer une contrainte, vous devez connaître son nom. Si le nom est connu, il est facile de le supprimer. Sinon, vous devez trouver le nom généré par le système. Le nom de table \ d de la commande psql peut être utile ici. La syntaxe générale est -
ALTER TABLE table_name DROP CONSTRAINT some_name;