Data definition language: Difference between revisions

Revision as of 17:32, 20 March 2019

Latest revision as of 19:09, 27 November 2024

(45 intermediate revisions by 34 users not shown)

Line 1:

{{short description|Syntax for defining data structures}}

{{Distinguish|Data manipulation language}}

{{Refimprove|date=December 2012}}

{{Multiple issues|

{{Refimprove|date=December 2012}}

A '''data definition ''' or '''data description language''' ('''DDL''') is a syntax similar to a computer [[programming language]] for defining [[data structure]]s, especially [[database schema]]s. DDL statements create, modify, and remove database objects such as tables, indexes, and users. Common DDL statements are CREATE, ALTER, and DROP

{{Cleanup|reason=the article focuses almost entirely on SQL.|date=June 2020}}

}}

[[File:UY3OsG1vuT-saving-a-ddl-file-in-Oracle-Developer.png|alt=Saving a ddl file in Oracle SQL Developer|thumb|Saving a ddl file in Oracle SQL Developer]]

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 structure]]s, especially [[database schema]]s. Common examples of DDL statements include <code>CREATE</code>, <code>ALTER</code>, and <code>DROP</code>. 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 [[Syntax (programming languages)|language syntax]] describing the [[Record (computer science)|records]], [[Field (computer science)|fields]], and [[Set (abstract data type)|sets]] of the user [[data model]].<ref>{{cite book|last=Olle|first=T. William|title=The Codasyl Approach to Data Base Management|year=1978|publisher=Wiley|isbn=0-471-99579-7|url-access=registration|url=https://archive.org/details/codasylapproacht00olle}}</ref> Later it was used to refer to a subset of [[Structured Query Language]] (SQL) for declaring [[Table (database)|tables]], columns, data types and [[Integrity constraints|constraints]]. [[SQL-92]] introduced a schema manipulation language and schema information tables to query schemas.<ref name="SQL92">{{cite web |title=Information Technology - Database Language SQL |url=http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt |website=SQL92 |publisher=Carnegie Mellon |accessdateaccess-date=12 November 2018}}</ref>. 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 queryQuery languageLanguage (SQL)==

Many data description languages use a declarative syntax to define columns and data types. Structured queryQuery languageLanguage (e.g., 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 |accessdateaccess-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 |accessdateaccess-date=12 November 2018}}</ref>

====CREATE TABLE statement====

Line 35 ⟶ 38:

first_name VARCHAR(50) not null,

last_name VARCHAR(75) not null,

fname 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'' (CTASCTaS) syntax of SQL.<ref>

{{cite book

| last = Allen

Line 47 ⟶ 50:

| title = The Definitive Guide to SQLite

| url = https://books.google.com/books?id=WLinoJaOUCwC

| accessdateaccess-date = 2012-10-02

| edition = 2

| series = Apresspod

Line 54 ⟶ 57:

| 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 ''CTASCTaS'', which stands for Create Table Asas Select, and that phrase is not uncommon among SQLite users.

}}

</ref>

Line 67 ⟶ 70:

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

<syntaxhighlight lang="textsql">

DROP TABLE employees;

</syntaxhighlight>

Line 82 ⟶ 85:

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

<syntaxhighlight lang="textsql">

ALTER TABLE sink ADD bubbles INTEGER;

ALTER TABLE sink DROP COLUMN bubbles;

Line 90 ⟶ 93:

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

<syntaxhighlight lang="textsql">

TRUNCATE TABLE table_name;

</syntaxhighlight>

Line 99 ⟶ 102:

==Other languages==

* [[XML Schema (W3C)|XML Schema]] is an example of a DDL for [[XML]].

* [[JSON#Metadata_and_schema|JSON Schema]] is an example of a DDL for [[JSON]].

* [[Data_Format_Description_Language|DFDL schema]] is an example of a DDL that can describe many text and binary formats.

==See also==

Line 114 ⟶ 119:

==External links==

* [https://oracletutorial.net/alter-table-modify-column-oracle.html Oracle ALTER TABLE MODIFY column] {{Webarchive|url=https://web.archive.org/web/20210421200756/https://oracletutorial.net/alter-table-modify-column-oracle.html |date=2021-04-21 }}

* [https://oracletutorial.net/dml-ddl-commands-in-oracle.html#ddl-commands-in-oracle DDL Commands In Oracle] {{Webarchive|url=https://web.archive.org/web/20210421203338/https://oracletutorial.net/dml-ddl-commands-in-oracle.html#ddl-commands-in-oracle |date=2021-04-21 }}

{{Database}}

{{databases}}

[[Category:Articles with example SQL code]]