Newsletter Developpez.com

Inscrivez-vous gratuitement au Club pour recevoir
la newsletter hebdomadaire des développeurs et IT pro

Developpez.com - SGBD & SQL
X

Choisissez d'abord la catégorieensuite la rubrique :


Requêtes CREATE TABLE avec Oracle 9i DataBase

07/09/2003

Par giminik (Matthieu Petiot) (autres articles)
 

niveau : normal

durée : 20 minutes

Cet article vous guidera dans la création de tables à partir d'un modèle conceptuel de données. Il est destiné aux débutants qui souhaitent apprendre et visualiser les résultats que produisent les requêtes SQL. Les bases du langage SQL sont nécessaires.
J'adresse mes remerciements à armatatuxa qui a gracieusement relu cet article et corrigé les fautes d'orthographe.


Introduction
1. Description des données
1.1. Réalisation du MCD - Modèle Conceptuel de Données
1.2. Réalisation du MLD - Modèle Logique de Données
2. Un petit rappel sur l'ordre de création des tables
2.1. Syntaxe
2.2. Convention sur les noms
2.3. Quelques types de données
2.4. L'option DEFAULT
2.5. Un petit rappel à propos des contraintes
2.5.1. Convention sur les contraintes
2.5.2. La contrainte NOT NULL
2.5.3. La contrainte UNIQUE
2.5.4. La contrainte PRIMARY KEY
2.5.5. La contrainte FOREIGN KEY ... REFERENCES
2.5.6. La contrainte CHECK
3. Ordres SQL de création des tables
3.2. Création de la table localisation
3.3. Création de la table departement
3.4. Création de la table fonction
3.5. Création de la table employe
4. Structure d'une table
5. Conclusion


Introduction


Ce tutoriel traite un exemple concret et simple, sans doute l'exemple le plus utilisé pour apprendre les ordres SQL. Il s'agit de la répartition des employés dans une entreprise. Les tables de ce tutoriel sont pratiquement identiques aux tables de test fournies avec Oracle, mais il est plus intéressant de créer ses propres tables.


1. Description des données


Chaque employé est caractérisé par un numéro, un nom, un prénom, une date d'embauche, un salaire, une éventuelle commission et un sexe. Chaque département est caractérisé par un numéro et un nom. Chaque localisation est caractérisée par un numéro et un nom. Chaque fonction est caractérisée par un numéro et un nom. Tous les employés travaillent dans un et un seul département qui est localisé dans une et une seule ville. Chaque employé est affecté à une et une seule fonction. Un employé peut avoir un supérieur hiérarchique.


1.1. Réalisation du MCD - Modèle Conceptuel de Données


Ce modèle représente les entités et les relations les reliant. Les identifiants sont soulignés.


1.2. Réalisation du MLD - Modèle Logique de Données


Ce modèle représente la structure de chaque table, comprenant les liens entre les tables. Les clés primaires sont soulignés, et les clés étrangères sont suivies d'un #. La création des tables doit se faire de gauche à droite.

Formes Normales :
Chaque table possède une clé primaire, tous les champs de toutes les tables sont atomiques (un champ ne contient qu'un type d'élèment). Chaque champ a une signification précise et constante dans le temps. Ces tables sont donc conformes à la 1ère forme normale 1FN .
Etant validées en 1FN et ayant toutes leurs propriétés non-clé totalement dépendantes de la totalité de la clé primaire , les tables sont conformes à la 2ème forme normale 2FN .
Etant validées en 2FN et n'ayant aucun champ non-clé en dépendance transitive avec la clé primaire , les tables sont conformes à la 3ème forme normale 3FN .


2. Un petit rappel sur l'ordre de création des tables



2.1. Syntaxe


CREATE TABLE <nom_de_la_table> (
    <colonne1> <type>,
    <colonne2> <type>
);


2.2. Convention sur les noms


