Databases: Data Definition Language (DDL)

From Wikibooks, open books for an open world
Jump to: navigation, search

UNIT 3 - ⇑ Databases ⇑

← DELETE Data definition language Security →


Data Definition Language (DDL) is a way to adjust the structure of a database. You might have created databases in the past using a GUI such as Access or even MySQL. DDL allows you to create databases from pure code including the ability to:

  • Create tables: CREATE TABLE
  • Change the structure of a table: ALTER
  • Delete tables: DROP

CREATE[edit]

You need to know what they all do (as listed above), though you only need to know how to implement the CREATE TABLE command. Let's look at how we could have made the crooks table above:

CREATE TABLE crooks
(
ID INTEGER PRIMARY KEY,
NAME VARCHAR(16),
GENDER VARCHAR(6),
DOB DATE,
TOWN VARCHAR(20),
NUMSCARS INTEGER
)


Questions

Write a DDL statement to create the table Customer(CustomerID, CustomerName, Address, Postcode, Email)

Answer :

CREATE TABLE customer (
CustomerID INTEGER PRIMARY KEY,
CustomerName VARCHAR(16),
Address VARCHAR(30),
Postcode VARCHAR(8),
Email VARCHAR(20)
)

ALTER[edit]

Ped(ro) = 2.8

DROP[edit]

Dropping a table is like dropping a baby, it's a very easy and common thing to do.


Let's look at how we could Drop the crooks table above:

DROP TABLE crooks

Setting Primary Keys[edit]

Setting Composite Keys[edit]

Setting Foreign Keys[edit]