Database Table Creation Constraints Syntax Guide

Création de tables

Syntaxe CREATE TABLE • CREATE TABLE nom de la table ( { colonne | contrainte_table } [ ,...] ) • où colonne est : nomtype [CONSTRAINTnom contrainte] • On peut spécifier des contraintes pour la table et pour certaines colonnes. • Une table peut avoir : • jusqu'à 1,000 colonnes, • nombre illimité de rangées

Nom des tables et des colonnes • Maximum 30 caractères. • Doivent commencer par une lettre. • Les caractères permis sont: • a-z, 0-9, $, # et _ • Un utilisateur ne peut avoir 2 tables portant le même nom. • Une table ne peut avoir 2 colonnes portant le même nom. • On ne peut utiliser les mots réservés (TABLE, SELECT, etc.).

Type de données • VARCHAR2(taille) ou VARCHAR(taille) • chaîne de caractères de longueur variable • maximum: 4000 caractères • NUMBER(p,s) • nombre • p, le nombre de chiffres total • s, le nombre de chiffres après le point. • DATE • date • CLOB • Character Large Objects • Permet de sauvegarder du texte jusqu’à 4Gig • CHAR(taille) • chaîne de caractères de longueur fixe • maximum: 255 bytes • longueur par défaut: 1 byte

Les contraintes de tables et de colonnes • Elles permettent de limiter le nombre de valeurs acceptables pour une colonne ou un groupe de colonnes. • Les règles que l'on peut imposer aux valeurs d'une colonne ou d'un groupe de colonnes: • qu'une colonne ou groupe de colonnes ne puissent pas contenir de valeurs nulles, • que la valeur d'une colonne ou d'un groupe de colonnes soit unique dans la table, • identifier une colonne ou un groupe de colonnes comme étant l'identifiant (clé primaire), • exiger que la valeur d'une colonne ou d'un groupe de colonnes existent dans une autre table (clé étrangère), • exiger que la valeur d'une colonne ou d'un groupe respecte une condition. • Avantages: • ces contraintes n'ont pas à être implantées dans chaque programme, • assure une meilleure intégrité des données.

Les contraintesSyntaxe • CREATE TABLE Table ( { Élément_colonne | Contrainte_table } [,...] ) où Élément_colonne est: Nom_Colonne Type [ Contrainte_colonne ] • Contrainte_table {UNIQUE|PRIMARY} ( Nom_Colonne [,...]) FOREIGN KEY (Nom_Colonne [,...] ) REFERENCES Table (Nom_Colonne [,...]) [ON DELETE CASCADE] CHECK (Condition) • Contrainte_colonne { NULL | NOT NULL } { UNIQUE | PRIMARY KEY } REFERENCES Table ( Nom_Colonne ) [ON DELETE CASCADE] CHECK (Condition)

Contraintes de tables et de colonnes • Contraintes de colonnes: • ces contraintes font référence à une seule colonne. • Contraintes de tables: • ces contraintes peuvent faire références à plusieurs colonnes. • Les contraintes sont spécifiées lors d’un CREATE TABLE ou d’un ALTER TABLE

Ne peut être utilisée que comme contrainte de colonnes • Exemple: CREATE TABLE departement ( id NUMBER(2) NOT NULL, nom VARCHAR2(20), Ville VARCHAR2(13) ); • Spécifie qu'une rangée doit avoir une valeur dans cette colonne

CONSTRAINT • Permet d’assigner un identifiant significatif à une contrainte. Si non spécifié, le système assignera un nom automatique (ex.: C10345678). • Exemple : CREATE TABLE departement ( id NUMBER(2) CONSTRAINT nn_dep_idNOT NULL, nom VARCHAR2(14), ville VARCHAR2(13) );

UNIQUE • Spécifie que chaque rangée de la table doit avoir une valeur différente dans cette colonne. • Peut être spécifiée comme contrainte de table ou de colonne. • Les colonnes spécifiées doivent également avoir la contrainte NOT NULL. • Cette contrainte n'est pas compatible avec la contrainte PRIMARY KEY.

Exemples pour la contrainte UNIQUE • Exemple de contrainte de ligne: • On ajoute un courriel à chaque employé : ALTER TABLE employeADD (Courriel VARCHAR2(50) UNIQUE); • Exemple de contrainte de table : • On ajoute à la table employe les colonnes code régional et numéro de téléphone avec la contrainte UNIQUE pour le numéro complet (code régional plus téléphone): ALTER TABLE employeADD ( code_regional VARCHAR2(3) NOT NULL, telephoneVARCHAR2 (7) NOT NULL, UNIQUE ( code_regional, telephone));

