CREATE and ALTER Commands

In SQL, the CREATE and ALTER commands are part of the Data Definition Language (DDL). These commands help define and modify the structure of your database objects like tables, columns, constraints and indexes without affecting the actual data inside.

CREATE Command

The CREATE command is used to define new database objects such as tables, databases, views or indexes. It acts as the blueprint for your data—letting you define how it is organized, the types of data it will store and the rules it should follow. Common uses of CREATE Command are listed below:

• Creating a new database or table
• Defining columns, data types, and constraints
• Structuring the schema before data is inserted

Syntax

CREATE TABLE table_name (

column1 datatype constraint,

column2 datatype constraint,

...

);

ALTER Command

The ALTER command is used to modify an existing table, adding new columns, changing data types, renaming fields or applying/removing constraints. This is useful when your data requirements change over time and you need to:

• Add or delete columns
• Change a column’s data type or constraint
• Rename columns or tables

Syntax

ALTER TABLE table_name

ADD|MODIFY|DROP|RENAME column_name datatype;

Example of CREATE and ALTER Commands

Let’s create a real-world scenario to understand how CREATE and ALTER work using a Student table in a college database.

Step 1: Create the Student Table

CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50),
    Age INT CHECK (Age >= 16),
    Email VARCHAR(100) UNIQUE
);

Key Terms:

• StudentID: Unique ID for each student (Primary Key).
• FirstName: Required field (NOT NULL).
• Age: Must be 16 or older (CHECK constraint).
• Email: Must be unique across all students.

Step 2: Insert Sample Data

This adds 3 student records to the Student table with basic information like name, age, and email.

INSERT INTO Student (StudentID, FirstName, LastName, Age, Email)
VALUES 
(1, 'Aarav', 'Sharma', 20, '[email protected]'),
(2, 'Meera', 'Verma', 19, '[email protected]'),
(3, 'Ravi', 'Patel', 18, '[email protected]');

Step 3: Use ALTER to Add a New Column

We modify the structure of the table by adding a new column named Department to track each student’s academic department.

ALTER TABLE Student
ADD Department VARCHAR(50);

Step 4: Update Department Data

We now populate the new Department column with values corresponding to each student using the UPDATE statement.

UPDATE Student
SET Department = 'Computer Science'
WHERE StudentID = 1;

UPDATE Student
SET Department = 'Electronics'
WHERE StudentID = 2;

UPDATE Student
SET Department = 'Mechanical'
WHERE StudentID = 3;

Step 5: View Final Table

SELECT * FROM Student;

Output:

Common SQL Constraints Used in CREATE & ALTER

CREATE vs ALTER