Langage de définition de données/Data Definition Language (DDL)

Le SQL Server propose des instructions Transact-SQL concernant le langage de définition de données ou Data Definition Language (DDL). Les instructions DDL sont divisées en trois groupes. Le premier groupe comprend des instructions créant des objets, le deuxième groupe comprend des instructions modifiant la structure des objets et le troisième groupe comprend des instructions supprimant des objets.

Création d'objets de base de données

L'organisation d'une base de données implique de nombreux objets différents. Tous les objets d'une base de données peuvent être physiques ou logiques. Les objets physiques sont liés à l'organisation des données sur le périphérique physique (disque). Les objets physiques du moteur de base de données sont des fichiers et des groupes de fichiers. Les objets logiques représentent la vue d'un utilisateur d'une base de données. Les bases de données, les tables, les colonnes et les vues (tables virtuelles) sont des exemples d'objets logiques. Le premier objet de base de données devant être créé est une base de données elle-même. Le moteur de base de données gère à la fois les bases de données système et utilisateur. Un utilisateur autorisé peut créer des bases de données utilisateur, tandis que les bases de données système sont générées lors de l'installation du système de base de données. Les bases de données système sont :

• master
• tempdb
• model
• msdb
• resource

Création d'une base de données

Deux méthodes de base sont utilisées pour créer une base de données. La première méthode consiste à utiliser l'Explorateur d'objets ou Object Explorer dans le SQL Server Management Studio. La deuxième méthode consiste à utiliser l'instruction CREATE DATABASE du Transact-SQL. Cette déclaration a la format générale suivante :

Pour la syntaxe des instructions Transact-SQL, les conventions sont, les éléments facultatifs apparaissent entre parenthèses, [ ]. Les éléments écrits entre accolades, { }, et suivis de « ... » sont des éléments pouvant être répétés un nombre illimité de fois.

Si vous disposez d'un objet de base de données devant exister dans chaque base de données utilisateur, vous devez d'abord créer cet objet dans la base de données model. L'exemple suivant crée une base de données simple sans aucune autre spécification. Pour exécuter cette instruction, saisissez-la dans la fenêtre de l'éditeur de requête de SQL Server Management Studio et appuyez sur la touche F5 :

L'exemple précédent crée une base de données nommée gladir. Cette forme concise de l'instruction CREATE DATABASE est possible, car presque toutes les options de cette instruction ont des valeurs par défaut. Le système crée par défaut, deux fichiers. Le nom logique du fichier de données est gladir et sa taille d'origine est de 2 Mo. De même, le nom logique du journal de bord des transactions est gladir_log et sa taille d'origine est de 1 Mo. (Les deux valeurs de taille, ainsi que d'autres propriétés de la nouvelle base de données, dépendent des spécifications correspondantes dans la base de données modèle.) L'exemple suivant crée une base de données avec des spécifications explicites pour la base de données et les fichiers journaux de bord des transactions :

L'exemple précédent crée une base de données appelée projets. Étant donné que l'option PRIMARY est omise, le premier fichier est considéré comme le fichier principal. Ce fichier porte le nom logique projet_dat et est entreposé dans le fichier projets.mdf. La taille d'origine de ce fichier est de 10 Mo. Des portions supplémentaires de 5 Mo d'entreposage sur disque sont allouées par le système, si nécessaire. Si l'option MAXSIZE n'est pas spécifiée ou est définie sur UNLIMITED, le fichier grandira jusqu'à ce que le disque soit plein. (Les suffixes Ko, To et Mo peuvent être utilisés pour spécifier respectivement des kilo-octets, des téraoctets ou des mégaoctets, la valeur par défaut étant Mo.) Il existe également un seul fichier journal de bord des transactions avec le nom logique projets_log et le nom physique projets.ldf. Toutes les options de la spécification de fichier pour le journal des transactions ont le même nom et la même signification que les options correspondantes de la spécification de fichier pour le fichier de données. À l'aide du langage de programmation Transact-SQL, vous pouvez appliquer l'instruction USE pour modifier le contexte de la base de données vers la base de données spécifiée. (L'autre méthode consiste à sélectionner le nom de la base de données dans le menu déroulant Base de données de la barre d'outils de SQL Server Management Studio.) L'administrateur système peut affecter une base de données par défaut à un utilisateur en utilisant l'instruction CREATE LOGIN ou l'instruction ALTER LOGIN. Dans ce cas, les utilisateurs n'ont pas besoin d'exécuter l'instruction USE s'ils souhaitent utiliser leur base de données par défaut.

Création d'un instantané de base de données

L'instruction CREATE DATABASE peut également être utilisée pour créer un instantané de base de données d'une base de données existante (base de données source). Un instantané de base de données est cohérent sur le plan transactionnel avec la base de données source telle qu'elle existait au moment de la création de l'instantané. La syntaxe pour la création d'un instantané est la suivante :

Comme vous pouvez le voir, si vous souhaitez créer un instantané de base de données, vous devez ajouter la clause AS SNAPSHOT OF dans l'instruction CREATE DATABASE. L'exemple suivant crée un instantané de la base de données Gladir et l'entrepose dans le répertoire de données C:\temp (Vous devez créer ce répertoire avant de commencer l'exemple suivant) :

