Editing Data definition language (section)

==Structured Query Language (SQL)==
Many data description languages use a declarative syntax to define columns and data types. Structured Query Language (SQL), however, 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 elements. These statements can be freely mixed with other SQL statements, making the DDL not a separate language.

===CREATE statement===
The ''create'' command is used to establish a new database, table, index, or [[stored procedure]].

The ''CREATE'' statement in [[SQL]] creates a component in a [[relational database management system]] (RDBMS).  In the SQL 1992 specification, the types of components that can be created are schemas, [[table (database)|tables]], [[View (database)|views]], domains, [[character set]]s, [[collation]]s, translations, and assertions.<ref name="SQL92" /> Many implementations extend the syntax to allow creation of additional elements, such as  [[Database index|indexes]] and user profiles. Some systems, such as [[PostgreSQL]] and [[Microsoft SQL Server|SQL Server]], allow ''CREATE'', and other DDL commands, inside a [[database transaction]] and thus they may be [[rollback (data management)|rolled back]].<ref>{{cite web |last1=Laudenschlager |first1=Douglas |last2=Milener |first2=Gene |last3=Guyer |first3=Craig |last4=Byham |first4=Rick |title=Transactions (Transact-SQL) |url=https://docs.microsoft.com/en-us/sql/t-sql/language-elements/transactions-transact-sql?view=sql-server-2017 |website=Microsoft Docs |publisher=Microsoft |access-date=12 November 2018}}</ref><ref>{{cite web |title=PostgreSQL Transactions |url=https://www.postgresql.org/docs/8.3/tutorial-transactions.html |website=PostgreSQL 8.3 Documentation |date=7 February 2013 |publisher=PostgreSQL |access-date=12 November 2018}}</ref>

====CREATE TABLE statement====
A commonly used ''CREATE'' command is the ''CREATE TABLE'' command. The typical usage is:

 CREATE TABLE ''[table name]'' ( ''[column definitions]'' ) ''[table parameters]''

The column definitions are:
*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]''
*[[relational database system|RDBMS]] specific functionality

An example statement to create a table named ''employees'' with a few columns is:
<syntaxhighlight lang="sql">
CREATE TABLE employees (
    id            INTEGER       PRIMARY KEY,
    first_name    VARCHAR(50)   not null,
    last_name     VARCHAR(75)   not null,
    mid_name      VARCHAR(50)   not null,
    dateofbirth   DATE          not null
);
</syntaxhighlight>

Some forms of ''CREATE TABLE DDL'' may incorporate DML ([[data manipulation language]])-like constructs, such as the ''CREATE TABLE AS SELECT'' (CTaS) syntax of SQL.<ref>
{{cite book
| last                  = Allen
| first                 = Grant
| others                = Mike Owens
| title                 = The Definitive Guide to SQLite
| url                   = https://books.google.com/books?id=WLinoJaOUCwC
| access-date            = 2012-10-02
| edition               = 2
| series                = Apresspod
| year                  = 2010
| publisher             = Apress
| isbn                  = 9781430232254
| pages                  = 90–91
| quote                 = The ''create table'' statement has a special syntax for creating tables from ''select'' statements. [...]: [...] ''create table foods2 as select * from foods;'' [...] Many other databases refer to this approach as ''CTaS'', which stands for Create Table as Select, and that phrase is not uncommon among SQLite users.
}}
</ref>

===DROP statement===
The ''DROP'' statement destroys an existing database, table, index, or view.

A ''DROP'' statement in [[SQL]] removes a component 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 [[table (database)|tables]], [[user (database)|users]], and [[database]]s. Some systems (such as [[PostgreSQL]]) allow DROP and other DDL commands to occur inside of a [[database transaction|transaction]] and thus be [[rollback (data management)|rolled back]].  The typical usage is simply:

 DROP ''objecttype'' ''objectname''.

For example, the command to drop a table named '''employees''' is:

<syntaxhighlight lang="sql">
DROP TABLE employees;
</syntaxhighlight>

The ''DROP'' statement is distinct from the ''[[Delete (SQL)|DELETE]]'' and ''[[Truncate (SQL)|TRUNCATE]]'' statements, in that ''DELETE'' and ''TRUNCATE'' 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 removes the entire table from the database.

===ALTER statement===
The ''ALTER'' statement modifies an existing database object.

An ''ALTER'' statement in [[SQL]] 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:

 ALTER ''objecttype'' ''objectname'' ''parameters''.

For example, the command to add (then remove) a column named '''bubbles''' for an existing table named '''sink''' is:

<syntaxhighlight lang="sql">
ALTER TABLE sink ADD bubbles INTEGER;
ALTER TABLE sink DROP COLUMN bubbles;
</syntaxhighlight>

===TRUNCATE statement===
The ''TRUNCATE'' statement is used to delete all data from a table. It's much faster than ''DELETE''.

<syntaxhighlight lang="sql">
TRUNCATE TABLE table_name;
</syntaxhighlight>

===Referential integrity statements===
Another type of DDL sentence in SQL is used to define [[referential integrity]] relationships, usually implemented as [[primary key]] and [[foreign key]] tags in some columns of the tables. These two statements can be included in a ''CREATE TABLE'' or an ''ALTER TABLE'' sentence;