World Library  
Flag as Inappropriate
Email this Article

Data definition language

Article Id: WHEBN0000452018
Reproduction Date:

Title: Data definition language  
Author: World Heritage Encyclopedia
Language: English
Subject: SQL, Data manipulation language, Database, Database design, PL/SQL
Collection:
Publisher: World Heritage Encyclopedia
Publication
Date:
 

Data definition language

A data definition language or data description language (DDL) is a syntax similar to a computer programming language for defining data structures, especially database schemas.

History

The data definition language concept and 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.[1] Later it was used to refer to a subset of Structured Query Language (SQL) for creating tables 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.

SQL

Many data description languages use a declarative syntax to define fields and data types. 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 objects. These statements can be freely mixed with other SQL statements, so the DDL is not truly a separate language.

CREATE statements

Create - To make a new database, table, index, or stored procedure.

A CREATE statement in SQL creates an object in a relational database management system (RDBMS). In the SQL 1992 specification, the types of objects 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 objects, such as indexes and user profiles. Some systems (such as PostgreSQL) allow CREATE, and other DDL commands, inside a 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 [table name] ( [column definitions] ) [table parameters].

column definitions: 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]
  • RDBMS specific functionality

For example, the command to create a table named employees with a few sample columns would be:

CREATE TABLE employees (
    id            INTEGER      PRIMARY KEY,
    first_name    VARCHAR(50) not null,
    last_name     VARCHAR(75) not null,
    fname         VARCHAR(50) not null,
    dateofbirth   DATE         null
);

Note that some forms of CREATE TABLE DDL may incorporate DML (data manipulation language)-like constructs as well, such as the CREATE TABLE AS SELECT (CTAS) syntax of SQL.[2]

DROP statements

Drop - To destroy an existing database, table, index, or view.

A DROP statement in SQL removes an object 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 would be:

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 would remove the entire table from the database.

ALTER statements

Alter - To modify 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 would be:

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

rename statement

Rename - to rename the table. for example

rename table old_name to new_name;

Referential integrity statements

Finally, another kind of DDL sentence in SQL is one 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 inside a CREATE TABLE or an ALTER TABLE sentence.

Other languages

See also

References

  1. ^ Olle, T. William (1978). The Codasyl Approach to Data Base Management. Wiley.  
  2. ^ Allen, Grant (2010). The Definitive Guide to SQLite. Apresspod. Mike Owens (2 ed.). Apress. p. 368.  

External links

  • How to change data type of a column MS SQL - How to change data type of a column
This article was sourced from Creative Commons Attribution-ShareAlike License; additional terms may apply. World Heritage Encyclopedia content is assembled from numerous content providers, Open Access Publishing, and in compliance with The Fair Access to Science and Technology Research Act (FASTR), Wikimedia Foundation, Inc., Public Library of Science, The Encyclopedia of Life, Open Book Publishers (OBP), PubMed, U.S. National Library of Medicine, National Center for Biotechnology Information, U.S. National Library of Medicine, National Institutes of Health (NIH), U.S. Department of Health & Human Services, and USA.gov, which sources content from all federal, state, local, tribal, and territorial government publication portals (.gov, .mil, .edu). Funding for USA.gov and content contributors is made possible from the U.S. Congress, E-Government Act of 2002.
 
Crowd sourced content that is contributed to World Heritage Encyclopedia is peer reviewed and edited by our editorial staff to ensure quality scholarly research articles.
 
By using this site, you agree to the Terms of Use and Privacy Policy. World Heritage Encyclopedia™ is a registered trademark of the World Public Library Association, a non-profit organization.
 


Copyright © World Library Foundation. All rights reserved. eBooks from Project Gutenberg are sponsored by the World Library Foundation,
a 501c(4) Member's Support Non-Profit Organization, and is NOT affiliated with any governmental agency or department.