PRIMARY KEY • Indique que la (ou les colonnes) servent d'identifiant pour les rangées de la table. • Implique les contraintes UNIQUE et NOT NULL. • Exemple: pour créer une table departementet indiquer que le numéro de département en est la clé primaire: CREATE TABLE departement (id NUMBER(2) PRIMARY KEY, nom VARCHAR2(13), ville VARCHAR2(13) ); • Si la clé primaire est formée de plusieurs colonnes ont doit utiliser une contrainte de table. • Exemple : pour créer la table employeet indiquer que le nom et le prénom forment la clé primaire: CREATE TABLE employe (nom VARCHAR2(15), prenom VARCHAR2(15), ... , PRIMARY KEY (nom, prenom) );

FOREIGN KEY • Permet de spécifier une intégrité référentielle entre les rangées de 2 tables departement employe

FOREIGN KEY / REFERENCES • syntaxe: • Contrainte de table: FOREIGN KEY ( Nom_Colonne[, ...]) REFERENCES Table [( Nom_Colonne[, ...]) Exemple: ALTER TABLE employe ADD CONSTRAINT fk_emp_depart ( FOREIGN KEY (id_departement) REFERENCES departement (id)); • Contrainte de colonne REFERENCES Table [( Nom_Colonne[, ...]) Exemple : ALTER TABLE employe ADD (id_departement REFERENCES departement(id));

FOREIGN KEY / REFERENCES (suite) Exemple: CREATE TABLE employe ( ... id_departement NUMBER(2) REFERENCES departement (id) ; ); Ou CREATE TABLE employe ( ... id_departement NUMBER(2), … CONSTRAINT fk_emp_dept FOREIGN KEY (id_departement) REFERENCES departement(id) );

Notes surFOREIGN KEY / REFERENCES • On doit faire référence à une clé primaire ou une colonne ( ou groupe de colonnes ) UNIQUE. • La référence sera à la clé primaire si aucune colonne n'est spécifiée. • D'autres contraintes peuvent spécifier des références sur la même table ou colonnes. • Les colonnes doivent être de même type. • Peut faire référence à une colonne de la même table.

CHECK • Syntaxe: ( contrainte de table ou de colonne) CHECK ( Condition ) • Pour une contrainte de table, la condition peut impliquer n'importe quelle colonne de la table. • Exemple: CREATE TABLE departement ( id NUMBER (2) CHECK ( id BETWEEN 10 AND 99 ), nom VARCHAR2(9) CHECK ( nom = UPPER ( nom ) ), ville VARCHAR2(10) CHECK ( ville IN ( ‘MTL, ' QC‘) ); • Exemple: ALTER TABLE employe ADD ( CHECK ( salaire+ commission<= 5000 ) );

Contrainte en cascade • La clause « ON DELETE CASCADE » permet, lors de la destruction d’un enregistrement d’une table, de détruire du même coup les enregistrements d’autres tables liés par une clé lointaine. • Exemple : • Si on supprime le département, les employés du département sont automatiquement supprimés ALTER TABLE employeADD CONSTRAINT fk_emp_dept FOREIGN KEY (id_departement) REFERENCES departement(id) ON DELETE CASCADE ;

Modificationdes contraintes • On utilise l'énoncé ALTER TABLE ALTER TABLE table [ ADD ( { définition_colonne | contrainte_table } ,...) ] [ MODIFY ( { définition_colonne} ,...) ]

Les index • Pour les requêtes qui sontrégulièrementutilisées, ilestextrèmement important d’utiliser des index. • En supposantquecetterequêteestconstammentutilisé. SELECT nom, prenom FROM usager WHERE nom_usager = ‘Arthax’ AND mot_de_passe = ‘md5asdd’; • Afind’avoirunerequêteperformante, ilfaudra y ajouter un index sur les colonnesnom_usager et mot_de_passe

Les index (suite) • On ne met pas des index n’importeoù, ilfaut identifier les requêtes les plus communes • Exemple : • Comme on fait régulièrement des requêtessur la colonne “ville” de la table “departement”, on y ajoute un index CREATE INDEX idx_dept_ville ON departement (ville)

Les index (suite) • Les clésprimaires, étrangères et les contraintesuniques font automatiquement des index. Il n’estdonc pas nécessaired’enajouter. • Imaginonsunerequête qui possèdeune clause WHERE sur un champ non indexé, et surune table de 1,000,000 de lignes. • Exemple de performance de la requête: • Sans index sur le champ : 4 secondes • Avec index sur le champ : 0.01 seconde

Les tables d’association • Il arrive qu’une relation entredeux tables soit de plusieurs-à-plusieurs Exemple : Table personne et Table intérêt (ex: cinéma, lecture) Unepersonnepeutavoirplusieursintérêts Un intérêtpeutêtrepartagé par plusieurspersonnes • Il fautdonc faire une table d’associationafin de pouvoir les relier ensemble

Les tables d’association (suite) personne interet_personne interet

Database Table Creation Constraints Syntax Guide