24

CHAPTER 24

CREATE TABLE & DDL

Designing and modifying database structure.

3

Learning Blocks

SQL

Interactive Queries

In This Chapter

Concepts you'll master

CREATE TABLE with all constraint types

PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, DEFAULT, CHECK

ALTER TABLE — adding, modifying, dropping columns

DROP TABLE — remove a table entirely

Creating our employees table from scratch

SQL QUERY

CREATE TABLE employees (
    emp_id        INT PRIMARY KEY AUTO_INCREMENT,
    name          VARCHAR(100)    NOT NULL,
    department    VARCHAR(50)     NOT NULL,
    salary        DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
    manager_id    INT,
    hire_date     DATE            NOT NULL,
    CONSTRAINT chk_salary CHECK (salary >= 0),
    FOREIGN KEY (manager_id) REFERENCES employees(emp_id)
);

ALTER TABLE

SQL

SQL QUERY

ALTER TABLE employees ADD COLUMN phone VARCHAR(15);
ALTER TABLE employees MODIFY COLUMN salary DECIMAL(12,2) NOT NULL;
ALTER TABLE employees DROP COLUMN phone;
ALTER TABLE employees RENAME COLUMN department TO dept;