Il est nécessaire de respecter quelques conventions portant sur les noms de tables et de colonne.
Les noms doivent commencer par une lettre, être composés de 1 à 30 caractères (A à Z, a à z, 0 à 9, -, $, #), ne pas porter le nom d'un autre objet appartenant au même utilisateur, et ne pas être un mot réservé d'Oracle.


2.3. Quelques types de données


  • VARCHAR2(<taille>) :
    chaîne de caractères de longueur variable (1 à 4000).
  • CHAR(<taille>) :
    chaîne de caractères de longueur fixe (1 à 2000).
  • DATE :
    valeur de date et heure allant du 01/01/-4712 au 31/12/9999.
  • NUMBER(p, s) :
    p étant la précision (nombre total de chiffres allant de 1 à 38),
    e étant le nombre de chiffres après la virgule.


2.4. L'option DEFAULT


Cette option sert à définir la valeur par défaut d'une colonne, si aucune valeur n'y est insèrée.
Un petit exemple :

CREATE TABLE test_nom (
    Nom VARCHAR2 (30) DEFAULT 'NOM INCONNU',
    Prenom VARCHAR2 (30) DEFAULT 'PRENOM INCONNU'
);

Ainsi, lors d'une insertion, si le nom n'est pas renseigné, la valeur 'NOM INCONNU' sera insèrée.


2.5. Un petit rappel à propos des contraintes


Le but essentiel des contraintes est de contrôler les règles de gestion, garantir l'intégrité des données ou encore empêcher la suppression de données lorsqu'il y a des dépendances.

Les différentes contraintes sont :

  • NOT NULL
  • UNIQUE
  • PRIMARY KEY
  • FOREIGN KEY
  • CHECK


2.5.1. Convention sur les contraintes


Il est recommandé, mais pas obligatoire, de donner un nom à chaque contrainte, si ce nom n'est pas renseigné Oracle lui en attribut un par défaut composé de la chaîne SYS_C suivie d'un numéro à 6 chiffres. On peut la définir à la création de la table ou après. Elle s'utilise au niveau d'une colonne ou d'une table. Nous allons nous en servir dans les points suivants.

CREATE TABLE <nom_de_la_table> (
    <colonne1> <type> [DEFAULT <expression>] [<contrainte_colonne>],
    <colonne1> <type> [DEFAULT <expression>] [<contrainte_colonne>]
    [, <contrainte_table>]
);

Un contrainte de colonne n'agit que sur une colonne. Elle se définit à la suite de la déclaration de la colonne.

Une contrainte de table agit sur une ou plusieurs colonnes. Elle se définit à la fin de l'ordre CREATE TABLE .


2.5.2. La contrainte NOT NULL


Cette contrainte interdit à une colonne de contenir une valeur NULL . Elle se définit uniquement au niveau colonne.
Exemple :

CREATE TABLE test_nom (
    Nom VARCHAR2 (30) CONSTRAINT test_nom_Nom_nn NOT NULL ,
    Prenom VARCHAR2 (30) CONSTRAINT test_nom_Prenom_nn NOT NULL
);

Par convention, on nomme la contrainte de cette façon : Table Colonne InitialesContrainte, séparés par des '_'. De cette manière, un message d'erreur sera retourné par Oracle si une valeur NULL se présentait.


2.5.3. La contrainte UNIQUE


Elle interdit à une colonne ou à un groupe de colonnes d'avoir des doublons, sauf pour les valeurs NULL. Elle peut donc se définir en contrainte de colonne ou de table.

CREATE TABLE test_nom (
    Nom VARCHAR2 (30) CONSTRAINT test_nom_Nom_uk UNIQUE ,
    Prenom VARCHAR2 (30) CONSTRAINT test_nom_Prenom_uk UNIQUE
);

Dans ce cas, il ne peut y avoir deux personnes ayant le même prénom, ou ayant le même nom, c'est un peu gênant...

CREATE TABLE test_nom (
    Nom VARCHAR2 (30),
    Prenom VARCHAR2 (30),
    CONSTRAINT test_nom_Nom_Prenom_uk UNIQUE (Nom, Prenom)
);

Dans ce cas, il peut y avoir plusieurs fois le même prénom, ou nom, mais pas plusieurs fois le même couple nom + prénom.


2.5.4. La contrainte PRIMARY KEY


Cette contrainte sert à définir la clé primaire sur une ou plusieurs colonnes, ce qui interdit les valeurs NULL et les doublons (UNIQUE). Elle se définit au niveau table ou colonne.

CREATE TABLE test_nom (
    idNom NUMBER (3) CONSTRAINT test_nom_idNom_pk PRIMARY KEY ,
    Nom VARCHAR2 (30) CONSTRAINT test_nom_Nom_nn NOT NULL ,
    Prenom VARCHAR2 (30) CONSTRAINT test_nom_Prenom_nn NOT NULL ,
    CONSTRAINT test_nom_Nom_Prenom_uk UNIQUE (Nom, Prenom)
);

Revient à faire :

CREATE TABLE test_nom (
    Nom VARCHAR2 (30),
    Prenom VARCHAR2 (30),
    CONSTRAINT test_nom_Nom_Prenom_pk PRIMARY KEY (Nom, Prenom)
);

La seconde requête étant plus simple, il est préfèrable d'utiliser la première qui comporte un identifiant numérique et qui offre de meilleures performances.


2.5.5. La contrainte FOREIGN KEY ... REFERENCES


Elle sert à créer le lien entre deux tables. Elle fait forcément référence à une clé primaire. Il est donc impératif que la colonne à laquelle elle fait référence soit existante, sinon Oracle retournera un message d'erreur. Elle se définit au niveau table ou colonne. Reprenons les deux exemple du point précédent, avec une table test_fonction faisant référence à test_nom.

Cette table est celle qu'il faudrait utiliser si l'on avait opté pour le premier exemple du point précédent. Notez que, lors d'une contrainte de colonne, le terme FOREIGN KEY disparaît.

CREATE TABLE test_fonction (
    NomFonction VARCHAR2 (50) NOT NULL UNIQUE ,
    idNom NUMBER (3) CONSTRAINT test_fonction_idNom_fk REFERENCES test_nom(idNom)
);

Celle ci est à utiliser avec le deuxième exemple du point précédent.

CREATE TABLE test_fonction (
    NomFonction VARCHAR2 (50) NOT NULL UNIQUE ,
    Nom VARCHAR2 (30),
    Prenom VARCHAR2 (30),
    CONSTRAINT test_fonction_idNom_fk FOREIGN KEY (Nom, Prenom) REFERENCES test_nom(Nom, Prenom)
);

Notez que vous pouvez utiliser l'option ON DELETE CASCADE en fin de ligne de la contrainte FOREIGN KEY. Dans cet exemple, si vous supprimiez une ligne de la table test_fonction, Oracle supprimerait les lignes dépendantes de la table test_nom.


2.5.6. La contrainte CHECK


Cette contrainte sert à définir une condition à vérifier lors de l'insertion des données. Elle se définit au niveau table.
Exemple : un salaire doit être supérieur à 1000. Si une valeur inférieure à 1000 est insérée, Oracle répondra par un message d'erreur.

CREATE TABLE test_check (
    salaire NUMBER (4),
    sexe CHAR (1) NOT NULL ,
    CONSTRAINT test_salaire_salaire_ck CHECK (salaire > 1000)
);


3. Ordres SQL de création des tables


On crée d'abord les tables statiques, ensuite les tables dynamiques. Dans cet exemple, on nomme toutes les contraintes , nous en verrons l'utilité plus tard. Pour exécuter vos requêtes, utilisez SQLPlus Worksheet . Si aucun autre utilisateur n'existe, il faut utiliser le compte scott ayant pour mot de passe tiger .


3.2. Création de la table localisation


Cette table contient des noms de lieu nomLoc uniques et non nuls identifiés par la clé primaire idLoc.

CREATE TABLE localisation (
    idLoc NUMBER (3) CONSTRAINT localisation_idLoc_pk PRIMARY KEY ,
    nomLoc VARCHAR2 (40) CONSTRAINT localisation_nomLoc_nn NOT NULL ,
    CONSTRAINT localisation_nomLoc_uk UNIQUE (nomLoc)
);


3.3. Création de la table departement


Cette table contient les noms des départements (ou secteurs) uniques et non nuls identifiés par la clé primaire idDep et contenant la clé étrangère non nulle idLoc renvoyant à la table localisation. La relation entre ces deux tables est de type 1 à N .

CREATE TABLE departement (
    idDep NUMBER (3) CONSTRAINT departement_idDep_pk PRIMARY KEY ,
    nomDep VARCHAR2 (40) CONSTRAINT departement_nomDep_nn NOT NULL ,
    idLoc NUMBER (3) CONSTRAINT departement_idLoc_nn NOT NULL ,
    CONSTRAINT departement_nomDep_uk UNIQUE (nomDep),
    CONSTRAINT departement_idLoc_fk FOREIGN KEY (idLoc) REFERENCES localisation(idLoc)
);


3.4. Création de la table fonction


Cette table contient des noms de lieu nomFonc uniques et non nuls identifiés par la clé primaire idFonc.

CREATE TABLE fonction (
    idFonc NUMBER (3) CONSTRAINT fonction_idFonc_pk PRIMARY KEY ,
    nomFonc VARCHAR2 (40) CONSTRAINT fonction_nomFonc_nn NOT NULL ,
    CONSTRAINT fonction_nomFonc_uk UNIQUE (nomFonc)
);


3.5. Création de la table employe


Cette table contient les noms et prénoms des employés (nom + prénom unique et non nul ), date d'embauche et salaire non nuls , une commission éventuelle, un sexe forcément connu (H ou F), le tout identifié par la clé primaire idEmp. La clé étrangère non nulle idFonc renvoie à la table fonction, c'est une relation de type 1 à N . La clé étrangère non nulle idDep renvoie à la table departement, c'est aussi une de type 1 à N . La clé étrangère éventuellement nulle supEmp renvoie à la table employe (elle même) et c'est une relation de type 1 à N .

CREATE TABLE employe (
    idEmp NUMBER (3) CONSTRAINT employe_idEmp_pk PRIMARY KEY ,
    nomEmp VARCHAR2 (40) CONSTRAINT employe_nomEmp_nn NOT NULL ,
    prenomEmp VARCHAR2 (40) CONSTRAINT empoye_prenomEmp_nn NOT NULL ,
    dateEmbauche DATE CONSTRAINT employe_dateEmbauche_nn NOT NULL ,
    salaireEmp NUMBER (8,2) CONSTRAINT employe_salaireEmp_nn NOT NULL ,
    commissionEmp NUMBER (8,2),
    sexeEmp VARCHAR2 (1) CONSTRAINT employe_sexeEmp_nn NOT NULL ,
    idFonc NUMBER (3) CONSTRAINT employe_idFonc_nn NOT NULL ,
    supEmp NUMBER (3),
    idDep NUMBER (3) CONSTRAINT employe_idDep_nn NOT NULL ,
    CONSTRAINT employe_idFonc_fk FOREIGN KEY (idFonc) REFERENCES fonction(idFonc),
    CONSTRAINT employe_supEmp_fk FOREIGN KEY (supEmp) REFERENCES employe(idEmp),
    CONSTRAINT employe_idDep_fk FOREIGN KEY (idDep) REFERENCES departement(idDep),
    CONSTRAINT employe_nomEmp_prenomEmp_uk UNIQUE (nomEmp, prenomEmp),
    CONSTRAINT employe_sexeEmp_ck CHECK (sexeEmp IN ('F', 'H'))
);

Notez l'utilisation de IN dans la contrainte CHECK. Ici, sexeEmp doit contenir soit la valeur 'H' soit 'F'. Ainsi, si une valeur différente de 'F' et de 'H' tente d'être insérée, un message d'erreur est retourné par Oracle.


4. Structure d'une table


Vous pouvez à tout moment consulter la structure d'une table en utilisant la commande DESCRIBE ou son abréviation DESC suivie du nom de la table. Cette commande affichera les noms et les types des champs de la table employe .

DESCRIBE employe ;


5. Conclusion


Maintenant que la création des tables a été effectuée, nous allons pouvoir passer, dans le chapitre suivant : insertion des données.



Ce document est issu de http://www.developpez.com et reste la propriété exclusive de son auteur.
La copie, modification et/ou distribution par quelque moyen que ce soit est soumise à l'obtention préalable de l'autorisation de l'auteur.
Contacter le responsable de la rubrique SGBD & SQL