SQL Guide Chapter

15 – Managing Schema Objects (Or: Teaching Your Constraints To Chill Out)

And look, once your database has been alive for more than about five minutes, people start saying things like “we just need one tiny schema change,” which is how you end up spending your weekend negotiating with constra...

oracle 19c mysql notes study chapter

And look, once your database has been alive for more than about five minutes, people start saying things like “we just need one tiny schema change,” which is how you end up spending your weekend negotiating with constraints, temporary tables, and external files that absolutely weren’t documented.

This lesson is about staying in control of that chaos:

1. Managing Constraints Without Losing Your Mind

Constraints are the grumpy hall monitors of your schema: they don’t let bad data through, and they absolutely do not care about your deadline.

You need to be able to:

1.1 Adding constraints with ALTER TABLE

For everything except NOT NULL, you use ALTER TABLE … ADD:

ALTER TABLE emp2
      ADD CONSTRAINT emp2_mgr_fk
          FOREIGN KEY (manager_id)
          REFERENCES emp2 (employee_id);

Notes:

For a NOT NULL constraint, you must use MODIFY because it lives at the column level:

ALTER TABLE emp2
      MODIFY last_name CONSTRAINT emp2_lastname_nn NOT NULL;

1.2 Dropping constraints

When a constraint has outlived its usefulness (or was a terrible idea to begin with):

ALTER TABLE emp2
      DROP CONSTRAINT emp2_mgr_fk;

Dropping a primary key and all its dependent foreign keys in one dramatic gesture:

ALTER TABLE emp2
      DROP PRIMARY KEY CASCADE;

Add ONLINE if you want DML to keep flowing while the constraint is being dropped:

ALTER TABLE emp2
      DROP PRIMARY KEY CASCADE ONLINE;

1.3 ON DELETE CASCADE vs ON DELETE SET NULL

This is where you decide how much collateral damage is acceptable when parents disappear.

Cascade: delete children when the parent goes

ALTER TABLE emp2
      ADD CONSTRAINT emp2_dept_fk
          FOREIGN KEY (department_id)
          REFERENCES departments (department_id)
          ON DELETE CASCADE;

Set null: orphans keep living, just confused

ALTER TABLE emp2
      ADD CONSTRAINT emp2_dept_fk
          FOREIGN KEY (department_id)
          REFERENCES departments (department_id)
          ON DELETE SET NULL;

1.4 Dropping columns with attached constraints

If a column has PRIMARY/UNIQUE keys or foreign keys pointing at it, dropping it is like removing a Jenga block from the bottom row.

Use CASCADE CONSTRAINTS so Oracle also drops any constraints that depend on that column:

ALTER TABLE emp2
      DROP COLUMN employee_id CASCADE CONSTRAINTS;

2. Enabling, Disabling, and Deferring Constraints

There are days when you want your constraints to back off a bit so you can do a bulk load, data migration, or “creative repair”.

2.1 Basic enable/disable

Temporarily turning constraint checks off:

ALTER TABLE emp2
      DISABLE CONSTRAINT emp2_dept_fk;

Re‑arming it later:

ALTER TABLE emp2
      ENABLE CONSTRAINT emp2_dept_fk;

Disable all foreign keys hanging off a primary key:

ALTER TABLE emp2
      DISABLE PRIMARY KEY CASCADE;

When you ENABLE a PRIMARY or UNIQUE key, Oracle automatically (re)creates the supporting unique index, because that’s what actually enforces uniqueness.

2.2 Validate vs NOVALIDATE

You get to choose whether enabling a constraint:

Example:

-- Check all existing rows and future rows
    ALTER TABLE dept2
      ENABLE VALIDATE PRIMARY KEY;
    
    -- Trust existing data (maybe foolishly), check only new rows
    ALTER TABLE dept2
      ENABLE NOVALIDATE PRIMARY KEY;

If you know your data is terrible but fixing it will take ages, NOVALIDATE is the “we’ll deal with this later” option.

2.3 Deferrable constraints (complain later, not now)

Deferrable constraints let you say, “yes, I know this looks wrong mid‑transaction, but I promise it’ll be fine by commit.”

Define a deferrable primary key:

CREATE TABLE demo (
      id   NUMBER
           CONSTRAINT demo_pk
           PRIMARY KEY
           DEFERRABLE INITIALLY DEFERRED,
      name VARCHAR2(25)
    );

Behavior:

Example pattern:

