Data Definition Language
Encyclopedia
A data definition language or data description language (DDL) is a syntax similar to a computer programming language
for defining data structure
s, especially database schema
s.
database model, where the schema of the database was written in a language syntax describing the records
, fields
, and sets of the user data model
. Later it was used to refer to a subset of Structured Query Language (SQL) for creating tables
and constraints
. SQL-92
introduced a schema manipulation language and schema information tables to query schemas. These information tables were specified as SQL/Schemata
in SQL:2003
. The term DDL is also used in a generic sense to refer to any formal language
for describing data or information structures.
A
creates an object inside of a relational database management system
(RDBMS). The types of objects that can be created depends on which RDBMS is being used, but most support the creation of tables
, indexes
, users, synonyms and database
s. Some systems (such as PostgreSQL
) allow
and thus they may be rolled back
.
Column Definitions:
A comma-separated list consisting of any of the following
For example, the command to create a table named employees with a few sample columns would be:
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
first_name VARCHAR(50) null,
last_name VARCHAR(75) not null,
dateofbirth DATE null
);
A
removes an object from a relational database management system
(RDBMS). The types of objects that can be dropped depends on which RDBMS is being used, but most support the dropping of tables
, users, and database
s. Some systems (such as PostgreSQL
) allow
and thus be rolled back
. The typical usage is simply:
For example, the command to drop a table named employees would be:
DROP TABLE employees;
The
An
changes the properties of an object inside of a relational database management system
(RDBMS). The types of objects that can be altered depends on which RDBMS is being used. The typical usage is:
For example, the command to add (then remove) a column named bubbles for an existing table named sink would be:
ALTER TABLE sink ADD bubbles INTEGER;
ALTER TABLE sink DROP COLUMN bubbles;
relationships, usually implemented as primary key and foreign key
tags in some columns of the tables.
These two statements can be included inside a
Programming language
A programming language is an artificial language designed to communicate instructions to a machine, particularly a computer. Programming languages can be used to create programs that control the behavior of a machine and/or to express algorithms precisely....
for defining data structure
Data structure
In computer science, a data structure is a particular way of storing and organizing data in a computer so that it can be used efficiently.Different kinds of data structures are suited to different kinds of applications, and some are highly specialized to specific tasks...
s, especially database schema
Database schema
A database schema of a database system is its structure described in a formal language supported by the database management system and refers to the organization of data to create a blueprint of how a database will be constructed...
s.
History
The data definition language concept and name was first introduced in relation to the CodasylCODASYL
CODASYL is an acronym for "Conference on Data Systems Languages". This was a consortium formed in 1959 to guide the development of a standard programming language that could be used on many computers...
database model, where the schema of the database was written in a language syntax describing the records
Record (computer science)
In computer science, a record is an instance of a product of primitive data types called a tuple. In C it is the compound data in a struct. Records are among the simplest data structures. A record is a value that contains other values, typically in fixed number and sequence and typically indexed...
, fields
Field (computer science)
In computer science, data that has several parts can be divided into fields. Relational databases arrange data as sets of database records, also called rows. Each record consists of several fields; the fields of all records form the columns....
, and sets of the user data model
Data model
A data model in software engineering is an abstract model, that documents and organizes the business data for communication between team members and is used as a plan for developing applications, specifically how data is stored and accessed....
. Later it was used to refer to a subset of Structured Query Language (SQL) for creating tables
Table (database)
In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical columns and horizontal rows. A table has a specified number of columns, but can have any number of rows...
and constraints
Integrity constraints
Integrity constraints are used to ensure accuracy and consistency of data in a relational database. Data integrity is handled in a relational database through the concept of referential integrity...
. SQL-92
SQL-92
SQL-92 was the third revision of the SQL database query language. Unlike SQL-89, it was a major revision of the standard. For all but a few minor incompatibilities, the SQL-89 standard is forwards-compatible with SQL-92....
introduced a schema manipulation language and schema information tables to query schemas. These information tables were specified as SQL/Schemata
SQL/Schemata
The SQL/Schemata, or Information and Definition Schemas, part to the SQL standard is defined by ISO/IEC 9075-11:2008. SQL/Schemata defines the Information Schema and Definition Schema, providing a common set of tools to make SQL databases and objects self-describing...
in SQL:2003
SQL:2003
SQL:2003 is the fifth revision of the SQL database query language. The latest revision of the standard is SQL:2008.-Summary:The SQL:2003 standard makes minor modifications to all parts of SQL:1999 , and officially introduces a few new features such as:* XML-related features * Window functions* the...
. The term DDL is also used in a generic sense to refer to any formal language
Formal language
A formal language is a set of words—that is, finite strings of letters, symbols, or tokens that are defined in the language. The set from which these letters are taken is the alphabet over which the language is defined. A formal language is often defined by means of a formal grammar...
for describing data or information structures.
SQL
Unlike many data description languages, SQL uses a collection of imperative verbs whose effect is to modify the schema of the database by adding, changing, or deleting definitions of tables or other objects. These statements can be freely mixed with other SQL statements, so the DDL is not truly a separate language.CREATE statements
Create - To make a new database, table, index, or stored query.A
CREATE statement in SQLSQL
SQL is a programming language designed for managing data in relational database management systems ....
creates an object inside of a relational database management system
Relational database management system
A relational database management system is a database management system that is based on the relational model as introduced by E. F. Codd. Most popular databases currently in use are based on the relational database model....
(RDBMS). The types of objects that can be created depends on which RDBMS is being used, but most support the creation of tables
Table (database)
In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical columns and horizontal rows. A table has a specified number of columns, but can have any number of rows...
, indexes
Index (database)
A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space...
, users, synonyms and database
Database
A database is an organized collection of data for one or more purposes, usually in digital form. The data are typically organized to model relevant aspects of reality , in a way that supports processes requiring this information...
s. Some systems (such as PostgreSQL
PostgreSQL
PostgreSQL, often simply Postgres, is an object-relational database management system available for many platforms including Linux, FreeBSD, Solaris, MS Windows and Mac OS X. It is released under the PostgreSQL License, which is an MIT-style license, and is thus free and open source software...
) allow
CREATE, and other DDL commands, inside of a transactionDatabase transaction
A transaction comprises a unit of work performed within a database management system against a database, and treated in a coherent and reliable way independent of other transactions...
and thus they may be rolled back
Rollback (data management)
In database technologies, a rollback is an operation which returns the database to some previous state. Rollbacks are important for database integrity, because they mean that the database can be restored to a clean copy even after erroneous operations are performed...
.
CREATE TABLE statement
Perhaps the most commonCREATE command is the CREATE TABLE command. The typical usage is:CREATE [TEMPORARY] TABLE [table name] ( [column definitions] ) [table parameters].Column Definitions:
A comma-separated list consisting of any of the following
- Column definition: [column name] [data type] {
NULL|NOT NULL} {column options} - Primary key definition:
PRIMARY KEY( [comma separated column list] ) - Constraints: {
CONSTRAINT} [constraint definition] - RDBMS specific functionality
For example, the command to create a table named employees with a few sample columns would be:
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
first_name VARCHAR(50) null,
last_name VARCHAR(75) not null,
dateofbirth DATE null
);
DROP statements
Drop - To destroy an existing database, table, index, or view.A
DROP statement in SQLSQL
SQL is a programming language designed for managing data in relational database management systems ....
removes an object from a relational database management system
Relational database management system
A relational database management system is a database management system that is based on the relational model as introduced by E. F. Codd. Most popular databases currently in use are based on the relational database model....
(RDBMS). The types of objects that can be dropped depends on which RDBMS is being used, but most support the dropping of tables
Table (database)
In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical columns and horizontal rows. A table has a specified number of columns, but can have any number of rows...
, users, and database
Database
A database is an organized collection of data for one or more purposes, usually in digital form. The data are typically organized to model relevant aspects of reality , in a way that supports processes requiring this information...
s. Some systems (such as PostgreSQL
PostgreSQL
PostgreSQL, often simply Postgres, is an object-relational database management system available for many platforms including Linux, FreeBSD, Solaris, MS Windows and Mac OS X. It is released under the PostgreSQL License, which is an MIT-style license, and is thus free and open source software...
) allow
DROP and other DDL commands to occur inside of a transactionDatabase transaction
A transaction comprises a unit of work performed within a database management system against a database, and treated in a coherent and reliable way independent of other transactions...
and thus be rolled back
Rollback (data management)
In database technologies, a rollback is an operation which returns the database to some previous state. Rollbacks are important for database integrity, because they mean that the database can be restored to a clean copy even after erroneous operations are performed...
. The typical usage is simply:
DROP objecttype objectname.For example, the command to drop a table named employees would be:
DROP TABLE employees;
The
DROP statement is distinct from the DELETEDelete (SQL)In the database structured query language , the DELETE statement removes one or more records from a table. A subset may be defined for deletion using a condition, otherwise all records are removed.-Usage:The DELETE statement follows the syntax:...
and TRUNCATETruncate (SQL)In SQL, the TRUNCATE TABLE statement is a Data Definition Language operation that marks the extents of a table for deallocation . The result of this operation quickly removes all data from a table, typically bypassing a number of integrity enforcing mechanisms...
statements, in that they do not remove the table itself. For example, a DELETE statement might delete some (or all) data from a table while leaving the table itself in the database, whereas a DROP statement would remove the entire table from the database.ALTER statements
Alter - To modify an existing database object.An
ALTER statement in SQLSQL
SQL is a programming language designed for managing data in relational database management systems ....
changes the properties of an object inside of a relational database management system
Relational database management system
A relational database management system is a database management system that is based on the relational model as introduced by E. F. Codd. Most popular databases currently in use are based on the relational database model....
(RDBMS). The types of objects that can be altered depends on which RDBMS is being used. The typical usage is:
ALTER objecttype objectname parameters.For example, the command to add (then remove) a column named bubbles for an existing table named sink would be:
ALTER TABLE sink ADD bubbles INTEGER;
ALTER TABLE sink DROP COLUMN bubbles;
Referential integrity statements
Finally, other kind of DDL sentence in SQL are the statements to define referential integrityReferential integrity
Referential integrity is a property of data which, when satisfied, requires every value of one attribute of a relation to exist as a value of another attribute in a different relation ....
relationships, usually implemented as primary key and foreign key
Foreign key
In the context of relational databases, a foreign key is a referential constraint between two tables.A foreign key is a field in a relational table that matches a candidate key of another table...
tags in some columns of the tables.
These two statements can be included inside a
CREATE TABLE or an ALTER TABLE sentence.Other languages
- XML Schema is an example of a DDL for XMLXMLExtensible Markup Language is a set of rules for encoding documents in machine-readable form. It is defined in the XML 1.0 Specification produced by the W3C, and several other related specifications, all gratis open standards....
. - Simple Declarative LanguageSimple Declarative LanguageThe Simple Declarative Language is a cross-platform declarative programming language used for defining basic data structures such as lists, maps, and trees of typed data in a compact, easy to read representation....