BD : Contraintes

Définition de données: Contraintes

Équipe BD

LPSM Université Paris-Cité

2024-10-11

Plan

• Introduction
• Contraintes de type
• Contrainte de vérification CHECK
• Contrainte de non-nullité (NOT NULL)
• Contrainte d’unicité
• Clef primaire
• Clef étrangère
• Fin

Introduction

Problème

• La définition d’un schéma de base ne s’arrête pas à la définition des tables, des colonnes et des types des colonnes

• Les contraintes décrivent des propriétés que devront vérifier toutes les mises en oeuvre (instances) du schéma durant toute sa durée de vie

La définition d’un schéma de base ne s’arrête pas à la définition des tables, des colonnes et des types des colonnes

Les contraintes décrivent des propriétés que devront vérifier toutes les mises en oeuvre (instances) du schémas durant toute leur durée de vie

Pour spécifier une contrainte, il suffit de préciser que le résultat d’une certaine requête doit toujours être vide

Famille de contraintes

• Contraintes d’attribut

  • Contrainte de type
    
  • Contrainte de non-nullité

• Contraintes de tupe

  • Contrainte de vérification

• Contraintes de table

  • Contrainte de clé primaire
    
  • Contrainte d’unicité
  • Contrainte d’unicité avancée
  • Contraintes d’exclusion

• Contraintes multi-tables
  • Contraintes de clé étrangère

Contraintes de type

Contrainte de type

Une contrainte de type spécifie le type/domaine d’une colonne

Les contrainte de type sont les plus simples à mettre en oeuvre.

Au delà des types de base varchar, integer, ... PostgreSQL propose des types sophistiqués pour :

• dates,
• les estampilles avec ou sans fuseau horaire,
• des types intervalles (rangetype),
• des types énumérés,
• des types tableaux,
• etc

Consulter la liste des types

https://docs.postgresql.fr/docs/current/datatype.html

Il faut essayer d’utiliser ces types pour préciser les contenus des colonnes

Conseil

Il faut essayer d’utiliser autant que possible ces types pour préciser les contenus des colonnes

Cela permet de contraindre les contenus

Cela permet d’utiliser une foule de fonctions de manipulation très pratiques, notamment pour l’arithmétique du temps

Mise en garde

Les types disponibles diffèrent d’un SGBD à l’autre (Mysql, Postgres, Oracle, SQL Server, …)

Contrainte de vérification CHECK

Contraintes de tuple

Une contrainte de tuple peut être vérifiée tuple par tuple (ligne par ligne)

Lors d’une insertion ou d’une modification de tuple, il suffit de vérifier que la contrainte est respectée par le nouveau tuple

Exemple

Dans un tuple de Piece, on devrait toujours avoir une valeur du prix positive.

On peut imposer cela en ajoutant au schéma de la table Piece une contrainte de type CHECK

Exemple (suite)

CREATE TABLE piece(
    nomp VARCHAR(20),
    prix MONEY CHECK (prix>= 0::money), 
    couleur VARCHAR(15)
);

Note

Have a look at PostGres Monetary type

Exemple world.country

Dans un tuple de country, les colonnes numériques ne devraient pas prendre de valeur négative

CREATE TABLE world.country (
    countrycode BPCHAR(3) NOT NULL,
    name_country TEXT NOT NULL,
    continent TEXT NOT NULL,
    region TEXT NOT NULL,
    surfacearea FLOAT4 CHECK (surfacearea>= 0) NOT NULL, 
    indepyear INT2 NULL,
    population_country INT4 NOT NULL,
    lifeexpectancy FLOAT4 CHECK (lifeexpectancy>= 0) NULL, 
    gnp NUMERIC(10, 2) NULL,    
    gnpold NUMERIC(10, 2) NULL,  
    localname TEXT NOT NULL,
    governmentform TEXT NOT NULL,
    headofstate TEXT NULL,
    capital INT4 NULL,
    code2 BPCHAR(2) NOT NULL,
    CONSTRAINT country_continent_check CHECK (
        ((continent = 'Asia'::text) OR (continent = 'Europe'::text) OR 
         (continent = 'North America'::text) OR (continent = 'Africa'::text) OR 
         (continent = 'Oceania'::text) OR (continent = 'Antarctica'::text) OR 
         (continent = 'South America'::text))),
    CONSTRAINT country_pkey PRIMARY KEY (countrycode)
);

