Database Definition Language

Introduction

What is a Database Definition Language?

This is the programming language used to construct and alter the design of tables contained within a database. The most common form of DDL is Structured Query Language(SQL).

This language is not used to directly alter the data stored within a table, rather it is used to construct the framework within which data is contained – this type of data that is used to define other data is known as metadata – data about data.

 

CREATE DATABASE

Creating a Database – CREATE DATABASE

The syntax to create a database in SQL is simple:

CREATE DATABASE databasename; e.g. CREATE DATABASE library.db

To do the same in SQLite then you would use the shell command

sqlite3 library.db  <-  if library.db already exists it will open the database, otherwise it will create it.

 

CREATE TABLE

Creating a Table – CREATE TABLE

Example 1 – Simple Non Relational Table

CREATE TABLE users(firstname varchar(255), lastname varchar(255), age integer, dateofbirth date);

Example 2 – Create a table with a primary key

CREATE TABLE Orders (OrderID integer NOT NULL PRIMARY KEY, OrderNumber integer NOT NULL, CustomerID integer FOREIGN KEY REFERENCES Customers(CustomerID));

 

SQL Data Types

  • Char – Fixed Length Text
  • Varchar(255) – Variable Length (For SQLite use – Text)
  • Integer – Store a whole number
  • Boolean – Stores 1 or 0 for Boolean
  • Real – Floating point number
  • Date / Time – (SQLite has no date  or time data type so use Text / Float / Integer and use something like Epoch Time)

ALTER TABLE

Modifying a table – ALTER TABLE

If you want to alter the structure of a table (change column names / data types) then you can use the ALER table command.

Examples:

ALTER TABLE username ADD COLUMN height real;

ALTER TABLE username MODIFY COLUMN height integer

ALTER TABLE username DROP COLUMN email;

 

DROP TABLE

Deleting a table – DROP TABLE

Deleting a table is easily with the drop table command.

DROP TABLE users;

Warning – this will drop the table straight away without warning – A  costly mistake that many an unwary junior developer has made..