Un instantané de base de données existant est une copie en lecture seulement de la base de données correspondante reflétant le moment où la base de données est copiée. (Pour cette raison, vous pouvez avoir plusieurs instantanés pour une base de données existante.) Le fichier d'instantané (dans l'exemple précédent, 'C:\temp\snapshot_DB.mdf') contient uniquement les données modifiées ayant été modifiées à partir de la base de données source. Par conséquent, le processus de création d'un instantané de base de données doit inclure le nom logique de chaque fichier de données de la base de données source ainsi que les nouveaux noms physiques correspondants. Bien que l'instantané ne contienne que des données modifiées, l'espace disque nécessaire pour chaque instantané n'est qu'une petite partie de l'espace global requis pour la base de données source correspondante.

Pour créer des instantanés d'une base de données, vous avez besoin de volumes de disque NTFS, car seuls ces volumes prennent en charge la technologie de fichiers fragmentés utilisée pour entreposer les instantanés. Les instantanés de base de données sont généralement utilisés comme mécanisme pour protéger les données contre les erreurs de l'utilisateur.

Attacher et détacher des bases de données

Toutes les données d'une base de données peuvent être détachées puis rattachées au même ou à un autre serveur de base de données. Le détachement et l'attachement d'une base de données doivent être effectués si vous souhaitez déplacer la base de données. Vous pouvez détacher une base de données d'un serveur de base de données à l'aide de la procédure système sp_detach_db. (La base de données détachée doit être en mode mono-utilisateur.) Pour attacher une base de données, utilisez l'instruction CREATE DATABASE ou la procédure système sp_attach_db. Lorsque vous attachez une base de données, tous les fichiers de données doivent être disponibles. Si un fichier de données a un chemin différent de celui de la première création de la base de données, vous devez spécifier le chemin actuel du fichier.

CREATE TABLE : Format de base

L'instruction CREATE TABLE crée une nouvelle table de base avec toutes les colonnes correspondantes et leurs types de données. Le format de base de l'instruction CREATE TABLE est le suivant :

Outre les tables de base, il existe également des types spéciaux de tables telles que les tables et les vues temporaires.

Le nom d'un objet de base de données peut généralement contenir quatre parties sous le format :

Et ses paramètres ont la signification suivante :

Les noms de table, combinés au nom de schéma, doivent être uniques dans la base de données. De même, les noms de colonnes doivent être uniques dans la table.

La première contrainte est l'existence et la non-existence de valeurs NULL dans une colonne. Si NOT NULL est spécifié, l'affectation de valeurs NULL pour la colonne n'est pas autorisée. (Dans ce cas, la colonne ne peut pas contenir de valeurs NULL, et s'il y a une valeur NULL à insérer, le système renvoie un message d'erreur.) Comme déjà indiqué, un objet de base de données (dans ce cas, une table) est toujours créé dans un schéma d'une base de données. Un utilisateur ne peut créer une table que dans un schéma pour lequel il dispose des autorisations ALTER. Tout utilisateur ayant le rôle sysadmin, db_ddladmin ou db_owner peut créer une table dans n'importe quel schéma. Le créateur d'une table ne doit pas en être le propriétaire. Cela signifie que vous pouvez créer une table appartenant à quelqu'un d'autre. De même, une table créée avec l'instruction CREATE TABLE ne doit pas appartenir à la base de données actuelle si un autre nom de base de données (existant), avec le nom du schéma, est spécifié comme préfixe du nom de la table.

