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