Section 11: Data Definition Language

Implementing Databases

This section introduces Data Definition Language (DDL) statements, which are used to define, modify, and delete database objects like tables and databases themselves. You will learn about creating tables with different constraints and understand referential integrity.

SQL examples in this section will be using PostgreSQL syntax unless otherwise noted.

§1 The CREATE DATABASE Statement #

The CREATE DATABASE statement is used to create a new database. In certain SQL environments such as SQLite, this command might not be explicitly used or might be handled by the connection process. But in multi-database systems like PostgreSQL or MySQL, CREATE DATABASE is an important command.

In DBMS which support creating databases, creating a new database is as simple as:

CREATE DATABASE database_name;

§2 The CREATE TABLE Statement #

The CREATE TABLE statement is used to define the structure of a new table in the database. This includes specifying column names, their data types, and various constraints that govern the data allowed in those columns.

Syntax:

CREATE TABLE table_name (
  column1_name data_type [constraint],
  column2_name data_type [constraint],
  ...
  [table_constraint]
);

Example:

CREATE TABLE Employees (
  employee_id SERIAL PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE,
  hire_date DATE DEFAULT CURRENT_DATE,
  salary DECIMAL(10, 2) CHECK (salary >= 0)
);

Each table always contains a set of typed columns, and optional constraints.

§2.1 Common Data Types #

Choosing the correct data type for each column is important for efficient storage and data integrity. While technically everything could be stored as a string, storing data as an appropriate type is both more space efficient and allows for meaningful calculations/comparisons. This was originally covered way back in section 2, here is a refresher:

§2.2 Column Constraints #

Constraints are rules enforced on data columns to limit the type of data that can be inserted or updated in a table. This ensures the accuracy and reliability of the data. Some common constraints include:

Example:

CREATE TABLE Employees (
  employee_id SERIAL PRIMARY KEY, --SERIAL auto-populates with integer if not defined
  first_name VARCHAR(50) NOT NULL, --value cannot be NULL
  last_name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE, --emails must be unique (one row is allowed to be NULL)
  hire_date DATE DEFAULT CURRENT_DATE, --use current date if no date provided
  salary DECIMAL(10, 2) CHECK (salary >= 0) --numbers must not be negative
);

Example:
Create a Products table with various column definitions and constraints.

CREATE TABLE Products (
  product_id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE,
  unit_price DECIMAL(10, 2) DEFAULT 0.00 CHECK (unit_price >= 0),
  stock_quantity INT DEFAULT 0 CHECK (stock_quantity >= 0),
  category_id INT,
  FOREIGN KEY (category_id) REFERENCES Categories(category_id)
);

CREATE TABLE Categories (
  category_id SERIAL PRIMARY KEY,
  category_name VARCHAR(50) NOT NULL UNIQUE
);

--Insert some data to demonstrate constraints
INSERT INTO Categories (category_name) VALUES ('Electronics'), ('Books');

INSERT INTO Products (name, unit_price, stock_quantity, category_id) VALUES
  ('Laptop', 1200.00, 50, 1),
  ('SQL Book', 45.50, 100, 2);

§3 The ALTER TABLE Statement #

The ALTER TABLE statement is used to modify the structure of an existing table. This includes adding, dropping, or modifying columns and constraints.

§3.1 Adding a Column #

Syntax:

ALTER TABLE table_name ADD COLUMN column_name data_type [constraint];

ALTER TABLE Example (Add Column):
Add an is_active column to the Products table.

CREATE TABLE Products (
  product_id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE
);

INSERT INTO Products (name) VALUES ('Laptop'), ('Mouse');

--add a new table column (use DEFAULT or all existing rows will be NULL)
ALTER TABLE Products
  ADD COLUMN is_active BOOLEAN DEFAULT TRUE;
product_idnameis_active
1Laptop1
2Mouse1

§3.2 Dropping(Deleting) a Column #

Syntax:

ALTER TABLE table_name DROP COLUMN column_name;

ALTER TABLE Example (Drop Column):
Remove the is_active column from the Products table.

CREATE TABLE Products (
  product_id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE,
  is_active BOOLEAN DEFAULT TRUE
);
INSERT INTO Products (name) VALUES ('Laptop'), ('Mouse');

ALTER TABLE Products DROP COLUMN is_active;

§3.3 Modifying Column Data Types or Constraints #

SQL allows you to modify existing columns and constraints at any time. The exact syntax for modifying column data types or constraints can vary significantly between different SQL database systems.

