Database Tables in DBMS: DDL Essentials Explained

Creating, Modifying, and Deleting Tables in a Database Management System (DBMS): DDL Deep Dive

When building a database to store information, tables are the foundational building blocks. The Database Definition Language (DDL) is a set of instructions used to create, modify, and delete tables in a Database Management System (DBMS). Let's explore these essential DDL concepts in more detail.

Creating Tables

To create a table, you use the CREATE TABLE statement, specifying its name, columns, and data types:

CREATE TABLE customers
(
  id INTEGER PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100)
);

This code snippet creates a customers table with three columns: id (an integer primary key), name (a string with a length of 50 characters), and email (a string with a length of 100 characters).

Modifying Tables

Sometimes you need to modify existing tables to add or change columns. To do this, you use the ALTER TABLE statement:

ALTER TABLE customers
ADD COLUMN address VARCHAR(100);

This statement adds a new column called address (a string with a length of 100 characters) to the customers table.

To rename a column, you can use the ALTER TABLE statement with the ALTER COLUMN clause:

ALTER TABLE customers
ALTER COLUMN email VARCHAR(255) NOT NULL;

This statement changes the data type of the email column to VARCHAR(255) and adds the NOT NULL constraint, which means the column cannot contain null values.

To delete a column, you use the ALTER TABLE statement with the DROP COLUMN clause:

ALTER TABLE customers
DROP COLUMN address;

This statement removes the address column from the customers table.

Deleting Tables

When a table is no longer needed, it can be deleted using the DROP TABLE statement:

DROP TABLE customers;

This statement removes the customers table from the DBMS.

Setting Constraints

Constraints are used to enforce data integrity and ensure the quality of data in a table. Constraints like primary keys, foreign keys, and not null can be declared during table creation or added later using the ALTER TABLE statement:

CREATE TABLE customers
(
  id INTEGER PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL,
  FOREIGN KEY (email) REFERENCES users(email)
);

This statement creates a customers table with a foreign key constraint on the email column, which references the primary key of another table called users.

Understanding and applying these DDL concepts are fundamental to building and maintaining a well-structured database. While this article touches upon the basics, DDL can be more complex in reality, and further exploration of advanced features and tools is encouraged.