SQL Guide Chapter

Lesson 11A – Introduction to Data Definition Language in Oracle (or: how to build the furniture before you sit on it)

And look, all the querying, joining, and updating in the world doesn’t help if the tables themselves are wrong—missing columns, bad data types, or no constraints so people can enter “banana” as a salary. DDL is where yo...

oracle 19c mysql notes study chapter

And look, all the querying, joining, and updating in the world doesn’t help if the tables themselves are wrong—missing columns, bad data types, or no constraints so people can enter “banana” as a salary. DDL is where you define what the database is, not just what’s inside it.

This lesson is about the structure: tables, columns, and constraints.

You will learn to:

1. Database Objects and Naming Rules

Common Oracle schema objects:

Naming rules for tables and columns:

So employees, dept_80, and job_history are fine. select and table are not.

2. CREATE TABLE – Defining Structure

To create a table you need:

Syntax (basic):

CREATE TABLE dept (
      deptno      NUMBER(2),
      dname       VARCHAR2(14),
      loc         VARCHAR2(13),
      create_date DATE DEFAULT SYSDATE
    );

Key points:

You can inspect the structure of a table with:

DESC dept;

…and of course with the SQL Developer UI or USER_TAB_COLUMNS data dictionary view.

3. Common Oracle Data Types

Character and numeric types:

Date and time:

Large objects and others (high level):

In most application tables you’ll use NUMBER, VARCHAR2, DATE, and occasionally TIMESTAMP and LOBs.

4. DEFAULT Values

You can specify a default for a column in CREATE TABLE:

CREATE TABLE employees_demo (
      employee_id   NUMBER(6),
      last_name     VARCHAR2(25) NOT NULL,
      hire_date     DATE DEFAULT SYSDATE,
      status        VARCHAR2(10) DEFAULT 'ACTIVE'
    );

Rules:

Default values apply when no value is supplied in the INSERT for that column.

5. Constraints – Enforcing Rules on Data

Constraints enforce business rules and maintain data integrity.

Core types:

5.1 Where and when you can define constraints

NOT NULL is always a column‑level constraint.

If you don’t name a constraint, Oracle invents one (SYS_C009876 etc.), which is how you end up hating yourself when debugging. Naming them is strongly recommended.

5.2 Column‑level constraints

Defined inline with the column:

CREATE TABLE employees_demo (
      employee_id NUMBER(6)
        CONSTRAINT emp_emp_id_pk PRIMARY KEY,
      last_name   VARCHAR2(25)  CONSTRAINT emp_lname_nn NOT NULL,
      email       VARCHAR2(25),
      salary      NUMBER(8,2)
    );

5.3 Table‑level constraints

Defined after all columns; useful for composite keys or when you prefer to group constraints together:

CREATE TABLE employees_demo (
      employee_id NUMBER(6),
      first_name  VARCHAR2(20),
      last_name   VARCHAR2(25),
      email       VARCHAR2(25),
      salary      NUMBER(8,2),
    
      CONSTRAINT emp_pk PRIMARY KEY (employee_id),
      CONSTRAINT emp_email_uk UNIQUE (email)
    );

For composite primary keys:

CONSTRAINT emp_name_pk PRIMARY KEY (employee_id, first_name)

Table‑level constraints always reference one or more existing columns by name.

5.4 NOT NULL

Ensures a column cannot be NULL.

last_name VARCHAR2(25) CONSTRAINT emp_lname_nn NOT NULL

5.5 UNIQUE

Ensures each non‑NULL value is unique in the column (or column set).

Example – prevent duplicate emails:

CONSTRAINT emp_email_uk UNIQUE (email)

5.6 PRIMARY KEY and FOREIGN KEY

PRIMARY KEY:

Example:

CONSTRAINT dept_pk PRIMARY KEY (department_id)

FOREIGN KEY:

Example – table‑level foreign key:

CREATE TABLE employees_demo (
      employee_id   NUMBER(6) PRIMARY KEY,
      last_name     VARCHAR2(25) NOT NULL,
      department_id NUMBER(4),
    
      CONSTRAINT emp_dept_fk
        FOREIGN KEY (department_id)
        REFERENCES departments (department_id)
    );

Options:

Good for enforcing referential integrity; dangerous if you forget you added CASCADE.

5.7 CHECK

Validates that a condition is true for each row. It cannot reference columns in other tables.

Example – salary must be > 0:

CONSTRAINT emp_salary_chk
      CHECK (salary > 0)

Another example – restrict job_id to a set of codes:

CONSTRAINT emp_job_chk
      CHECK (job_id IN ('SA_REP', 'IT_PROG', 'AD_PRES'))

If you violate a constraint, Oracle tells you which constraint you violated—another good reason to name them.

6. Creating Tables with Subqueries (CTAS)

You can create a table and populate it at the same time using CREATE TABLE ... AS SELECT (CTAS).

Example – create dept80 from employees in department 80:

CREATE TABLE dept80 AS
    SELECT employee_id,
           last_name,
           salary * 12 AS annsal,
           hire_date
    FROM   employees
    WHERE  department_id = 80;

Key points:

7. ALTER TABLE – Changing Existing Structure

Use ALTER TABLE to:

7.1 ADD columns

ALTER TABLE dept80
    ADD (job_id VARCHAR2(9));

7.2 MODIFY columns

ALTER TABLE dept80
    MODIFY (last_name VARCHAR2(30));

Rules:

7.3 DROP columns

ALTER TABLE dept80
    DROP COLUMN job_id;

7.4 SET UNUSED and DROP UNUSED COLUMNS

Alternative to DROP COLUMN for large tables:

ALTER TABLE dept80 SET UNUSED (job_id);
    -- Later...
    ALTER TABLE dept80 DROP UNUSED COLUMNS;

7.5 READ ONLY / READ WRITE

You can lock a table’s structure and data against change during maintenance:

ALTER TABLE employees READ ONLY;
    -- table can be queried, but not modified or altered
    
    -- When done
    ALTER TABLE employees READ WRITE;

Useful when you want to freeze a table while still letting people run reports.

8. DROP TABLE and the Recycle Bin

DROP TABLE removes a table and its data.

DROP TABLE dept80;

By default (in modern Oracle with Recycle Bin enabled):

To bypass the Recycle Bin and remove the table permanently:

DROP TABLE dept80 PURGE;

Flashback example:

FLASHBACK TABLE dept80 TO BEFORE DROP;

After which you can DESC dept80 and see the structure again.

9. What You Should Now Be Able to Do

By the end of this lesson, you should be able to:

You now have the power not just to query data, but to shape the schema itself—which is how you go from “SQL user” to “person everyone blames when a table disappears.”

← previous view source markdown next →
// sql-guide generated from markdown notes