Fundamentals of databases: Data definition language

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

PAPER 2 - ⇑ Fundamentals of databases ⇑

← DELETE Data definition language Client server databases →


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

To create a table the user needs to define the name of the table and each of the attributes including the data type and length.

CREATE TABLE Customer(CustomerID varchar(5), CustomerName varchar(255), CustomerAddress varchar(255), PRIMARY KEY (CustomerID));

Examples of supported data types (depending on the database):

Data type Description
Character (n) character string with fixed length (n)
Varchar (n) character string variable length with maximum field length (n)
Boolean True or False
Int short for integer and is a whole number
Decimal (p,s) decimal number with number of digits before and after the decimal point
Real any number up to 7 decimal places
Date in the format day, month, year
Time in the format hour, mintues, seconds

CREATE[edit]

You need to know what they all do (as listed above), though you only need to know how to use 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]

An ALTER statement in SQL changes the properties of a table in a relational database without the need to access the table manually.

ALTER TABLE crooks ADD convictions INTEGER
ALTER TABLE crooks DROP COLUMN convictions

DROP[edit]

Dropping a table is like dropping a nuclear bomb. It is irreversible and generally not recommendedǃ.

DROP TABLE crooks

By running this line of code, the table "crooks" will be removed from the database with no chance of it being recovered unless backups have been previously made.

Setting Primary Keys[edit]

Primary keys can be set after table creation via the alter statement.

ALTER TABLE Persons
ADD PRIMARY KEY (id)

Primary keys can also be set during table creation

CREATE TABLE users
(
user_id int NOT NULL,
username varchar(255) NOT NULL,
password varchar(255) NOT NULL,
Address varchar(255),
PRIMARY KEY (user_id)
)

Setting Composite Keys[edit]

To set a primary key made up of two columns during table creation you could do something such as this

CREATE TABLE users
(
user_id int NOT NULL,
username varchar(255) NOT NULL,
password varchar(255) NOT NULL,
Address varchar(255),
CONSTRAINT pk_UserId PRIMARY KEY (user_id,username)
)

Where the constraint name would be UserId and the table's primary key would be made up of the user_id and the username columns.

This could also be done after table creation:

ALTER TABLE users
ADD CONSTRAINT pk_UserID PRIMARY KEY (user_id,username)

Setting Foreign Keys[edit]