 | This articleneeds additional citations forverification. Please helpimprove this article byadding citations to reliable sources. Unsourced material may be challenged and removed. Find sources: "Data definition language" – news ·newspapers ·books ·scholar ·JSTOR(December 2012) (Learn how and when to remove this message) |
In the context ofSQL,data definition ordata 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 language for 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.
The concept of the data definition language and its name was first introduced in relation to theCodasyl database model, where the schema of thedatabase was written in alanguage syntax describing therecords,fields, andsets of the userdata model.[1] Later it was used to refer to a subset ofStructured Query Language (SQL) for declaringtables, columns, data types andconstraints.SQL-92 introduced a schema manipulation language and schema information tables to query schemas.[2] These information tables were specified asSQL/Schemata inSQL:2003. The term DDL is also used in a generic sense to refer to anyformal language for describing data or information structures.
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.
Thecreate command is used to establish a new database, table, index, orstored procedure.
TheCREATE statement inSQL creates 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.[2] Many implementations extend the syntax to allow creation of additional elements, such asindexes and user profiles. Some systems, such asPostgreSQL andSQL Server, allowCREATE, and other DDL commands, inside adatabase transaction and thus they may berolled back.[3][4]
A commonly usedCREATE command is theCREATE TABLE command. The typical usage is:
CREATE TABLE[table name] ([column definitions] )[table parameters]
The column definitions are:
An example statement to create a table namedemployees with a few columns is:
CREATETABLEemployees(idINTEGERPRIMARYKEY,first_nameVARCHAR(50)notnull,last_nameVARCHAR(75)notnull,mid_nameVARCHAR(50)notnull,dateofbirthDATEnotnull);
Some forms ofCREATE TABLE DDL may incorporate DML (data manipulation language)-like constructs, such as theCREATE TABLE AS SELECT (CTaS) syntax of SQL.[5]
TheDROP statement destroys an existing database, table, index, or view.
ADROP statement inSQL removes 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 atransaction and thus berolled back. The typical usage is simply:
DROPobjecttypeobjectname.
For example, the command to drop a table namedemployees is:
DROPTABLEemployees;
TheDROP statement is distinct from theDELETE andTRUNCATE statements, in thatDELETE andTRUNCATE do not remove the table itself. For example, aDELETE statement might delete some (or all) data from a table while leaving the table itself in the database, whereas aDROP statement removes the entire table from the database.
TheALTER statement modifies an existing database object.
AnALTER statement inSQL changes 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 namedbubbles for an existing table namedsink is:
ALTERTABLEsinkADDbubblesINTEGER;ALTERTABLEsinkDROPCOLUMNbubbles;
TheTRUNCATE statement is used to delete all data from a table. It's much faster thanDELETE.
TRUNCATETABLEtable_name;
Another type of DDL sentence in SQL is used to definereferential integrity relationships, usually implemented asprimary key andforeign key tags in some columns of the tables. These two statements can be included in aCREATE TABLE or anALTER TABLE sentence;
Thecreate table statement has a special syntax for creating tables fromselect statements. [...]: [...]create table foods2 as select * from foods; [...] Many other databases refer to this approach asCTaS, which stands for Create Table as Select, and that phrase is not uncommon among SQLite users.
