Database Tutorial

Course Tutorial Site

Site Admin

2 – DDL Statements

without comments

The Data Definition Language (DDL) subsystem provides you with the ability to create, alter, drop, rename, and truncate structures in a database. Structures may be tables or other types of objects, where those other types of objects are the subject of product variation and often drivers of SQL dialect nuances. The supporting pages in this lesson support DDL commands, which you can see by clicking the drop down label above.

  • CREATE – Creates an object, which can be a table, view, or other structure supported by the DBMS implementation.
  • ALTER – Alters the structure of an object, which can be a table, view, or other structure supported by the DBMS implementation.
  • DROP – Drops a structure (e.g., a table, view, sequence, et cetera) from the database, which means that the definition of the table is removed from the database catalog (or metadata). The drop may be constrained when there are other objects that depend on an object. For example, in Oracle you append the CASCADE CONSTRAINTS clause to instruct the database to also remove any dependencies on the object before dropping a structure.
  • RENAME – Renames a structure, which can be a table, view, or subcomponent like a column name or data type of a table.
  • TRUNCATE – Truncate deletes data like the DELETE statement with a twist. It preserves the structure of the table but deletes the contents without logging the deletes for recovery. Implementations vary between products, and Oracle’s flashback technology does provide for recovery of truncated objects.
  • COMMENT – Lets you add a comment to a table or a column in a table. It is very handy for labeling foreign key columns that aren’t constrained by a database constraint because developers can then check the valid list of values. It is also very handy for letting you qualify the list of acceptable values imposed by a CHECK constraint.

Written by michaelmclaughlin

January 17th, 2018 at 1:29 am

Posted in