Data definition language

Not to be confused withData manipulation language.

Saving a ddl file in Oracle SQL Developer

In the context ofSQL,data definitionordata description language(DDL) is a syntax for creating and modifying database objects such as tables, indices, and users. DDL statements are similar to a computerprogramming languagefor definingdata structures,especiallydatabase schemas.Common examples of DDL statements includeCREATE,ALTER,andDROP.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 theCodasyldatabase model, where the schema of thedatabasewas written in alanguage syntaxdescribing therecords,fields,andsetsof the userdata model.Later it was used to refer to a subset ofStructured Query Language(SQL) for declaringtables,columns, data types andconstraints.SQL-92introduced a schema manipulation language and schema information tables to query schemas.These information tables were specified asSQL/SchematainSQL:2003.The term DDL is also used in a generic sense to refer to anyformal languagefor 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

Thecreatecommand is used to establish a new database, table, index, orstored procedure.

TheCREATEstatement inSQLcreates a component in arelational 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 asindexesand user profiles. Some systems, such asPostgreSQLandSQL Server,allowCREATE,and other DDL commands, inside adatabase transactionand thus they may berolled back.

CREATE TABLE statement

A commonly usedCREATEcommand is theCREATE TABLEcommand. 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 keydefinition:PRIMARY KEY([comma separated column list])
• Constraints:{CONSTRAINT}[constraint definition]
• RDBMSspecific functionality

An example statement to create a table namedemployeeswith a few columns is:

CREATETABLEemployees(
idINTEGERPRIMARYKEY,
first_nameVARCHAR(50)notnull,
last_nameVARCHAR(75)notnull,
mid_nameVARCHAR(50)notnull,
dateofbirthDATEnotnull
);

Some forms ofCREATE TABLE DDLmay incorporate DML (data manipulation language)-like constructs, such as theCREATE TABLE AS SELECT(CTaS) syntax of SQL.

DROP statement

TheDROPstatement destroys an existing database, table, index, or view.

ADROPstatement inSQLremoves a component from arelational database management system(RDBMS). The types of objects that can be dropped depends on which RDBMS is being used, but most support the dropping oftables,users,anddatabases.Some systems (such asPostgreSQL) allow DROP and other DDL commands to occur inside of atransactionand thus berolled back.The typical usage is simply:

DROPobjecttypeobjectname.

For example, the command to drop a table namedemployeesis:

DROPTABLEemployees;

TheDROPstatement is distinct from theDELETEandTRUNCATEstatements, in thatDELETEandTRUNCATEdo not remove the table itself. For example, aDELETEstatement might delete some (or all) data from a table while leaving the table itself in the database, whereas aDROPstatement removes the entire table from the database.

ALTER statement

TheALTERstatement modifies an existing database object.

AnALTERstatement inSQLchanges the properties of an object inside of arelational database management system(RDBMS). The types of objects that can be altered depends on which RDBMS is being used. The typical usage is:

ALTERobjecttypeobjectnameparameters.

For example, the command to add (then remove) a column namedbubblesfor an existing table namedsinkis:

ALTERTABLEsinkADDbubblesINTEGER;
ALTERTABLEsinkDROPCOLUMNbubbles;

TRUNCATE statement

TheTRUNCATEstatement is used to delete all data from a table. It's much faster thanDELETE.

TRUNCATETABLEtable_name;

Referential integrity statements

Another type of DDL sentence in SQL is used to definereferential integrityrelationships, usually implemented asprimary keyandforeign keytags in some columns of the tables. These two statements can be included in aCREATE TABLEor anALTER TABLEsentence;

Other languages

• XML Schemais an example of a DDL forXML.
• JSON Schemais an example of a DDL forJSON.
• DFDL schemais 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)

External links

• Oracle ALTER TABLE MODIFY columnArchived2021-04-21 at theWayback Machine
• DDL Commands In OracleArchived2021-04-21 at theWayback Machine