SQL Table Creation and Modification with DDL

Covers SQL DDL commands: CREATE TABLE with data types and constraints, ALTER TABLE for modifications, CREATE VIEW, and DROP TABLE.

3 min read

SQL’s Data Definition Language (DDL) is used to create, modify, and delete database structures and objects (tables, views, etc.).

1. Creating Tables (CREATE TABLE)

The CREATE TABLE statement creates a new table in the database.

CREATE TABLE table_name (
    column1 data_type [DEFAULT value] [constraint],
    column2 data_type [DEFAULT value] [constraint],
    -- ...
    columnN data_type [DEFAULT value] [constraint],
    [table_level_constraint]
);

Data Types

Each column requires a data type appropriate for the kind of data it stores.

  • Character types:
    • CHAR(size): Fixed-length string. Stores data padded to the specified size.
    • VARCHAR(size): Variable-length string. Stores data up to the specified size limit.
  • Numeric types:
    • INT / INTEGER: Integer.
    • BIGINT: Larger integer.
    • FLOAT / DOUBLE: Floating-point numbers.
  • Date/Time types:
    • DATE: Date (e.g., ‘YYYY-MM-DD’).
    • TIME: Time (e.g., ‘HH:MM:SS’).
    • DATETIME / TIMESTAMP: Date and time.

Constraints

Setting constraints on columns or tables maintains data integrity.

  • NOT NULL: Does not allow NULL values (absence of a value) in the column.
  • UNIQUE: Guarantees that column values are unique within the table.
  • PRIMARY KEY: Primary key constraint. The column (or combination of columns) uniquely identifies each row and satisfies both NOT NULL and UNIQUE.
  • FOREIGN KEY: Foreign key constraint. References the primary key of another table, defining relationships between tables and ensuring referential integrity.
  • DEFAULT value: Sets a default value that is automatically inserted when no value is explicitly specified.

Creating Tables from Subqueries (CREATE TABLE AS SELECT)

You can also create a new table based on an existing table or query result.

CREATE TABLE new_table_name [(column1, column2, ...)]
AS subquery;

Example: Extract employees from the employees table where department_id is 10 into a new table department10_employees.

CREATE TABLE department10_employees AS
SELECT employee_id, first_name, last_name, email
FROM employees
WHERE department_id = 10;

Creating Views (CREATE VIEW)

A view is a virtual table based on one or more tables (or other views). Views do not physically store data; when the source tables change, the view’s content is automatically reflected.

CREATE VIEW view_name [(column1, column2, ...)]
AS subquery;

Example: Create a view expensive_products that shows only products priced at 1000 or above from the products table.

CREATE VIEW expensive_products AS
SELECT product_id, product_name, price
FROM products
WHERE price >= 1000;

Views are useful for simplifying complex queries or restricting the data displayed to specific users.

2. Modifying and Deleting Tables

Modifying Table Structure (ALTER TABLE)

The ALTER TABLE statement modifies the structure of an existing table.

  • Adding a column:
    ALTER TABLE table_name
ADD column_name data_type [DEFAULT value] [constraint];
  • Modifying a column:
    ALTER TABLE table_name
MODIFY column_name data_type [constraint]; -- MySQL, Oracle, etc.
-- or
ALTER TABLE table_name
ALTER COLUMN column_name SET DATA TYPE data_type; -- PostgreSQL, etc.
  • Dropping a column:
    ALTER TABLE table_name
DROP COLUMN column_name;

Renaming Tables (RENAME TABLE)

Renames a table.

RENAME TABLE old_table_name TO new_table_name;

Dropping Tables (DROP TABLE)

The DROP TABLE statement completely removes a table and all its data from the database.

DROP TABLE table_name;

Caution: DROP TABLE is an irreversible operation, so execute it with extreme care.

関連記事