General Syntax:

ALTER TABLE table_name
  ALTER COLUMN column_name SET DATA TYPE new_data_type; --PostgreSQL
ALTER TABLE table_name
  MODIFY COLUMN column_name new_data_type; --MySQL

ALTER TABLE Example (Modify Column):

CREATE TABLE Products (
  product_id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description VARCHAR(255)
);

--increase string length
ALTER TABLE Products ALTER COLUMN name TYPE VARCHAR(200);

When you change a column’s data type using ALTER TABLE, the database will make a best-effort to convert existing data in that column to the new type.

Simple conversions such as INT to VARCHAR(200) will always succeed as long as the VARCHAR length is sufficient to hold the string, values like 123 will become '123'.

Some conversions are not as straightforward, VARCHAR(200) to INT may fail depending on the data being converted. Values such as '1' or '123' will be simply converted to number representations. Incompatible values such as 'abc' or '12.5' will not succeed in conversion, the behavior will depend on the database being used. Some databases will reject the ALTER TABLE statement, others will default all invalid values to NULL or 0. It is important to validate or cleanup all data when making risky type conversions.

Changing data length, such as VARCHAR(200) to VARCHAR(100) will also behave differently depending on the database being used. PostgreSQL will reject the attempt, MySQL will simply truncate all strings that are too long.

§3.4 Adding/Dropping Constraints #

Constraints can also be added or dropped after table creation.

Syntax (Add Constraint):

ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_definition;

Note: Every constraint must have a unique name per table.

Syntax (Drop Constraint):

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

ALTER TABLE Examples:

CREATE TABLE Products (
  product_id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description VARCHAR(255)
);

INSERT INTO Products (name) VALUES ('Laptop'), ('Mouse');

--Altering just the data type
ALTER TABLE Products
  ALTER COLUMN name TYPE VARCHAR(200);

--Altering just a constraint (adding NOT NULL to an existing column)
--Note: If 'description' already exists and has NULLs, this would fail.
ALTER TABLE Products
  ALTER COLUMN description SET NOT NULL;

--Altering data type and constraint at same time
--This example changes 'name' to VARCHAR(250) and adds a UNIQUE constraint
--Note: If there are duplicate values in 'name', this will fail.
ALTER TABLE Products
  ALTER COLUMN name TYPE VARCHAR(250),
  ADD CONSTRAINT unique_product_name UNIQUE (name);

--Dropping Constraint: remove the 'unique_product_name' constraint from the table
ALTER TABLE Products DROP CONSTRAINT unique_product_name;

§4 The DROP TABLE Statement #

The DROP TABLE statement is used to remove an entire table from the database. This action is irreversible and will delete all data, indexes, and constraints associated with the table. Be extremely careful with the DROP TABLE statement

Syntax:

DROP TABLE table_name;

§5 The DROP DATABASE Statement #

The DROP DATABASE statement is used to delete an entire database. This is also irreversible (without a backup) and will delete all tables, data, metadata, and other objects within that database.

Syntax:

DROP DATABASE database_name;

§6 Referential Integrity #

Referential integrity is a database concept that ensures relationships between tables remain consistent. It is primarily enforced using Foreign Keys. A foreign key in one table points to a primary key in another table, establishing a link.

When referential integrity is enforced:

Foreign Key Example:

