- La contrainte Check d ́efinit une condition que chaque ligne de la table doit verifier
Les expressions suivantes ne sont pas autoris ́ees :
appels de fonction telle que SYSDATE
interrogations qui font r ́ef ́erence `a d’autres valeurs dans d’autres
lignes.
Exemple
Au niveau colonne :
CREATE TABLE Fournisseurs (fournisseur id number(10) CHECK (fournisseur id
between 10 and 1000), nom varchar2(20) not null, contact varchar2(50)) ;
CREATE TABLE Fournisseurs (fournisseur id number(10) CONSTRAINT
ck fournisseurs CHECK (fournisseur id between 10 and 1000), nom varchar2(20) not
null, contact varchar2(50)) ;
Au niveau table :
CREATE TABLE Fournisseurs (fournisseur id number(10), nom varchar2(20) not null,
contact varchar2(50), CONSTRAINT ck fournisseurs CHECK (fournisseur id between
10 and 1000)) ;
Les contraintes
La contrainte PRIMARY KEY
Une contrainte cl ́e primaire cr ́ee une cl ́e primaire pour la table
Une seule cl ́e primaire peut ˆetre cr ́e ́ee par table
La cl ́e primaire peut ˆetre constitu ́ee d’une ou plusieurs colonnes.
Aucune des colonnes faisant partie de la cl ́e primaire ne peut ˆetre
NULL
Exemple
Au niveau colonne :
CREATE TABLE Fournisseurs (fournisseur id number(10) PRIMARY KEY, nom
varchar2(20) not null, contact varchar2(50)) ;
CREATE TABLE Fournisseurs (fournisseur id number(10) CONSTRAINT
pk fournisseurs PRIMARY KEY, nom varchar2(20) not null, contact varchar2(50)) ;
Au niveau table :
CREATE TABLE Fournisseurs (fournisseur id number(10), nom varchar2(20) not null,
contact varchar2(50), CONSTRAINT pk fournisseurs PRIMARY
KEY(fournisseur id)) ;
CREATE TABLE Etudiants(nom varchar2(30), prenom varchar2(30), date naiss date,
CONSTRAINT pk etudiants PRIMARY KEY (nom,prenom))
Les contraintes
La contrainte FOREIGN KEY
Syntaxe
Au niveau table :
CREATE TABLE <nom table>
(<col1> type null/not null,
<col2> type null/not null,
...,
CONSTRAINT <fk table colonne> FOREIGN KEY (col1, col2, ... coln) REFERENCES
<table parente> (col1, col2, ... coln)
ON DELETE {CASCADE|SET NULL|SET DEFAULT|RESTRICT}
ON UPDATE {CASCADE|SET NULL|SET DEFAULT|RESTRICT}
) ;
Au niveau colonne :
CREATE TABLE <nom table>
(<col1> type null/not null CONSTRAINT <fk table colonne> REFERENCES <table parente>
(col) ON DELETE {CASCADE|SET NULL|SET DEFAULT|RESTRICT}
ON UPDATE {CASCADE|SET NULL|SET DEFAULT|RESTRICT},
<col2> type null/not null,
...,
) ;
Les contraintes
La contrainte FOREIGN KEY
FOREIGN KEY : d ́efinit la colonne dans la table fille au niveau
contrainte de table
REFERENCES : identifie la table et la colonne dans la table parente
Notez Bien
Cr ́eer les tables parentes avant les tables filles
Supprimer les tables filles avant les tables parentes
Les contraintes
La contrainte FOREIGN KEY : mot cl ́e ON DELETE
Les options possibles avec le mot cl ́e ON DELETE :
CASCADE : supprime les lignes d ́ependantes dans la table fille
lorsqu’une ligne de la table parente est supprim ́ee
SET NULL : convertit les valeurs des cl ́es ́etrang`eres d ́ependantes en
valeurs NULL. Cette contrainte ne peut ˆetre ex ́ecut ́ee que si toutes
les colonnes de cl ́e ́etrang`ere de la table cible acceptent des
valeurs NULL.
SET DEFAULT place la valeur par d ́efaut (qui suit ce param`etre)
dans la ligne de la table ́etrang`ere en cas d’effacement d’une valeur
correspondant `a la cl ́e (option n’est pas prise en charge dans oracle)
RESTRICT indique une erreur en cas d’effacement d’une valeur
correspondant `a la cl ́e (option n’est pas prise en charge dans oracle)
Les contraintes
La contrainte FOREIGN KEY : mot cl ́e ON UPDATE
Les options possibles avec le mot cl ́e ON UPDATE (option n’est pas prise
en charge dans oracle) :
CASCADE : met `a jour les lignes d ́ependantes dans la table fille
lorsqu’une ligne de la table parente est mise `a jour
SET NULL : convertit les valeurs des cl ́es ́etrang`eres d ́ependantes en
valeurs NULL. Cette contrainte ne peut ˆetre ex ́ecut ́ee que si toutes
les colonnes de cl ́e ́etrang`ere de la table cible acceptent des
valeurs NULL.
SET DEFAULT place la valeur par d ́efaut (qui suit ce param`etre)
dans la ligne de la table ́etrang`ere en cas de mise `a jour d’une valeur
correspondant `a la cl ́e
RESTRICT indique une erreur en cas de mise `a jour d’une valeur
correspondant `a la cl ́e
Les contraintes
La contrainte FOREIGN KEY
Exemple 1
CREATE TABLE Fournisseurs ( fournisseur id number(10) not null PRIMARY KEY, nom
varchar2(50) not null, contact varchar2(50)) ;
CREATE TABLE Produits (produit id number(10) not null, fournisseur id number(10) not null,
CONSTRAINT fk produits FOREIGN KEY (fournisseur id ) REFERENCES Fournisseurs
(fournisseur id ) ON DELETE cascade) ;
Exemple 2
CREATE TABLE Fournisseurs ( fournisseur id number(10) not null, nom varchar2(50) not null,
contact varchar2(50),CONSTRAINT pk fournisseur PRIMARY KEY (fournisseur id ,nom)) ;
CREATE TABLE Produits (produit id number(10) not null, fournisseur id number(10) not null,
nom varchar2(50) not null, CONSTRAINT fk produits FOREIGN KEY (fournisseur id , nom)
REFERENCES Fournisseurs (fournisseur id ,nom)) ;
Modification de table
L’instruction ALTER TABLE : permet de modifier la structure de la table
colonne :
ajouter une colonne (ADD)
modifier une colonne existante (MODIFY) –propri ́et ́e d’oracle
supprimer une colonne (DROP)
contrainte :
ajouter une contrainte (ADD CONSTRAINT)
supprimer une contrainte (DROP CONSTRAINT)
Modification de table
Ajouter une colonne
Syntaxe
ALTER TABLE <nom table> ADD (<column> type [,
<column> type] ,...) ;
Exemple
ALTER TABLE Fournisseurs ADD( adresse varchar2(50), telephone
number(8) NOT NULL) ;
Modification de table
Modifier une colonne
La modification d’une colonne peut concerner :
le type de donn ́ees,
la taille,
la valeur par d ́efaut d’une colonne (affecte uniquement les nouvelles
insertions dans la table)
Syntaxe
ALTER TABLE <nom table> MODIFY (<column> type [, <column> type] ,...) ;
Exemple
ALTER TABLE Fournisseurs Modify(adresse varchar2(100), telephone
number(13)) ;
Modification de table
Supprimer une colonne
Syntaxe
ALTER TABLE <nom table> DROP (column1, column2, ...) ;
Exemple
ALTER TABLE Fournisseurs DROP (adresse, telephone)
Modification de table
Ajouter une contrainte
Syntaxe
ALTER TABLE <nom table> ADD type contrainte
(<nom colonne>) ;
Exemples
ALTER TABLE Fournisseurs ADD CONSTRAINT uq fournisseurs UNIQUE(fournisseur id) ;
ALTER TABLE Fournisseurs ADD CONSTRAINT ck fournisseurs CHECK (fournisseur id
between 10 and 1000) ;
ALTER TABLE Fournisseurs ADD CONSTRAINT pk fournisseurs PRIMARY KEY
(fournisseur id) ;
ALTER TABLE Produits add constraint fk produits FOREIGN KEY (fournisseur id, nom)
REFERENCES Fournisseurs(fournisseur id, nom) ;
Pour ajouter une contrainte NULL/NOT NULL on utilise l’option MODIFY
Exemple :
ALTER TABLE Fournisseurs MODIFY contact CONSTRAINT nn fournisseurs contact NOT
NULL ;
Modification de table
Supprimer une contrainte
Syntaxe
ALTER TABLE <nom table> DROP CONSTRAINT
<nom contrainte> ;
Exemple
ALTER TABLE Fournisseurs DROP CONSTRAINT ck fournisseurs ;
ALTER TABLE Fournisseurs DROP CONSTRAINT pk fournisseurs
Modification de table
Activer/D ́esactiver une contrainte
Syntaxe
ALTER TABLE <nom table> ENABLE | DISABLE CONSTRAINT
<nom contrainte> ;
Exemple
CREATE TABLE T1(a1 number PRIMARY KEY, b1 varchar2(10)) ;
CREATE TABLE T2(a2 varchar2(10) PRIMARY KEY, b2 number CONSTRAINT fk T2 REFERENCES T1(a1)) ;
ALTER TABLE T1 ADD CONSTRAINT fk T1 FOREIGN KEY (b1) REFERENCES T2(a2) ;
=⇒Pour ins ́erer des lignes dans ces deux tables il faut d ́esactiver l’une des contraintes FOREIGN KEY :
ALTER TABLE T1 DISABLE CONSTRAINT fk T1 ;
=⇒ Ins ́erer les lignes dans T1 et T2 :
INSERT INTO T1 VALUES(1,’a’) ;
INSERT INTO T1 VALUES(2,’b’) ;
INSERT INTO T2 VALUES(’b’,1) ;
=⇒ Activer la contrainte fk T1 :
ALTER TABLE T1 ENABLE CONSTRAINT fk T1 ;
La v ́erification des valeurs ins ́er ́ees se fait lors de l’activation de la contrainte fk T1
Suppression de table
Syntaxe
DROP TABLE <nom table> ;
Remarque
Il existe aussi la commande TRUNCATE qui permet de vider la table.
Syntaxe :
TRUNCATE TABLE nom table ;
Renommage de table
Syntaxe
RENAME <ancien nom> TO <nouveau nom> ;
Exemple
RENAME Fournisseurs TO LesFournisseurs ;
Vue
Une vue est une table logique sur une ou plusieurs autres tables ou
vues,
Seule la d ́efinition de la vue (requˆete) est enregistr ́ee dans la base, et
pas les donn ́ees de la vue,
Limite l’acc`es `a la base de donn ́ees,
Facilite la cr ́eation des requˆetes complexes,
Pr ́esente les mˆemes donn ́ees sous diff ́erentes formes,
Il existe deux types de vues :
vue simple :
utilise une seule table
ne contient ni fonction ni groupe de donn ́ees
permet d’ex ́ecuter des instructions LMD (UPDATE, DELETE,
INSERT)
vue complexe :
utilise plusieurs tables
contient des fonctions ou des groupes de donn ́ees
ne permet pas des instructions LMD (UPDATE, DELETE, INSERT)
Cr ́eation et modification de vue
Syntaxe
CREATE VIEW <nom vue> [(alias [, alias], . . . )]
AS SELECT <requˆete> ]
] ;
FORCE : cr ́ee la vue que les tables existent ou non
alias : indique les noms des expressions s ́electionn ́ees par la requˆete de la vue. Le nombre
d’alias doit ˆetre ́egal au nombre d’expressions s ́electionn ́ees
WITH CHECK OPTION : n’autorise l’insertion et la mise `a jour des lignes que pour les
lignes auxquelles la vue peut acc ́eder (v ́erifient les conditions de la clause WHERE)
CONSTRAINT : donne un nom de contrainte aux restrictions WITH CHECK OPTION et
WITH READ ONLY (messages d’erreur mieux compr ́ehensibles)
WITH READ ONLY : aucune op ́eration LMD ne peut ˆetre ex ́ecut ́ee.
Notez Bien
Pour pouvoir ins ́erer dans une vue, la vue doit comprendre au moins les colonnes sur lesquelles on
a une contrainte NOT NULL
Cr ́eation et modification de vue
Exemple
CREATE TABLE Fournisseurs (fournisseur id number(10) PRIMARY KEY, nom
varchar2(20) not null, contact varchar2(50), code region number(3)) ;
1 CREATE OR REPLACE VIEW vue1 Fournisseurs 10 (numero,nom,region) AS SELECT
fournisseur id, nom, code region from fournisseurs where code region=10 ;
2 CREATE OR REPLACE VIEW vue2 Fournisseurs 10 (numero,nom,region) AS SELECT
fournisseur id, nom, code region from fournisseurs where code region=10 WITH CHECK
OPTION CONSTRAINT ck 10 ;
INSERT INTO vue2 Fournisseurs 10 VALUES (100, ’Daniel’,100) ;
=⇒ORA-01402 : vue WITH CHECK OPTION - violation de clause WHERE
INSERT INTO vue2 Fournisseurs 10 VALUES (100, ’Daniel’,10) ;
=⇒1 ligne(s) ins ́er ́ee(s).
3 CREATE OR REPLACE VIEW vue3 Fournisseurs 10 (numero,nom,region) AS SELECT
fournisseur id, nom, code region from fournisseurs where code region=10 WITH READ
ONLY
Suppression de vue
Syntaxe
DROP VIEW <nom vue> ;
Exemple
DROP VIEW vue3 Fournisseurs 10
Une s ́equence :
G ́en`ere automatiquement des num ́eros uniques
Est Partageable entre plusieurs utilisateurs et ́eventuellement entre
plusieurs tables
Permet de cr ́eer une valeur de cl ́e primaire
Syntaxe
CREATE SEQUENCE <nom sequence>
[{MAXVALUE <valeur max> | NOMAXVALUE}]
[{MINVALUE <valeur min> | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE <cache> | NOCACHE}]
INCREMENT BY : d ́efinit l’intervalle entre les num ́eros (pas d’incr ́ementation)
START WITH : premier num ́ero de la s ́equence
MAXVALUE | NOMAXVALUE : valeur maximale
MINVALUE | NOMINVALUE : valeur minimale
CYCLE | NOCYCLE : la s ́equence peut continuer `a g ́en ́erer ou non des valeurs si la valeur
maximale (ou minimale) est atteinte (la valeur par d ́efaut NOCYCLE)
CACHE | NOCACHE : nombre de valeurs pr ́e-allou ́ees et conserv ́ees en m ́emoire (la valeur
par d ́efaut est cache=20)
Cr ́eation de s ́equence
Exemples
CREATE SEQUENCE sequence1 increment by 1 start with 1
maxvalue 5 ;
CREATE SEQUENCE sequence2 increment by 5 start with 10
maxvalue 100 NOCACHE NOCYCLE ;
CREATE SEQUENCE sequence3 increment by 1 start with 1
maxvalue 20 CACHE 10 CYCLE
CREATE SEQUENCE sequence4 increment by 5 start with -10
minvalue -20 maxvalue 5 cache 2 cycle ;
Notez Bien
La valeur du cache doit etre inf ́erieur ou ́egale au nombre de valeurs d’un
cycle
Modification de s ́equence
Syntaxe
ALTER SEQUENCE <nom sequence>
[{MAXVALUE <valeur max> | NOMAXVALUE}]
[{MINVALUE <valeur min> | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE <cache> | NOCACHE}]
Exemple
ALTER SEQUENCE sequence1 maxvalue 10 cycle
Utilisation de s ́equence
L’utilisation d’une s ́equence se fait par des ”pseudo-colonnes” CURRVAL et NEXTVAL. On
parle de pseudo-colonne car cela se manipule un peu comme une colonne de table, mais ce
n’est pas une colonne de table.
CURRVAL : retourne la valeur courante de la s ́equence
NEXTVAL : incr ́emente la s ́equence et retourne la nouvelle valeur
Exemple
CREATE TABLE Tseq(a number, b varchar2(5)) ;
CREATE SEQUENCE seq increment by 5 start with -10 minvalue -20 maxvalue 5 cache 2
cycle ;
INSERT INTO Tseq values (seq.nextval, ’a’)
SELECT * FROM Tseq ;
SELECT seq.currval FROM dual ;
Suppression de s ́equence
Syntaxe
DROP SEQUENCE <nom sequence> ;
Exemple
DROP SEQUENCE seq ;
Un index est un objet de la base de donn ́ees qui permet d’acc ́el ́erer
la recherche des lignes. Il contient deux champs :
la cl ́e d’index
l’adresse du bloc de donn ́ees contenant la cl ́e
Un index peut ˆetre cr ́e ́e juste apr`es la cr ́eation d’une table ou sur
une table contenant d ́ej`a des lignes
Un index peut porter sur plusieurs colonnes : la cl ́e d’acc`es sera la
concat ́enation des diff ́erentes colonnes
Les index sont ind ́ependants logiquement et physiquement des tables
qu’ils indexent
Cr ́eation d’index
La cr ́eation d’index peut ˆetre :
Automatique : un index unique est cr ́ee automatiquement lors de la d ́efinition d’une
contrainte PRIMARY KEY ou contrainte UNIQUE
Manuelle : un index non unique peut ˆetre cr ́ee manuellement (cl ́es d’index peuvent
ˆetre dupliqu ́ees)
Syntaxe
CREATE INDEX <nom index> ON <nom table>(col1, col2,...)
Exemple
CREATE UNIQUE INDEX idx fournisseurs ON Fournisseurs (contact) ;
=⇒des valeurs dupliqu ́ees ne sont pas permise dans la colonne contact mˆeme si il y a pas une
contrainte UNIQUE sur cette colonne
Remarque
Il est possible de renommer un index
Syntaxe :
ALTER INDEX <ancien nom> RENAME TO <nouveau nom> ;
Cr ́eation d’index
Cr ́eez index si :
La colonne doit ˆetre souvent utilis ́ee dans la clause WHERE ou une
condition de jointure
La colonne contient un grand nombre de valeurs NULL
Deux ou plusieurs colonnes sont souvent utilis ́ees conjointement dans une
clause WHERE ou une condition de jointure
La table est de grande taille et la plupart des requˆetes doivent extraire
moins de 2 `a 4% des lignes
Ne pas cr ́eez index si :
La table est de petite taille
La table est souvent mise `a jour
Les colonnes ne sont pas souvent utilis ́ees comme condition dans une
requˆete
La plupart des requˆetes sont pr ́evues pour extraire un tr`es grand
pourcentage de lignes
Supression d’index
Syntaxe
DROP INDEX <nom index> ;
Exemple
DROP INDEX idx fournisseurs
Synonyme
Un synonyme est un alias sur un Objet de la base ou Sch ́ema, une
sorte de raccourcis.
L’objet peut ˆetre une table, une vue, une s ́equence, une proc ́edure,
une fonction, un package, etc
Le synonyme peut ˆetre publique ou priv ́e.
publique : accessible `a partir de tous sch ́ema et utilisateurs
priv ́e il sera accessible uniquement `a partir du sch ́ema dans lequel il a
́et ́e cr ́e ́e
Cr ́eer des synonymes pour :
masquer le vrai nom des objets et leur localisations
simplifier les noms des objets
́eviter le pr ́e-fixage dans les requˆetes avec le nom de son propri ́etaire
Cr ́eation et supression de synonyme
Cr ́eation :
Syntaxe
CREATE SYNONYM
<nom synonyme> FOR <nom objet> ;
Supression
Syntaxe
DROP SYNONYM <nom synonyme> ;