Alter (SQL)

In the context of SQL, data definition or data description language (DDL) is a syntax for creating and modifying database objects such as tables, indices, and users. DDL statements are similar to a computer programming language for defining data structures, especially database schemas. Common examples of DDL statements include CREATE, ALTER, and DROP. If you see a .ddl file, that means the file contains a statement to create a table. Oracle SQL Developer contains the ability to export from an ERD generated with Data Modeler to either a .sql file or a .ddl file.

History

The concept of the data definition language and its name was first introduced in relation to the Codasyl 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 declaring tables, columns, data types 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.

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, tables, views, domains, character sets, collations, translations, and assertions. Many implementations extend the syntax to allow creation of additional elements, such as indexes and user profiles. Some systems, such as PostgreSQL and SQL Server, allow ''CREATE'', and other DDL commands, inside a database transaction and thus they may be rolled back.

CREATE TABLE statement

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

CREATE TABLE '' able name' ( '' olumn definitions' ) '' able parameters'

The column definitions are:
*A comma-separated list consisting of any of the following
*Column definition: '' olumn name' '' ata type' ' '
* Primary key definition: ''PRIMARY KEY'' ( '' omma separated column list' )
*Constraints: ' '' onstraint definition'
* RDBMS specific functionality

An example statement to create a table named ''employees'' with a few columns is:

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
);

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.

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 tables, users, and databases. Some systems (such as PostgreSQL) allow DROP and other DDL commands to occur inside of a transaction and thus be rolled back. The typical usage is simply:

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

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

DROP TABLE employees;

The ''DROP'' statement is distinct from the '' DELETE'' and '' 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:

ALTER TABLE sink ADD bubbles INTEGER;
ALTER TABLE sink DROP COLUMN bubbles;

TRUNCATE statement

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

TRUNCATE TABLE table_name;

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;

Other languages

* XML Schema is an example of a DDL for XML.
* JSON Schema is an example of a DDL for JSON.
* DFDL schema is an example of a DDL that can describe many text and binary formats.

See also

* Data control language
* Data manipulation language
* Data query language
* Select (SQL)
* Insert (SQL)
* Update (SQL)
* Delete (SQL)
* Truncate (SQL)

References

External links

Oracle ALTER TABLE MODIFY column

{{Database

Articles with example SQL code
Data modeling languages
SQL