DDL Commands

Shaping Your Database

DDL (Data Definition Language) commands define and modify the database structure — creating tables, altering their structure, and removing them. DDL defines the schema that DML commands will work with.

CREATE TABLE

  CREATE TABLE Students (
      ID        INT PRIMARY KEY,
      Name      VARCHAR(100) NOT NULL,
      Age       INT,
      Email     VARCHAR(150) UNIQUE,
      DeptID    INT,
      CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  );

  CREATE TABLE Courses (
      CourseID  INT PRIMARY KEY,
      Title     VARCHAR(200) NOT NULL,
      Credits   INT DEFAULT 3,
      DeptID    INT
  );

  CREATE TABLE Enrollments (
      StudentID INT,
      CourseID  INT,
      Grade     CHAR(2),
      PRIMARY KEY (StudentID, CourseID),
      FOREIGN KEY (StudentID) REFERENCES Students(ID),
      FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
  );

ALTER TABLE

  -- Add a column
  ALTER TABLE Students ADD Phone VARCHAR(20);

  -- Modify a column
  ALTER TABLE Students MODIFY Email VARCHAR(200);

  -- Drop a column
  ALTER TABLE Students DROP COLUMN Phone;

  -- Rename a column
  ALTER TABLE Students RENAME COLUMN Name TO FullName;

  -- Add a constraint
  ALTER TABLE Students ADD CONSTRAINT chk_age CHECK (Age >= 16);

DROP and TRUNCATE

  -- DROP: removes the entire table (structure + data)
  DROP TABLE Students;
  -- Cannot be undone!

  -- TRUNCATE: removes all rows but keeps the structure
  TRUNCATE TABLE Students;
  -- Faster than DELETE, resets auto-increment

DROP vs. DELETE vs. TRUNCATE: