Deep Dive into SQL DDL Commands

Data Definition Language (DDL) is a subset of SQL (Structured Query Language) that deals with metadata management. It allows you to create, alter, and delete database objects such as tables, indices, and schemas. This blog post will explain the basic DDL commands in SQL: CREATE, ALTER, DROP, TRUNCATE, and RENAME.


link to this section

The CREATE command is used to create a database, table, index, or stored procedure.

Create Database:

CREATE DATABASE database_name; 

This command creates a new database named database_name .

Create Table:

CREATE TABLE table_name ( 
    column1 datatype, 
    column2 datatype, 
    column3 datatype, 

This command creates a new table named table_name with the specified columns and datatypes.


link to this section

The ALTER command is used to modify an existing database object like a table. It allows you to add, modify, or delete columns in an existing table. It can also be used to add and drop various constraints on an existing table.

Add Column:

ALTER TABLE table_name 
ADD column_name datatype; 

This command adds a new column to an existing table.

Modify Column:

ALTER TABLE table_name 
MODIFY column_name datatype; 

This command changes the datatype of a column in a table.


link to this section

The DROP command is used to delete an existing database, table, index, or view.

Drop Table:

DROP TABLE table_name; 

This command deletes an existing table and all its rows.

Drop Database:

DROP DATABASE database_name; 

This command deletes an entire database.


link to this section

The TRUNCATE command is used to delete all rows from a table without deleting the table itself. Unlike the DROP command, TRUNCATE allows you to maintain the structure of the table for future use.

TRUNCATE TABLE table_name; 


link to this section

The RENAME command is used to rename an existing table.

RENAME TABLE old_table_name TO new_table_name; 

This command changes the name of the table from old_table_name to new_table_name .


link to this section

In SQL, constraints are used to specify rules for the data in a table. If there is any violation between the constraint and the data action, the action is aborted. Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table. The following constraints are commonly used in SQL:

  • NOT NULL : Ensures that a column cannot have a NULL value.
  • UNIQUE : Ensures that all values in a column are different.
  • PRIMARY KEY : Uniquely identifies each record in a table.
  • FOREIGN KEY : Prevents actions that would destroy links between tables.
  • CHECK : Ensures that the value in a column meets a specific condition.
  • DEFAULT : Provides a default value for a column.

Adding a Check Constraint:

ALTER TABLE table_name 
ADD CONSTRAINT constraint_name 
CHECK (condition); 

Dropping a Constraint:

ALTER TABLE table_name 
DROP CONSTRAINT constraint_name; 


link to this section

An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value and hence it will be faster to retrieve data.

Creating an Index:

CREATE INDEX index_name 
ON table_name (column1, column2, ...); 

Dropping an Index:

DROP INDEX index_name; 

Please note that not all database systems support the DROP INDEX command.


link to this section

A sequence is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values.

Creating a Sequence:

CREATE SEQUENCE sequence_name 
START WITH initial_value 
INCREMENT BY increment_value 
MINVALUE minimum_value 
MAXVALUE maximum_value; 

Dropping a Sequence:

DROP SEQUENCE sequence_name; 


link to this section

A View in SQL is a logical subset of data from one or more tables. Views are virtual tables that do not store data of their own but display data stored in other tables. They are used to encapsulate complex queries, provide a layer of security, and present data in a different perspective.

Creating a View:

CREATE VIEW view_name AS 
SELECT column1, column2, ... 
FROM table_name 
WHERE condition; 

Dropping a View:

DROP VIEW view_name; 


link to this section

In SQL, a synonym is an alias or alternative name for a table, view, sequence, procedure, or other database objects. They provide both data independence and location transparency. Synonyms permit applications to function without modification regardless of which user owns the table or view and regardless of which database holds the table or view.

Creating a Synonym:

CREATE SYNONYM synonym_name FOR object_name; 

Dropping a Synonym:

DROP SYNONYM synonym_name; 

Database Schema

link to this section

A database schema is the skeleton structure that represents the logical view of the entire database. It defines how the data is organized and how the relations among them are associated. It formulates all the constraints that are to be applied on the data.

Creating a Schema:

CREATE SCHEMA schema_name 

Altering a Schema:

ALTER SCHEMA schema_name TRANSFER object_name; 

Dropping a Schema:

DROP SCHEMA schema_name; 


link to this section

SQL's Data Definition Language (DDL) commands are instrumental in structuring and managing databases. Through this comprehensive guide, you have learned the core components and advanced features of DDL, including creating tables, altering structures, managing constraints, and implementing stored procedures, functions, and triggers. By mastering these concepts, you can design efficient databases, maintain data integrity, and automate repetitive tasks.

Remember to refer to the specific syntax and capabilities of your chosen database system, as SQL dialects may vary. Practice using DDL commands to build, modify, and optimize your database schema. With a strong foundation in SQL DDL, you have the tools to create well-organized, scalable, and secure databases for your applications and systems.

So, go ahead and apply your newfound knowledge of SQL DDL to streamline your data management processes and take your database skills to the next level.