Contrainte de vérification - Exemple (suite)

CREATE TABLE piece(
    nomp VARCHAR(20),
    prix moNey CHECK (prix>= 0::money),  
    prix_promo MONEY CHECK (prix_promo >= 0::MONEY), 
    couleur VARCHAR(15),
    CHECK(prix > prix_promo) 
); 

INSERT INTO piece 
  VALUES
  ('x21', 1.51, 1.61,'rouge');

ERROR: new row for relation "piece" violates check constraint piece_check

INSERT INTO piece VALUES('x21', 1.51, null,'rouge');

est accepté

Valeurs nulles et contraintes de tuple

Les contraintes CHECK sont satisfaites si l’expression associée vaut TRUE ou NULL

Comme une expression composée vaut parfois NULL si l’un des opérandes vaut lui-même NULL, les contraintes CHECK ne suffisent pas à prévenir l’insertion de valeurs nulles

Astuce

Il faut compléter les contraintes de tuple avec des contraintes de non-nullité

Contrainte de non-nullité (NOT NULL)

Pourquoi imposer qu’une colonne soit toujours renseignée ?

Important

La présence possible des valeurs nulles rend beaucoup d’opérations complexes

Il faut avoir en tête que en SQL,

• les booléens peuvent avoir trois valeurs : TRUE, FALSE et NULL et que

• le fait qu’une expression ne vaille pas FALSE ne veut pas dire qu’elle vaut TRUE …

Pour imposer qu’une colonne soit renseignée :

• préciser après le type : NOT NULL

Contrainte de non-nullité (Exemple)

CREATE TABLE piece(
    nomp VARCHAR(20) NOT NULL,
    prix MONEY CHECK (prix>= 0::MONEY),  
    prix_promo MONEY CHECK (prix_promo >= 0::MONEY),  
    couleur VARCHAR(15),
    CHECK (prix > prix_promo)   
); 

Contrainte d’unicité

Contrainte d’unicité

• Les contraintes d’unicité garantissent l’unicité des données contenues dans une colonne ou un groupe de colonnes par rapport à toutes les lignes de la table

• C’est une contrainte de table puisqu’elle fait intervenir tous les tuples de la table

CREATE TABLE piece(
    nomp VARCHAR(20) NOT NULL UNIQUE,  
    prix money CHECK (prix>= 0::money),
    prix_promo MONEY CHECK (prix_promo >= 0::MONEY),
    couleur VARCHAR(15),
    CHECK (prix > prix_promo)
);

Contrainte d’unicité

Note

La contrainte d’unicité peut porter sur un groupe de colonnes

CREATE TABLE fournisseur(
    nomf VARCHAR(20) NOT NULL,
    villef VARCHAR(20) NOT NULL,
    UNIQUE(nomf, villef)  
);

Il ne peut pas y avoir deux lignes identiques dans la table fournisseur

Clef primaire

Clef primaire : PRIMARY KEY

Définition

Une contrainte de type clef primaire indique qu’une colonne, ou un groupe de colonnes, peuvent être utilisée comme un identifiant unique de ligne pour cette table.

Note

Ceci nécessite que les valeurs soient à la fois uniques et NON NULL.

Les définitions de table suivantes acceptent de ce fait les mêmes données

CREATE TABLE fournisseur(
    nomf VARCHAR(20) NOT NULL,
    villef VARCHAR(20) NOT NULL,
    UNIQUE(nomf, villef)  
);

CREATE TABLE fournisseur(
    nomf VARCHAR(20),
    villef VARCHAR(20),
    PRIMARY KEY (nomf, villef)  
);

Une table a au plus une clef primaire

Clef étrangère

Clef étrangère

