Oracle Database / Basic: relational and transaction foundations / Lesson 2

Types, missing values and integrity constraints

Represent freight facts with Oracle-specific numeric, character and temporal semantics.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Choose exact types and explicit presence rules.
Explain empty strings, CHECK and generated identity.

Choose types from the business contract

A tracking code is text even when it contains only digits: leading zeros carry meaning and arithmetic does not. NUMBER(9,2) is suitable for a bounded exact charge, whereas binary floating point introduces approximate representation. VARCHAR2 should be used deliberately for variable-length database character data. State BYTE or CHAR length semantics when international names matter, because a character can occupy multiple bytes in the database character set. Do not assume a declaration inherited from a developer session has the same NLS length setting on every migration runner.

Empty text is currently NULL

Oracle currently treats a zero-length character string as NULL. Consequently, a mandatory VARCHAR2 column cannot store an empty string as a separate known value. A single space is not the same thing, and silently substituting spaces hides data quality problems. NULL means a missing or unknown value, so ordinary equality with NULL is unknown rather than true. WHERE retains true predicates only. Use IS NULL for absence and make any defaulting rule explicit. The course targets established 19c SQL semantics; newer releases and noncharacter JSON values need their own documented rules.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

SELECT CASE WHEN '' IS NULL THEN 'missing' ELSE 'present' END AS empty_text,
       CASE WHEN ' ' IS NULL THEN 'missing' ELSE 'present' END AS space_text
FROM dual;

Expected output

EMPTY_TEXT | SPACE_TEXT
missing    | present

The zero-length string is NULL, while one space remains a character value.

Separate four guarantees

The table definition separates identity generation, uniqueness, required presence and allowed range. Identity provides surrogate values but PRIMARY KEY expresses uniqueness. CHECK (charge >= 0) does not reject NULL because an unknown check result is permitted; NOT NULL supplies that missing rule. A UNIQUE tracking code is a separate business key. Constraints guard every writer, including a future import script, so they should not disappear merely because a web form validates the same fields. The example is a definition for a disposable teaching schema, not an instruction to alter an existing application.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

CREATE TABLE shipments (
  shipment_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tracking_code VARCHAR2(24 CHAR) NOT NULL UNIQUE,
  charge NUMBER(9,2) NOT NULL CHECK (charge >= 0),
  dispatched_at TIMESTAMP WITH TIME ZONE
);

Identity columns are available from 12c. Values are identifiers and need not be consecutive; table creation is DDL with implicit-commit consequences.

DATE includes a clock component

Oracle DATE stores year through second, not merely a calendar day. TIMESTAMP adds fractional seconds; TIMESTAMP WITH TIME ZONE represents time with zone information, while WITH LOCAL TIME ZONE normalizes storage and displays according to the session zone. Choose from the event meaning rather than from display formatting. A DATE literal such as DATE '2026-10-01' is independent of NLS_DATE_FORMAT and denotes midnight. Comparing a timestamp containing an afternoon time to that literal by equality misses the row. Later filtering lessons use half-open ranges to express entire days correctly.

Model relationships and optionality together

A foreign key points to a parent primary or unique key and prevents orphan references. A nullable child key still permits an absent relationship; add NOT NULL when every shipment requires a customer. Review child-side indexing for joins and parent-key changes instead of assuming the foreign key automatically creates a suitable index. Choose delete behavior from the lifecycle: deleting a customer may need to be forbidden while historical shipments remain. Do not turn referential integrity off to make an import appear successful; quarantine invalid records and repair their provenance.

Common errors and fixes

ORA-01400

Why it happens: A required field received NULL, including an empty VARCHAR2 string.
How to fix it: Provide a meaningful value or explicitly revise the optionality contract.
Prevent it next time: Test empty input after trimming and preserve the source rejection reason.

ORA-02290

Why it happens: The new row violates a named CHECK constraint.
How to fix it: Inspect the exact domain rule and repair the source value.
Prevent it next time: Name constraints and test boundaries, including zero and missing values.

ORA-12899

Why it happens: A character value exceeds the declared byte or character capacity.
How to fix it: Compare encoded length with the actual column semantics.
Prevent it next time: Specify international text limits deliberately in schema reviews.

Check your understanding

1. A nonnegative charge column has only CHECK (charge >= 0). Can an unknown charge be inserted?

Show worked solution

Yes. The check can evaluate to unknown and still permit the row. Add NOT NULL if the charge is mandatory. Keep the numeric precision and business bound separate from this presence decision; neither a default nor a client-side required field replaces the stored constraint.

2. An identity key jumps from 40 to 43 after failed work. Is that evidence of missing shipments?