Le schéma auquel appartient une table a deux noms par défaut possibles. Si une table est spécifiée sans le nom de schéma explicite, le système recherche un nom de table dans le schéma par défaut correspondant. Si le nom de l'objet est introuvable dans le schéma par défaut, le système recherche dans le schéma dbo. Vous devez toujours spécifier le nom de la table avec le nom du schéma correspondant. De cette façon, vous pouvez éliminer les ambiguïtés possibles. Les tables temporaires sont un type spécial de table de base. Ils sont entreposés dans la base de données tempdb et sont automatiquement supprimés à la fin de la session. L'exemple suivant montre la création de toutes les tables de la base de données exemple (La base de données gladir doit être la base de données actuelle) :

Outre le type de données et la possibilité de nullité, la spécification de colonne peut contenir les options suivantes :

• Clause DEFAULT
• Propriété IDENTITY

La clause DEFAULT dans la définition de colonne spécifie la valeur par défaut de la colonne, c'est-à-dire qu'à chaque fois qu'une nouvelle ligne est insérée dans la table, la valeur par défaut de la colonne particulière sera utilisée si aucune valeur n'est spécifiée pour celle-ci. Une valeur constante, telle que les fonctions système USER, CURRENT_USER, SESSION_USER, SYSTEM_USER, CURRENT_TIMESTAMP et NULL, entre autres, peut être utilisée comme valeurs par défaut. Une colonne avec la propriété IDENTITY n'autorise que des valeurs entières, étant généralement affectées implicitement par le système. Chaque valeur, devant insérée dans la colonne, est calculée en incrémentant la dernière valeur insérée de la colonne. Par conséquent, la définition d'une colonne avec la propriété IDENTITY contient (implicitement ou explicitement) une valeur initiale et un incrément.

Étant donné que le moteur de base de données génère les valeurs avec la propriété IDENTITY, ces valeurs sont toujours différentes, même lorsque plusieurs utilisateurs ajoutent des lignes en même temps. Cette fonctionnalité est très utile dans un environnement multi-utilisateurs, où il est assez difficile pour un programme ordinaire de générer des valeurs numériques uniques. L'exemple suivant montre la création d'une table avec une colonne de type SQL_VARIANT :

Dans l'exemple précédent, la table contient la colonne valeur, étant de type SQL_VARIANT. Le type de données SQL_VARIANT peut être utilisé pour entreposer simultanément des valeurs de différents types de données, telles que des valeurs numériques, des chaînes de caractères et des valeurs de date. Notez que dans l'exemple précédent, le type de données SQL_VARIANT est utilisé pour les valeurs de colonne, car différentes valeurs d'attribut peuvent être de types de données différents. Par exemple, l'attribut de taille entrepose une valeur d'attribut entière et un attribut de nom entrepose une valeur d'attribut de chaîne de caractères.

CREATE TABLE et contraintes d'intégrité déclarative

L'une des fonctionnalités les plus importantes qu'un SGBD doit fournir est un moyen de maintenir l'intégrité des données. Les contraintes, permettant de vérifier la modification ou l'insertion de données, sont appelées contraintes d'intégrité. La tâche de maintien des contraintes d'intégrité peut être gérée par l'utilisateur dans des programmes d'application ou par le SGBD. Les avantages les plus importants de la gestion des contraintes d'intégrité par le SGBD sont les suivants :

• Fiabilité accrue des données
• Temps de programmation réduit
• Entretien simple