-- This may temporarily violate the constraint…
    INSERT INTO demo VALUES (1, 'First');
    INSERT INTO demo VALUES (1, 'Duplicate');  -- allowed for now
    
    COMMIT;  -- BOOM: ORA-00001, transaction rolled back

You can switch the mode for the whole session:

ALTER SESSION SET CONSTRAINTS = IMMEDIATE;  -- behave like normal
    ALTER SESSION SET CONSTRAINTS = DEFERRED;   -- check at COMMIT

Immediate mode: constraint checked per statement. Deferred mode: constraint checked at commit, and a single failure rolls back the whole transaction.

3. Temporary Tables: Shopping Carts For Data

And look, not every intermediate result deserves a permanent table and a place in the ERD. Sometimes you just need a scratchpad that magically cleans itself up.

That’s what temporary tables are for.

Key points:

3.1 Global temporary tables

Classic pattern:

CREATE GLOBAL TEMPORARY TABLE cart_items (
      session_id  VARCHAR2(30),
      item_id     NUMBER,
      qty         NUMBER
    ) ON COMMIT DELETE ROWS;

Options:

Remember:

3.2 Private temporary tables

Private temp tables are like global temp tables that went into witness protection:

Examples:

-- Transaction-specific private temp table
    CREATE PRIVATE TEMPORARY TABLE ora$ptt_txn_buffer
    ON COMMIT DROP DEFINITION AS
    SELECT * FROM employees WHERE department_id = 10;
    
    -- Session-specific private temp table
    CREATE PRIVATE TEMPORARY TABLE ora$ptt_session_buffer
    ON COMMIT PRESERVE DEFINITION AS
    SELECT * FROM employees WHERE department_id = 20;

In both cases, once your session ends, the table definition and data are gone. Which is great, unless you forgot to copy out the data you needed, in which case… oops.

4. External Tables: When Your Data Refuses To Live In The Database

Sometimes data stubbornly exists in files because someone loves CSVs, or there’s an ETL process that insists on spitting out flat files like it’s 1997.

External tables let you:

4.1 The moving parts

  1. Oracle DIRECTORY object – a pointer to a filesystem path:
   CREATE OR REPLACE DIRECTORY emp_dir AS '/home/oracle/labs/sql2/emptor';
       GRANT READ ON DIRECTORY emp_dir TO ora21;
  1. External table – a table definition that describes file layout.

4.2 External table with ORACLE_LOADER

Typical “read a CSV‑ish text file” example:

CREATE TABLE ext_books (
      category_id  NUMBER,
      book_id      NUMBER,
      book_price   NUMBER,
      quantity     NUMBER
    )
    ORGANIZATION EXTERNAL (
      TYPE ORACLE_LOADER
      DEFAULT DIRECTORY emp_dir
      ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        FIELDS TERMINATED BY ','
      )
      LOCATION ('library_items.dat')
    )
    REJECT LIMIT UNLIMITED;

Now you can:

SELECT category_id, book_id, quantity
    FROM   ext_books
    WHERE  book_price > 20;

Notes:

4.3 External table with ORACLE_DATAPUMP

ORACLE_DATAPUMP writes data into binary dump files for fast unload/reload.

Example: create an external table that’s backed by Data Pump files:

CREATE TABLE dept_ext
    ORGANIZATION EXTERNAL (
      TYPE ORACLE_DATAPUMP
      DEFAULT DIRECTORY emp_dir
      LOCATION ('dept_ext_1.exp', 'dept_ext_2.exp')
    ) AS
    SELECT department_id,
           department_name,
           manager_id,
           location_id
    FROM   departments;

This does two things:

  1. Creates the dept_ext table definition.
  2. Writes the query result into the listed .exp files.

Later, you can:

SELECT * FROM dept_ext;

and read directly from the dump files without touching the original DEPARTMENTS table.

5. Recycle Bin, PURGE, and “No, Really, Delete It”

By default, when you:

DROP TABLE big_table;

Oracle doesn’t really delete it. It:

Nice for “oops, I didn’t mean to drop that.” Less nice for “why am I out of space?”

If you’re sure you don’t need it back:

DROP TABLE big_table PURGE;

Now it’s gone from the database and the Recycle Bin, and you reclaim the space.

What You Should Be Able To Do Now

By this point, you should be able to:

In short: you now have the tools to control your schema objects instead of letting them control you – which, in database terms, is about as close to happiness as it gets.

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