Creating and Manipulating Tables

Understanding Table Creation

Up to this point, your focus has been on retrieving, inserting, and modifying data within existing database structures. However, managing a relational database also requires the ability to create, alter, and delete the underlying structures that house your data. These tasks are performed using the Data Definition Language (DDL) subset of SQL.

The foundational structure in any relational database is the table. While many database management systems provide visual tools for building tables, writing the raw SQL code gives you absolute control over the schema and allows you to script your database deployments directly.

The CREATE TABLE Statement

To create a new table, you use the CREATE TABLE statement. This statement requires you to specify the name of the new table, followed by a comma-separated list of the columns that will make up the table. For each column, you must define its name, its specific data type, and whether it allows missing data.

CREATE TABLE new_employees (
    employee_id INT NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    phone_number VARCHAR(20) NULL
);

In this example, the database engine creates a new table named new_employees with four distinct columns. The data types, such as INT (integer) and VARCHAR (variable-length character string), dictate exactly what kind of information can be stored in each field and how the database should process it.

Handling Null Values

When defining a table, you must explicitly state whether each column can accept a NULL value. If a column is defined as NOT NULL, the database engine will reject any INSERT or UPDATE operation that attempts to leave that column empty. This is a primary method for enforcing basic data integrity at the database level. If a column is defined as NULL, the system will permit records to be saved even if that specific piece of data is missing. If you omit the NULL or NOT NULL designation, most database systems will default to allowing NULL values.

Specifying Default Values

SQL allows you to assign a default value to a column using the DEFAULT keyword during table creation. If a user inserts a new row but omits a value for that specific column, the database engine will automatically insert the predefined default value rather than leaving the field blank.

CREATE TABLE new_orders (
    order_id INT NOT NULL,
    order_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'Pending'
);

Default values are frequently used for system dates, standard status flags, and mathematical baselines.

Modifying Existing Tables

As business requirements evolve, you may need to change a table's structure. You perform this task using the ALTER TABLE statement. Because modifying a table that already contains data can be complex and risky, ALTER TABLE is typically used for simple operations like adding a brand new column. For example, let's add an email_address column to the existing, seeded employees table:

ALTER TABLE employees
ADD email_address VARCHAR(100) NULL;

You must use ALTER TABLE with extreme caution. Depending on your specific database system, altering or dropping existing columns can result in permanent data loss or break existing applications that rely on the original database schema.

Deleting and Renaming Tables

If a table is no longer needed, you can remove it from the database entirely using the DROP TABLE statement.

DROP TABLE deprecated_inventory;

This command is immediate and permanent. It deletes the table structure and permanently erases all the data stored within it. There is no confirmation prompt, so it must be executed with absolute certainty.

Occasionally, you may also need to rename a table. While the concept is standard, the specific SQL syntax for renaming tables varies significantly across different database management systems. You will need to consult your specific platform's documentation to perform a rename operation safely.

Apply these data definition techniques to structure your database securely before moving on to creating virtual tables using views.