L'utilisation du SGBD pour définir les contraintes d'intégrité augmente la fiabilité des données car il n'y a aucune possibilité que les contraintes d'intégrité puissent être oubliées par un programmeur. (Si une contrainte d'intégrité est gérée par des programmes d'application, tous les programmes concernant la contrainte doivent inclure le code correspondant. Si le code est omis dans un programme d'application, la cohérence des données est compromise.) Une contrainte d'intégrité non gérée par le SGBD doit être définie dans chaque programme applicatif utilisant les données impliquées dans la contrainte. En revanche, une même contrainte d'intégrité ne doit être définie qu'une seule fois pour être manipulée par le SGBD. De plus, les contraintes imposées par l'application sont généralement plus complexes à coder que les contraintes imposées par la base de données. Si une contrainte d'intégrité est gérée par le SGBD, la modification de la structure de la contrainte ne doit être traitée qu'une seule fois, dans le SGBD. La modification d'une structure dans les programmes d'application nécessite la modification de chaque programme faisant intervenir le code correspondant. Il existe deux groupes de contraintes d'intégrité gérées par un SGBD :

• Contraintes d'intégrité déclarative
• Contraintes d'intégrité procédurale gérées par des déclencheurs

Les contraintes déclaratives sont définies à l'aide des instructions CREATE TABLE et ALTER TABLE de DDL. Il peut s'agir de contraintes au niveau des colonnes ou des contraintes au niveau des tables. Les contraintes au niveau de la colonne, ainsi que le type de données et les autres propriétés de la colonne, sont placées dans la déclaration de la colonne, tandis que les contraintes au niveau de la table sont toujours définies à la fin de l'instruction CREATE TABLE ou ALTER TABLE, après la définition de toutes les colonnes.

Il n'y a qu'une seule différence entre les contraintes au niveau des colonnes et les contraintes au niveau des tables : une contrainte au niveau des colonnes ne peut être appliquée que sur une colonne, tandis qu'une contrainte au niveau des tables peut couvrir une ou plusieurs colonnes d'une table.

Chaque contrainte déclarative a un nom. Le nom de la contrainte peut être attribué explicitement à l'aide de l'option CONSTRAINT dans l'instruction CREATE TABLE ou l'instruction ALTER TABLE. Si l'option CONSTRAINT est omise, le Database Engine attribue un nom implicite à la contrainte.

L'utilisation de noms de contraintes explicites est fortement recommandée. La recherche d'une contrainte d'intégrité peut être grandement améliorée si un nom explicite pour une contrainte est utilisé.

Toutes les contraintes déclaratives peuvent être classées en plusieurs groupes :

• Clause DEFAULT
• Clause UNIQUE
• Clause PRIMARY KEY
• Clause CHECK
• Clause FOREIGN KEY et intégrité référentielle

La définition de la valeur par défaut à l'aide de la clause DEFAULT a été présentée plus haut.

La clause UNIQUE

Parfois, plusieurs colonnes ou groupes de colonnes de la table ont des valeurs uniques et peuvent donc être utilisées comme clef primaire. Toutes les colonnes ou groupes de colonnes se qualifiant comme clefs primaires sont appelées clefs candidates. Chaque clef candidate est définie à l'aide de la clause UNIQUE dans l'instruction CREATE TABLE ou ALTER TABLE. La clause UNIQUE a la forme suivante :

L'option CONSTRAINT de la clause UNIQUE attribue un nom explicite à la clef candidate. L'option CLUSTERED ou NONCLUSTERED est liée au fait que le Database Engine génère toujours un index pour chaque clef candidate d'une table. L'index peut être groupé, c'est-à-dire que l'ordre physique des lignes est spécifié à l'aide de l'ordre indexé des valeurs de colonne. Si l'ordre n'est pas spécifié, l'index n'est pas en unité d'allocation. La valeur par défaut est NONCLUSTERED. Le col_name1 est un nom de colonne construisant la clef candidate. (Le nombre maximal de colonnes par clef candidate est de 16.)

Chaque valeur de la colonne no_projet de la table des projets est unique, y compris la valeur NULL. (Comme pour toute autre valeur avec une contrainte UNIQUE, si les valeurs NULL sont autorisées sur une colonne correspondante, il peut y avoir au plus une ligne avec la valeur NULL pour cette colonne particulière.) Si une valeur existante doit être insérée dans la colonne no_projet, le système le rejette. Le nom explicite de la contrainte définie dans l'exemple précédent est no_unique.

La clause PRIMARY KEY

La clef primaire d'une table est une colonne ou un groupe de colonnes dont les valeurs sont différentes dans chaque ligne. Chaque clef primaire est définie à l'aide de la clause PRIMARY KEY dans l'instruction CREATE TABLE ou ALTER TABLE. La clause PRIMARY KEY a la forme suivante :

Toutes les options de la clause PRIMARY KEY ont la même signification que les options correspondantes portant le même nom dans la clause UNIQUE. Contrairement à UNIQUE, la colonne PRIMARY KEY doit être NOT NULL et sa valeur par défaut est CLUSTERED. L'exemple suivant montre la spécification de la clef primaire pour la table employes de la base de données exemple.

La table employes est recréée et sa clef primaire est définie dans l'exemple précédent. La clef primaire de la table est spécifiée à l'aide de la contrainte d'intégrité déclarative nommée prim_empl. Cette contrainte d'intégrité est une contrainte au niveau de la table, car elle est spécifiée après la définition de toutes les colonnes de la table des employés. L'exemple suivant est équivalent à l'exemple précédent, à l'exception de la spécification de la clef primaire de la table employes en tant que contrainte au niveau des colonnes :

Dans l'exemple précédent, la clause PRIMARY KEY appartient à la déclaration de la colonne correspondante, avec son type de données et sa capacité de nullité. Pour cette raison, on l'appelle une contrainte au niveau de la colonne.

La clause CHECK

La contrainte de vérification spécifie les conditions pour les données insérées dans une colonne. Chaque ligne insérée dans un tableau ou chaque valeur mettant à jour la valeur de la colonne doit répondre à ces conditions. La clause CHECK est utilisée pour spécifier les contraintes de vérification. Cette clause peut être définie dans l'instruction CREATE TABLE ou ALTER TABLE. La syntaxe de la clause CHECK est :

L'expression doit évaluer une valeur booléenne (true ou false) et peut référencer n'importe quelle colonne de la table actuelle (ou simplement la colonne actuelle si elle est spécifiée comme contrainte de niveau de colonne), mais aucune autre table. La clause CHECK n'est pas appliquée lors d'une réplication des données si l'option NOT FOR REPLICATION existe. (Une base de données, ou une partie de celle-ci, est dite répliquée si elle est entreposée sur plusieurs sites. La réplication peut être utilisée pour améliorer la disponibilité des données.

La table client créée dans l'exemple précédent contient la colonne groupe_client avec la contrainte de vérification correspondante. Le système de base de données renvoie une erreur si la colonne groupe_client, après une modification de ses valeurs existantes ou après l'insertion d'une nouvelle ligne, contiendrait une valeur différente des valeurs de l'ensemble ('c1', 'c2', 'c10' ).

La clause FOREIGN KEY

Une clef étrangère est une colonne ou un groupe de colonnes dans une table contenant des valeurs correspondant aux valeurs de clef primaire dans la même table ou dans une autre. Chaque clef étrangère est définie à l'aide de la clause FOREIGN KEY combinée à la clause REFERENCES. La clause FOREIGN KEY a le format suivante :

La clause FOREIGN KEY définit explicitement toutes les colonnes appartenant à la clef étrangère. La clause REFERENCES spécifie le nom de la table avec toutes les colonnes créant la clef primaire correspondante. Le nombre et les types de données des colonnes de la clause FOREIGN KEY doivent correspondre au nombre et aux types de données correspondants des colonnes de la clause REFERENCES (et, bien entendu, les deux doivent correspondre au nombre et aux types de données des colonnes de la clef primaire de la table référencée). La table contenant la clef étrangère est appelée table de référencement et la table contenant la clef primaire correspondante est appelée table parente ou table référencée. L'exemple suivant montre la spécification de la clef étrangère dans la table travail_etranger de la base de données exemple :

La table travails_dans de l'exemple précédent est spécifiée avec deux contraintes d'intégrité déclaratives : travail_prim et travail_etranger. Les deux contraintes sont des contraintes au niveau de la table, où la première spécifie la clef primaire et la seconde la clef étrangère de la table travails_dans. De plus, la contrainte travail_etranger spécifie la table employes comme table parent et sa colonne emp_no comme clef primaire correspondante de la colonne du même nom dans la table travail_prim.

La clause FOREIGN KEY peut être omise si la clef étrangère est définie comme une contrainte au niveau de la colonne, car la colonne contrainte est la colonne implicite "liste" de la clef étrangère, et le mot-clef REFERENCES est suffisant pour indiquer quel type de contrainte cela est. Le nombre maximal de contraintes FOREIGN KEY dans une table est de 63. Une définition des clefs étrangères dans les tables d'une base de données impose la spécification d'une autre contrainte d'intégrité importante : l'intégrité référentielle.

Intégrité référentielle

Une intégrité référentielle applique des règles d'insertion et de mise à jour pour les tables avec la clef étrangère et la contrainte de clef primaire correspondante. Les exemples précédentes spécifient deux de ces contraintes : travail_prim et travail_etranger. La clause REFERENCES de l'exemple précédent détermine la table des employés comme table parent.