Création de tables
VII-2
CREATE TABLE nom de la table ( { colonne | contrainte_table }
[ ,...])
où colonne est : nom type [CONSTRAINT nom 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
Syntaxe CREATE TABLE
VII-3
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.).
Nom des tables et des colonnes
VII-4
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
Type de données
VII-5
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 contraintes de tables et de colonnes
VII-6
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)
Les contraintesSyntaxe
VII-7
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
Contraintes de tables et de colonnes
VII-8
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
VII-9
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_id NOT NULL,
nom VARCHAR2(14), ville VARCHAR2(13) );
CONSTRAINT
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.
10
UNIQUE
Exemple de contrainte de ligne:◦ On ajoute un courriel à chaque employé :
ALTER TABLE employe ADD (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 employe ADD ( code_regional VARCHAR2(3) NOT NULL,telephone VARCHAR2 (7) NOT NULL,UNIQUE ( code_regional, telephone));
11
Exemples pour la contrainte UNIQUE
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 departement et
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 employe et indiquerque le nom et le prénom forment la clé primaire:
CREATE TABLE employe (nom VARCHAR2(15), prenom VARCHAR2(15), ... , PRIMARY KEY (nom, prenom) );
12
PRIMARY KEY
Permet de spécifier une intégrité référentielle entre les rangées de 2 tables
13
FOREIGN KEY
id Nom10 Recherches20 Ventes30 Comptabilité
id nom id_departement1 … 102 … 103 … 30
departement
employe
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));
14
FOREIGN KEY / REFERENCES
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));
15
FOREIGN KEY / REFERENCES (suite)
On doit faire référence à une clé primaire ouune 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 desré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.
16
Notes surFOREIGN KEY / REFERENCES
id nom id_patron1 President Null2 John 13 Jane 1
Syntaxe: ( contrainte de table ou de colonne) CHECK ( Condition )
Pour une contrainte de table, la condition peutimpliquer 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 ) );
17
CHECK
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 employe ADD CONSTRAINT fk_emp_dept FOREIGN KEY (id_departement) REFERENCES departement(id) ON DELETE CASCADE ;
18
Contrainte en cascade
On utilise l'énoncé ALTER TABLE
ALTER TABLE table [ ADD ( { définition_colonne |
contrainte_table } ,...) ] [ MODIFY ( { définition_colonne} ,...) ]
19
Modificationdes contraintes
Pour les requêtes qui sont régulièrement utilisées, il est extrèmement important d’utiliser des index.
En supposant que cette requête est constamment utilisé.
SELECT nom, prenom FROM usagerWHERE
nom_usager = ‘Arthax’ ANDmot_de_passe = ‘md5asdd’;
Afin d’avoir une requête performante, il faudra y ajouter un index sur les colonnes nom_usager et mot_de_passe
20
Les index
On ne met pas des index n’importe où, il faut identifier les requêtes les plus communes
Exemple : ◦ Comme on fait régulièrement des
requêtes sur la colonne “ville” de la table “departement”, on y ajoute un index
CREATE INDEX idx_dept_ville ON departement (ville)
21
Les index (suite)
Les clés primaires, étrangères et les contraintes uniques font automatiquement des index. Il n’est donc pas nécessaire d’en ajouter.
Imaginons une requête qui possède une clause WHERE sur un champ non indexé, et sur une 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
22
Les index (suite)
Il arrive qu’une relation entre deux tables soit de plusieurs-à-plusieurs
Exemple : Table personneet Table intérêt (ex: cinéma, lecture)
Une personne peut avoir plusieurs intérêtsUn intérêt peut être partagé par plusieurs personnes
Il faut donc faire une table d’association afin de pouvoir les relier ensemble
23
Les tables d’association
24
Les tables d’association (suite)
id_personne id_interet1 12 12 22 3
id prenom nom1 Roger Rabbit2 Eddie Valliant
id nom1 Cinéma2 Lecture3 Vélo
personne
interet
interet_personne