Schema Change Statements in SQL

 

Schema Change Statements in SQL

Schema change statements allow the structure of a database to be modified while the database is running, without rebuilding or recompiling the whole database.

These commands support schema evolution, meaning:

  • Adding or removing tables

  • Adding or removing attributes (columns)

  • Modifying constraints

  • Changing defaults and definitions

📌 The DBMS performs consistency checks to ensure that changes do not break references, constraints, or views.

Main Schema Change Commands in SQL

  1. DROP – removes schema elements

  2. ALTER – modifies existing schema elements

7.4.1 The DROP Command

The DROP command is used to delete schema objects completely.

It can remove:

  • Schemas

  • Tables

  • Domains

  • Types

  • Constraints

  • Views

⚠️ DROP removes both:

  • Data

  • Definition (from system catalog)

DROP SCHEMA

DROP SCHEMA COMPANY CASCADE;

CASCADE vs RESTRICT

OptionMeaning
CASCADEDrops schema and all dependent objects
RESTRICTDrops schema only if empty

📌 With RESTRICT, user must first delete all tables, views, constraints manually.

DROP TABLE

DROP TABLE DEPENDENT CASCADE;

Explanation

  • Removes the table and all its records

  • Deletes the table definition from the catalog

  • With CASCADE: dependent views, constraints, foreign keys are also removed

DROP TABLE: CASCADE vs RESTRICT

OptionBehavior
CASCADE            Deletes table + dependent objects
RESTRICT            Fails if table is referenced anywhere

DROP vs DELETE (Very Important for Exams)

DROP TABLEDELETE
Removes table structureKeeps table structure
Deletes all recordsDeletes selected/all records
Table no longer existsTable still usable

📌 Use DELETE when you want to keep the table for future use.

Dropping Other Schema Elements

DROP CONSTRAINT constraint_name;
DROP DOMAIN domain_name;

📌 Constraint must be named to be dropped.

7.4.2 The ALTER Command

The ALTER command is used to modify existing schema objects, especially tables.

It can:

  • Add columns

  • Drop columns

  • Modify column definitions

  • Add or drop constraints

  • Change default values

ALTER TABLE – Add Column

ALTER TABLE COMPANY.EMPLOYEE
ADD COLUMN Job VARCHAR(12);

What Happens?

  • New column Job is added

  • Existing rows get:

    • NULL values (if no default specified)

⚠️ NOT NULL is not allowed unless:

  • A default value is provided

  • Or values are updated immediately

ALTER TABLE – Drop Column

ALTER TABLE COMPANY.EMPLOYEE
DROP COLUMN Address CASCADE;

Behavior Options

OptionEffect
CASCADE                Drops column and dependent constraints/views
RESTRICT                Drops column only if unused

ALTER TABLE – Change Default Value

Remove Default

ALTER TABLE COMPANY.DEPARTMENT
ALTER COLUMN Mgr_ssn DROP DEFAULT;

Set Default

ALTER TABLE COMPANY.DEPARTMENT
ALTER COLUMN Mgr_ssn SET DEFAULT '333445555';

📌 Affects future inserts, not existing data.

ALTER TABLE – Drop Constraint

ALTER TABLE COMPANY.EMPLOYEE
DROP CONSTRAINT EMPSUPERFK CASCADE;

Key Points

  • Constraint must be named

  • CASCADE removes dependent objects

  • Once dropped, a new constraint can be added

ALTER TABLE – Add Constraint

ALTER TABLE COMPANY.EMPLOYEE
ADD CONSTRAINT NewConstraint
FOREIGN KEY (Super_ssn)
REFERENCES EMPLOYEE(Ssn);

📌 Supports:

  • PRIMARY KEY

  • FOREIGN KEY

  • UNIQUE

  • CHECK

  • NOT NULL

Important Observations 

✔ Schema changes can be made without stopping the database
✔ DBMS ensures referential and structural consistency
✔ CASCADE removes dependencies automatically
✔ RESTRICT prevents unsafe changes
✔ ALTER modifies structure, DROP removes structure entirely

Summary Table

CommandPurpose
DROP            Removes schema objects completely
ALTER            Modifies existing schema
CASCADE            Removes dependent objects
RESTRICT            Prevents deletion if dependencies exist

One-Line Exam Definition

Schema change statements in SQL allow dynamic modification of database structure using DROP and ALTER commands while preserving database consistency.

Comments

Post a Comment

Popular posts from this blog

Database Management Systems DBMS PCCST402 Semester 4 KTU CS 2024 Scheme

 About Me Database Management Systems DBMS PCCST402 Scheme and Syllabus Module-1 Introduction to DBMS Introduction to DBMS Example  Database Database System Vs Traditional File Processing Advantages of using DBMS Database System Concepts and Architecture Data Models, Schemas and Instances   Three schema architecture and Data Independence Database Languages and Interfaces Centralized and Client Server Architectures in DBMS Classification of Database Management Systems Conceptual Data Modeling and Database Design Conceptual Modeling and the Role of the ER Model in Database Design Entities and Attributes  Company Database Application - Example Entity Types, Entity Sets, Keys, and Value Sets in the ER Model Initial Conceptual Design of the COMPANY Database Relationship Types in the ER Model Relationship Degree, Role Names, and Recursive Relationships Constraints on Binary Relationship Types Attributes of Relationship Types Weak Entity Types Summary of the Notations Used ...
Read more

Data Models, Schemas and Instances

  Data Models, Schemas and Instances  1. Data Abstraction In databases, data abstraction means hiding the complex details of how data is stored and focusing only on what is important. Different users can view data at different levels of detail depending on their needs. 2. Data Models A data model is a collection of concepts used to describe the structure of a database. It defines: Data types Relationships among data Constraints on data Operations for accessing and updating data Data models help us understand and design databases without worrying about low-level storage details. Categories of Data Models a) High-level / Conceptual Data Models Close to how users think about data. Use concepts like entities , attributes , and relationships . Example: ER (Entity–Relationship) Model . b) Representational (Implementation) Data Models In between conceptual and physical models. Easy for users to understand but still close to how data is st...
Read more

Introduction to Database Management System -DBMS

Image
In today’s digital world, almost every application—from mobile banking and e-commerce to university portals and hospital systems—depends on the efficient storage, organization, and retrieval of data. As the amount of information generated by individuals and organizations continues to grow, managing this data in a structured and meaningful way becomes essential. This is where database systems play a central role. A database can be thought of as a well-organized collection of related data, designed so that it can be easily accessed, managed, and updated. But the database itself is only one component. To manage it effectively, we rely on a specialized software package known as a Database Management System (DBMS) . A DBMS provides a systematic and automated way to store large volumes of data, enforce rules for consistency and correctness, protect data from unauthorized access, and allow multiple users to work with the data simultaneously without conflicts. A database typically has three...
Read more