CREATE TABLE Departments (
  department_id SERIAL PRIMARY KEY,
  department_name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE Employees (
  employee_id SERIAL PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  department_id INTEGER
  --The FOREIGN KEY definition here is commented out because it's added below with ALTER TABLE
  -- FOREIGN KEY (department_id) REFERENCES Departments(department_id)
);

--Alter the Employees table to add a foreign key constraint after creation
ALTER TABLE Employees
  ADD CONSTRAINT fk_department_id  --Give the FK constraint a name
  FOREIGN KEY (department_id)  --The column in the Employees table
  REFERENCES Departments(department_id)  --The table and column it references
  ON DELETE SET NULL;  --Action to take if referenced department is deleted (next section)

§6.1 ON DELETE and ON UPDATE Actions #

When defining a FOREIGN KEY constraint, you can specify actions to be taken when the referenced primary key in the parent table is deleted or updated.

Referential Integrity Example (ON DELETE CASCADE):
If a category is deleted, all products belonging to that category are also deleted.

CREATE TABLE Categories (
  category_id SERIAL PRIMARY KEY,
  category_name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE Products (
  product_id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  category_id INT,
  --this constraint causes products matching a deleted category to also be deleted
  FOREIGN KEY (category_id) REFERENCES Categories(category_id) ON DELETE CASCADE
);

INSERT INTO Categories (category_name) VALUES ('Electronics'), ('Books');
INSERT INTO Products (name, category_id) VALUES
  ('Laptop', 1),
  ('Mouse', 1),
  ('SQL Book', 2);

SELECT 'Before Delete:';
SELECT P.name, C.category_name FROM Products P JOIN Categories C ON P.category_id = C.category_id;

DELETE FROM Categories WHERE category_name = 'Electronics';

SELECT 'After Delete:';
SELECT P.name, C.category_name FROM Products P JOIN Categories C ON P.category_id = C.category_id;

Before Delete:

namecategory_name
LaptopElectronics
MouseElectronics
SQL BookBooks

After Delete:

namecategory_name
SQL BookBooks

Referential Integrity Example (ON DELETE SET NULL):
If a category is deleted, the category_id for associated products is set to NULL.

CREATE TABLE Categories (
  category_id SERIAL PRIMARY KEY,
  category_name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE Products (
  product_id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  category_id INT, --Must be nullable for SET NULL to work
  FOREIGN KEY (category_id) REFERENCES Categories(category_id) ON DELETE SET NULL
);

INSERT INTO Categories (category_name) VALUES ('Electronics'), ('Books');
INSERT INTO Products (name, category_id) VALUES
  ('Laptop', 1),
  ('Mouse', 1),
  ('SQL Book', 2);

SELECT 'Before Delete:';
SELECT P.name, P.category_id, C.category_name FROM Products P LEFT JOIN Categories C ON P.category_id = C.category_id;

DELETE FROM Categories WHERE category_name = 'Electronics';

SELECT 'After Delete:';
SELECT P.name, P.category_id, C.category_name FROM Products P LEFT JOIN Categories C ON P.category_id = C.category_id;

Before Delete:

namecategory_idcategory_name
Laptop1Electronics
Mouse1Electronics
SQL Book2Books

After Delete:

namecategory_idcategory_name
Laptop1
Mouse1
SQL Book2Books

§7 More Resources #

CREATE TABLE

ALTER TABLE

DROP TABLE

Referential Integrity

§8 Questions #

Please number each response to directly correspond with the question. Provide clear, concise answers that fully address each question without unnecessary elaboration or essay-style writing.

  1. What is Data Definition Language (DDL) used for?
  1. Which SQL statement is used to create a new database?
  1. Explain the difference between NOT NULL and UNIQUE constraints.
  1. What is the primary function of a PRIMARY KEY, and what two constraints does it combine?
  1. How does a FOREIGN KEY enforce referential integrity?
  1. Which SQL statement is used to modify the structure of an existing table?
  1. Write the SQL syntax to add a new column named bio of type VARCHAR(255) with a default value of 'none' to an existing table called Users.
  1. Which constraint ensures that a numeric column’s values fall within a specified range (e.g., quantity must be greater than 0)? (provide a SQL snippet).
  1. Provide an example SQL statement that is used to add a new column to an existing table.
  1. What happens when you use the DROP TABLE statement? Is this action reversible?
  1. Which FOREIGN KEY action automatically deletes child rows when the parent row is deleted?
  1. If a foreign key column is defined with ON DELETE SET NULL, what must be true about that foreign key column in the child table?
  1. What happens if you try to delete a row from a parent table when there are related rows in a child table, and the foreign key constraint uses ON DELETE RESTRICT?
  1. Write a CREATE TABLE statement for a table named Employees with the following columns and constraints using PostgreSQL SQL:
    • employee_id: Integer, primary key, auto-incrementing.
    • first_name: Variable-length string (max 50 chars), cannot be null.
    • last_name: Variable-length string (max 50 chars), cannot be null.
    • email: Variable-length string (max 100 chars), must be unique.
    • hire_date: Date, defaults to the current date.
    • salary: Decimal (8 total digits, 2 after decimal), must be greater than or equal to 0.
  1. Write an ALTER TABLE statement that adds a new column department_id (integer) to the Employees table created in the previous question.
  1. Write a SQL statement that adds a foreign key constraint linking department_id from the Employees table to a Departments table’s department_id (assume Departments table exists with department_id as its primary key), the department_id in the Employees table should become null if the department_id in Departments is deleted.
  1. Write a SQL statement to remove the email column from the Employees table.