DDL for Table Management

The Language of Structure: DDL

SQL commands are often grouped into categories based on their function. The queries you've written so far belong to the Data Query Language (DQL). Today, we focus on Data Definition Language (DDL).

DDL commands don't interact with the data inside the tables; they define, modify, or destroy the tables themselves and other related objects. To get a formal definition, please read the short section on DDL from the provided resource.

1. CREATE TABLE: Building the Blueprint

The CREATE TABLE statement is the starting point. It defines a new table, its name, and the columns it will contain. Each column is defined with a name, a data type (e.g., INT, VARCHAR, DATE), and optional constraints (e.g., NOT NULL).

Let's watch a practical demonstration of how to create tables. This video walks through creating two related tables, customers and vehicles.

Let's recap the core syntax from the video:

CREATE TABLE table_name (
    column1_name data_type [constraints],
    column2_name data_type [constraints],
    ...
    columnN_name data_type [constraints]
);

For example, to create a simple Products table, the SQL might look like this:

CREATE TABLE Products (
    product_id INT IDENTITY, -- The DBMS will auto-generate this number
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2), -- A number with up to 10 digits, 2 of which are after the decimal
    launch_date DATE
);

We'll cover data types and constraints like PRIMARY KEY in more detail in our next lesson. For now, focus on the overall structure of the CREATE TABLE command.

2. DROP TABLE: Demolishing the Structure

The DROP TABLE command is the counterpart to CREATE TABLE. It is simple, powerful, and irreversible. It completely removes a table, including its structure, all the data it contained, and any associated indexes or constraints.

The syntax is as simple as it gets:

DROP TABLE table_name;

A very useful variation, especially during development, is DROP TABLE IF EXISTS. You saw this used in the first video by Michael Fudge. This prevents an error if you try to drop a table that has already been deleted.

DROP TABLE IF EXISTS Products;

This command is destructive, so in a production environment, it is used with extreme caution.

CREATE TABLE Employees (
    employee_id INT IDENTITY,
    full_name VARCHAR(100),
    hire_date DATE
);

3. ALTER TABLE: Modifying an Existing Blueprint

Requirements change. You might need to add a new column, remove one you no longer need, or change a column's data type. Dropping and recreating the table would cause you to lose all your data. This is where ALTER TABLE comes in. It allows you to modify a table's structure without destroying it.

Let's see the most common uses of ALTER TABLE.

Here's a summary of the syntax:

• To add a new column:

  ALTER TABLE Employees ADD COLUMN salary DECIMAL(10, 2);
  

• To remove an existing column:

  ALTER TABLE Employees DROP COLUMN some_old_column;
  

• To modify a column's data type:

  ALTER TABLE Employees ALTER COLUMN full_name VARCHAR(150);
  

How ALTER TABLE Works Internally

Given your interest in the low-level "how," it's useful to know what the database is often doing behind the scenes when you run ALTER TABLE. It's not just a simple metadata change.

As the article explains, for many types of changes, the database engine effectively:

1. Creates a new, empty table with the desired new structure.
2. Copies all the data row by row from the old table to the new one.
3. Drops the old table.
4. Renames the new table to the original name.

This process ensures data integrity but can be slow and require significant disk space for large tables. This is a critical consideration in your future work, as running an ALTER TABLE on a massive production table can lock it and impact application performance.

Conclusion

In this lesson, you've learned the fundamental DDL commands for managing table structures. You are no longer just a consumer of data; you are now equipped to define the very containers that hold it.

Key Takeaways:

• Data Definition Language (DDL) is the subset of SQL used to create and manage the structure of database objects.
• CREATE TABLE is used to define a new table, its columns, and their data types. This is the blueprint for your data.
• DROP TABLE is a destructive command that completely removes a table and all its data.
• ALTER TABLE is used to modify an existing table's structure, such as adding, removing, or changing columns. This is an essential command for evolving a database schema over time.
• ALTER operations can be resource-intensive on large tables because the database often has to rebuild the table in the background.

Preview of the Next Lesson:
We briefly touched on data types (INT, VARCHAR) and constraints (NOT NULL, IDENTITY) in this lesson. In the next lesson, we will dive much deeper. You'll learn how to choose the right data types and apply various constraints (UNIQUE, PRIMARY KEY, FOREIGN KEY) to enforce data integrity at the database level. This is a crucial step in designing robust and reliable database schemas.