Unit 4: SQL Data Definition Language (DDL)

Introduction to SQL Data Definition Language (DDL)

SQL (Structured Query Language) is a standard language for managing and manipulating relational databases. It is divided into several sub-languages, one of which is Data Definition Language (DDL).

DDL commands are used to define, modify, and delete the structure of database objects. These commands deal with the database schema itself, rather than the data within the tables.

Key Concept: DDL commands operate on the structure or schema of the database.

Why is DDL important?

• It allows database administrators and developers to design and organize the database.
• It defines how data is stored, what types of data are allowed, and how different pieces of data relate to each other.
• Changes made using DDL commands are auto-committed, meaning they are permanently saved and cannot be rolled back.

Datatypes in SQL

Datatypes specify the kind of data that a column can hold (e.g., numbers, text, dates). Choosing the correct datatype is crucial for data integrity, storage efficiency, and query performance.

Here are some common SQL datatypes:

Important Observation: The specific availability and behavior of datatypes can vary slightly across different SQL database systems (e.g., MySQL, PostgreSQL, SQL Server, Oracle).

Schema Definition

Definition: A database schema is a logical collection of database objects, such as tables, views, indexes, stored procedures, and more, that are owned by a single user or application. It defines the structure of the database.

When you design a database, you are essentially defining its schema. This involves:

• Identifying entities (things you want to store data about).
• Determining attributes for each entity (columns for tables).
• Specifying relationships between entities.
• Applying constraints to ensure data integrity.

DDL commands are the primary tools used to implement this schema definition in a database.

Example of conceptual schema components:

• Tables: The fundamental storage units (e.g., Customers, Products, Orders).
• Columns: Attributes of tables with specific datatypes (e.g., CustomerID INT, ProductName VARCHAR(255)).
• Constraints: Rules to maintain data integrity (e.g., PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, CHECK, DEFAULT).
• Indexes: Used to speed up data retrieval operations.
• Views: Virtual tables based on the result-set of an SQL query.

Basic Structure of SQL DDL Queries

DDL commands are straightforward. They typically involve the keyword for the operation (CREATE, ALTER, DROP, RENAME, TRUNCATE) followed by the type of object and its name, along with any necessary parameters.

CREATE Command

The CREATE command is used to create new database objects like databases, tables, views, or indexes.

Purpose: To construct new components within the database schema.

1. Creating a Database (if supported by your SQL system):

CREATE DATABASE database_name;

Example:

CREATE DATABASE CompanyDB;

2. Creating a Table:

CREATE TABLE table_name (
column1_name datatype [constraints],
column2_name datatype [constraints],
...
PRIMARY KEY (column_name) -- or composite primary key
);

Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    PhoneNumber VARCHAR(20) DEFAULT 'N/A',
    HireDate DATE,
    Salary DECIMAL(10, 2) CHECK (Salary > 0)
);

Exam-Oriented Notes:

• PRIMARY KEY: Uniquely identifies each record in a table; cannot contain NULL values.
• NOT NULL: Ensures a column cannot have a NULL value.
• UNIQUE: Ensures all values in a column are different. Can contain one NULL.
• DEFAULT: Sets a default value for a column when no value is specified.
• CHECK: Ensures that all values in a column satisfy a specific condition.
• FOREIGN KEY: Establishes a link between two tables, ensuring referential integrity. (e.g., FOREIGN KEY (DeptID) REFERENCES Departments(DeptID))

ALTER Command

The ALTER command is used to modify the structure of an existing database object.

Purpose: To change the definition of an existing schema object.

Common Uses with Tables:

• Adding a New Column:

ALTER TABLE table_name ADD column_name datatype [constraints];

Example: Add a 'DepartmentID' column.

ALTER TABLE Employees
ADD DepartmentID INT;

• Dropping an Existing Column:

ALTER TABLE table_name DROP COLUMN column_name;

Example: Drop the 'PhoneNumber' column.

ALTER TABLE Employees
DROP COLUMN PhoneNumber;

• Modifying a Column's Datatype or Constraints:

ALTER TABLE table_name ALTER COLUMN column_name datatype [constraints]; (SQL Server syntax)
ALTER TABLE table_name MODIFY column_name datatype [constraints]; (MySQL, Oracle syntax)

Example (MySQL/Oracle): Change 'Email' column length and add NOT NULL.

ALTER TABLE Employees
MODIFY Email VARCHAR(120) NOT NULL;

• Adding/Dropping Constraints:

ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_definition;
ALTER TABLE table_name DROP CONSTRAINT constraint_name;

Example: Add a Foreign Key constraint.

ALTER TABLE Employees
ADD CONSTRAINT FK_Department
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID);

Important Observation: Modifying existing columns with data can lead to data loss or conversion errors if the new datatype is incompatible with existing data.

DROP Command

The DROP command is used to delete existing database objects entirely from the database.

Purpose: To remove a schema object and all its associated data and definitions.

1. Dropping a Database:

DROP DATABASE database_name;

Example:

DROP DATABASE CompanyDB;

2. Dropping a Table:

DROP TABLE table_name;

Example:

DROP TABLE Employees;

Exam-Oriented Notes:

• DROP is a permanent and irreversible operation. Once an object is dropped, all its data and structure are gone.
• When dropping a table, related objects like indexes and triggers associated with that table are also dropped.
• If other tables have foreign key constraints referencing the table being dropped, the `DROP` command might fail or require additional clauses (like CASCADE in some systems) to delete dependent objects.

RENAME Command

The RENAME command is used to change the name of an existing database object.

Purpose: To change the identifier of a schema object without affecting its contents.

The syntax for renaming objects can vary between different database systems.

1. Renaming a Table (Example using standard SQL-like syntax):

ALTER TABLE old_table_name RENAME TO new_table_name; (PostgreSQL, Oracle)
RENAME TABLE old_table_name TO new_table_name; (MySQL)

Example (Standard SQL compliant):

ALTER TABLE Employees RENAME TO Staff;

2. Renaming a Column (Example using standard SQL-like syntax):

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name; (PostgreSQL, Oracle)
ALTER TABLE table_name CHANGE old_column_name new_column_name datatype; (MySQL - note datatype must be re-specified)

Example (Standard SQL compliant):

ALTER TABLE Staff RENAME COLUMN FirstName TO GivenName;

Important Observation: When renaming objects, ensure that any queries, views, or stored procedures that reference the old name are updated to use the new name to avoid breaking applications.

TRUNCATE Command

The TRUNCATE command is used to delete all rows from a table, but it keeps the table structure intact, including its columns, constraints, indexes, and privileges.

Purpose: To quickly remove all data from a table while retaining its definition.

TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE Staff;

Comparison: TRUNCATE vs. DELETE vs. DROP

Exam-Oriented Notes:

• TRUNCATE is faster and uses fewer system resources than DELETE for removing all rows because it deallocates the data pages rather than deleting row by row.
• Since TRUNCATE is a DDL command, it is implicitly committed and cannot be rolled back.
• It is commonly used when you want to clear out a table's data for a fresh start while keeping the table definition.