Skip to content
Tutorials Center
My learning

Topics

Lesson 6 of 38 ยท SQL in Oracle

Creating Tables and Constraints

Create Oracle tables with primary keys, foreign keys, unique, check and not-null constraints, add virtual columns, alter and drop tables, and read the dictionary.

DDL, short for data definition language, is the part of SQL that creates and changes the structure of the database: CREATE, ALTER, DROP and TRUNCATE. In Oracle, every DDL statement commits automatically, before and after it runs. You cannot roll a CREATE TABLE back, so double-check what you type.

A good table definition does more than name columns. It uses constraints so the database itself rejects bad data: a missing name, a negative price, an order that points at a customer who does not exist. This lesson covers the Oracle syntax for all of it.

CREATE TABLE

SQL
CREATE TABLE departments (
  department_id  NUMBER(6)      CONSTRAINT dept_pk PRIMARY KEY,
  name           VARCHAR2(60)   NOT NULL,
  location       VARCHAR2(60)
);

CREATE TABLE employees (
  employee_id    NUMBER(6)      GENERATED ALWAYS AS IDENTITY,
  first_name     VARCHAR2(40),
  last_name      VARCHAR2(40)   NOT NULL,
  email          VARCHAR2(100)  NOT NULL,
  hire_date      DATE           DEFAULT SYSDATE NOT NULL,
  salary         NUMBER(9,2)    CHECK (salary > 0),
  department_id  NUMBER(6),
  manager_id     NUMBER(6),
  CONSTRAINT emp_pk       PRIMARY KEY (employee_id),
  CONSTRAINT emp_email_uk UNIQUE (email),
  CONSTRAINT emp_dept_fk  FOREIGN KEY (department_id)
                          REFERENCES departments (department_id),
  CONSTRAINT emp_mgr_fk   FOREIGN KEY (manager_id)
                          REFERENCES employees (employee_id)
);

Oracle upper-cases unquoted names, so these tables are stored as EMPLOYEES and DEPARTMENTS. A table name may be up to 128 bytes in 12.2 and later, and must start with a letter.

Since 23ai you can write CREATE TABLE IF NOT EXISTS employees (...) and DROP TABLE IF EXISTS employees, which saves the old trick of catching an error in PL/SQL.

The five constraint types

Constraint Rule Typical use
NOT NULL Value required Names, dates, amounts
UNIQUE No two rows share the value (NULLs are allowed) Email, username
PRIMARY KEY NOT NULL plus UNIQUE; one per table Row identity
FOREIGN KEY Value must exist in the parent table (or be NULL) Links between tables
CHECK A condition on the row must not be false Ranges and allowed values

Give constraints names (CONSTRAINT emp_pk ...). If you do not, Oracle invents names like SYS_C0012345, and error messages become hard to read. Violations report the name: ORA-00001: unique constraint (LEARNER.EMP_EMAIL_UK) violated.

Foreign key actions

By default you cannot delete a parent row that children still reference. You can change that:

SQL
FOREIGN KEY (department_id) REFERENCES departments (department_id)
  ON DELETE CASCADE        -- delete the children with the parent
-- or
  ON DELETE SET NULL       -- keep the children, clear the link

Oracle has no ON UPDATE action. Primary keys should not change anyway.

CHECK constraints and DEFAULT

SQL
status   VARCHAR2(10) DEFAULT 'NEW' NOT NULL
         CONSTRAINT ord_status_ck CHECK (status IN ('NEW','PAID','SHIPPED')),
discount NUMBER(3,2)  DEFAULT 0 CHECK (discount BETWEEN 0 AND 0.5)

A CHECK passes when the condition is true or unknown, so a NULL discount slips through unless the column is also NOT NULL.

Stand-in: SQLite accepts these constraints, so you can try the logic. This example creates a table and inserts a valid row:

SQL Constraints at work (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Now break a rule on purpose. The error shows what a violated CHECK looks like (Oracle reports ORA-02290: check constraint (...) violated):

SQL A rejected row

Press Run (or Ctrl + Enter) to see the result here.

Virtual columns and identity columns

A virtual column is computed from other columns and takes no storage:

SQL
CREATE TABLE order_lines (
  quantity    NUMBER(5)    NOT NULL,
  unit_price  NUMBER(9,2)  NOT NULL,
  line_total  NUMBER(12,2) GENERATED ALWAYS AS (quantity * unit_price) VIRTUAL
);

You can index a virtual column or put a constraint on it. The GENERATED ... AS IDENTITY clause in the employees example creates an auto-numbered key; sequences and identity covers it.

The same idea runs in the editor:

SQL Virtual column (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

ALTER TABLE

SQL
ALTER TABLE employees ADD (phone VARCHAR2(30));            -- add a column
ALTER TABLE employees MODIFY (first_name VARCHAR2(80));    -- change a column
ALTER TABLE employees RENAME COLUMN phone TO mobile;
ALTER TABLE employees DROP COLUMN mobile;
ALTER TABLE employees ADD CONSTRAINT emp_sal_ck CHECK (salary < 1000000);
ALTER TABLE employees DROP CONSTRAINT emp_sal_ck;
ALTER TABLE employees RENAME TO staff;

Oracle puts new columns in parentheses and uses MODIFY (not ALTER COLUMN). Shrinking a column fails if existing data would not fit, and you cannot make a column NOT NULL while it still contains NULLs.

DROP and TRUNCATE

SQL
TRUNCATE TABLE order_lines;               -- remove all rows fast, keep the table (no rollback)
DROP TABLE order_lines;                   -- remove the table (goes to the recycle bin)
DROP TABLE order_lines PURGE;             -- remove for good
DROP TABLE departments CASCADE CONSTRAINTS;  -- also drop foreign keys that reference it
FLASHBACK TABLE order_lines TO BEFORE DROP;  -- bring a dropped table back

A dropped table normally goes to the recycle bin and can be restored while its space is not needed. PURGE skips the bin. TRUNCATE is DDL: it cannot be rolled back, while DELETE FROM t can.

Looking at what you built

SQL
SELECT constraint_name, constraint_type, table_name
FROM user_constraints
WHERE table_name = 'EMPLOYEES';

constraint_type is P for primary key, R for foreign key (referential), U for unique and C for check or not-null. USER_TAB_COLUMNS lists columns and USER_CONS_COLUMNS the columns of each constraint.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What happens when you run CREATE TABLE inside a transaction that you later roll back?

    Oracle commits before and after every DDL statement, so ROLLBACK cannot undo CREATE, ALTER or DROP.
  2. 2. A CHECK (discount BETWEEN 0 AND 0.5) column also allows:

    A CHECK only rejects rows where the condition is false. Add NOT NULL to forbid NULL.
  3. 3. How do you change a column's size in Oracle?

    Oracle uses MODIFY. ALTER COLUMN is SQL Server and PostgreSQL syntax, CHANGE is MySQL.
  4. 4. What does DROP TABLE t PURGE do differently from DROP TABLE t?

    A plain DROP puts the table in the recycle bin. PURGE removes it permanently.
  5. 5. Which statement is true about TRUNCATE TABLE?

    TRUNCATE is DDL. It is fast but commits and cannot be rolled back.

Summary

  • DDL statements (CREATE, ALTER, DROP, TRUNCATE) auto-commit in Oracle.
  • Use named constraints: PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK, NOT NULL.
  • ALTER TABLE uses ADD (...), MODIFY (...), RENAME COLUMN and DROP COLUMN.
  • Virtual columns compute values and take no space.
  • A dropped table sits in the recycle bin until purged; FLASHBACK TABLE can restore it.

Next: INSERT, UPDATE, DELETE and MERGE