• Une contrainte de clef étrangère stipule que les valeurs d’une colonne (ou d’un groupe de colonnes) doivent correspondre aux valeurs qui apparaissent dans les lignes d’une autre table.

• On dit que cela maintient l’intégrité référentielle entre les deux tables

• Soit les deux tables suivantes

CREATE TABLE fournisseur(
    idf INT PRIMARY KEY,
    nomf VARCHAR(20),
    villef VARCHAR(20)
);

CREATE TABLE piece(
    nomp VARCHAR(20) PRIMARY KEY,
    prix MONEY CHECK 
        (prix>= 0::MONEY),
    prix_promo MONEY CHECK 
        (prix_promo >= 0::MONEY),
    couleur VARCHAR(15),
    CHECK (prix > prix_promo)
);

Clef étrangère : REFERENCES

• Soit également une table livraison qui stocke les livraisons de ces pièces

• Il est intéressant de s’assurer que la table livraison ne contient que des références de pièces et de fournisseurs qui existent dans la base

Pour cela, deux contraintes de clef étrangère sont émises par la table des livraisons

• Une contrainte référence piece
• Une contrainte référence fournisseur

CREATE TABLE livraison(
    numliv INT PRIMARY KEY,
    idf INT REFERENCES fournisseur,   
    nomp VARCHAR(20) REFERENCES piece,  
    dateLiv DATE DEFAULT NOW(),
    quantite INT DEFAULT 1 CHECK (quantite>=0)
);
 

• Il est désormais impossible de créer des livraisons pour lesquelles les valeurs non NULL de nomp n’apparaissent pas dans la table piece et les valeurs non NULL de idf n’apparaissent pas dans la table fournisseur

• On dit que la table des livraisons est la table qui référence et la table des pièces est la table référencée

Définition de world.countrylanguage

+-------------+--------------+-------------+
| Column      | Type         | Modifiers   |
|-------------+--------------+-------------|
| countrycode | character(3) |  not null   |
| language    | text         |  not null   |
| isofficial  | boolean      |  not null   |
| percentage  | real         |  not null   |
+-------------+--------------+-------------+
Indexes:
    "countrylanguage_pkey" PRIMARY KEY, btree (countrycode, language)

Définition de world.country

+--------------------+---------------+-------------+
| Column             | Type          | Modifiers   |
|--------------------+---------------+-------------|
| countrycode        | character(3)  |  not null   |
| name_country       | text          |  not null   |
| continent          | text          |  not null   |
| region             | text          |  not null   |
| surfacearea        | real          |  not null   |
| indepyear          | smallint      |             |
| population_country | integer       |  not null   |
| lifeexpectancy     | real          |             |
| gnp                | numeric(10,2) |             |
| gnpold             | numeric(10,2) |             |
| localname          | text          |  not null   |
| governmentform     | text          |  not null   |
| headofstate        | text          |             |
| capital            | integer       |             |
| code2              | character(2)  |  not null   |
+--------------------+---------------+-------------+

Indexes:
    "country_pkey" PRIMARY KEY, btree (countrycode)
Check constraints:
    "country_continent_check" CHECK
    (continent = 'Asia'::text OR 
      continent = 'Europe'::text OR 
      continent = 'North America'::text OR 
      continent = 'Africa'::text OR 
      continent = 'Oceania'::text OR 
      continent = 'Antarctica'::text OR 
      continent = 'South America'::text)
Foreign-key constraints:
    "country_capital_fkey" 
        FOREIGN KEY (capital) 
        REFERENCES world.city(id)  
Referenced by:
    TABLE "world.countrylanguage"
    CONSTRAINT "countrylanguage_countrycode_fkey" 
        FOREIGN KEY (countrycode)
        REFERENCES country(countrycode)

Foreign keys emitted by world.country

ALTER TABLE world.country ADD 
    CONSTRAINT country_capital_fkey 
        FOREIGN KEY (capital)   
        REFERENCES world.city(id);  

ALTER TABLE world.country 
    ADD CONSTRAINT country_fk 
        FOREIGN KEY (continent)   
        REFERENCES world.code_continent(continent);  

Fin

Contraintes