Creating and Modifying Oracle Table Structures

A table begins as a logical design, but Oracle needs an explicit database definition before it can store and protect business data. Data definition language (DDL) turns that design into schema objects by defining table names, columns, data types, defaults, constraints, indexes, and selected storage attributes. Module 7 follows that structural lifecycle from the first CREATE TABLE statement through later changes and, when appropriate, removal of the table.

The examples in this module use Oracle AI Database 26ai syntax. This distinction matters because a generic SQL example can hide important Oracle details. Oracle recommends VARCHAR2 for variable-length character data, provides its own table and storage clauses, and defines precise rules for constraint enforcement, dependencies, transaction boundaries, and dropped-object recovery.

The module concentrates on relational tables, the basic structures used to hold application data. Oracle also supports specialized table forms, but the same foundational questions apply: Which values can each column store? Which rules must the database enforce? Where will the table's data be stored? How can the definition change without violating existing data or dependent objects?

DDL statements create or change database definitions. CREATE, ALTER, and DROP are the principal DDL operations used in this module. Data manipulation language (DML) statements such as INSERT, UPDATE, DELETE, and MERGE operate on rows stored within those definitions. The distinction prevents a common vocabulary error: dropping a column is a structural operation, whereas deleting rows is a data operation.

DDL also affects transaction boundaries. Ordinary schema-object DDL implicitly commits the current transaction before the DDL executes. When the statement succeeds, Oracle commits the resulting definition change. A failed DDL statement does not restore unrelated work that was committed before the attempt. For that reason, do not casually test table-definition statements in a session containing uncommitted business changes.

Three statements establish the basic lifecycle. Each statement supports many clauses, but its structural purpose remains consistent.

Statement	Structural purpose	Important qualification
CREATE TABLE	Creates a table definition, its columns, and optional constraints or properties.	The table is empty unless an AS subquery clause supplies rows.
ALTER TABLE	Changes an existing table, including columns, constraints, partitions, and table properties.	Whether a change is legal can depend on existing data, indexes, constraints, partitions, and dependent objects.
DROP TABLE	Removes a table definition and all of its rows.	An eligible table normally enters the recycle bin unless PURGE is specified.

Oracle AI Database 26ai also supports existence checks in these statement families, including CREATE TABLE IF NOT EXISTS, ALTER TABLE IF EXISTS, and DROP TABLE IF EXISTS. These forms can make deployment scripts more repeatable, but they do not weaken privilege requirements, resolve an incompatible existing definition, or make destructive SQL safe without review.

Indexes have their own DDL statements: CREATE INDEX, ALTER INDEX, and DROP INDEX. An index can provide an access path and can help Oracle enforce some constraints, but an index is not itself a business rule. This module therefore treats table constraints and index structures as related but distinct concepts.

A basic relational-table definition supplies a table name and a parenthesized list of relational properties. At minimum, a conventional table definition identifies its columns and their data types. Defaults and constraints can be included so the database understands more of the business rules from the moment the table is created.

CREATE TABLE employees_demo (
    employee_id   NUMBER(6),
    first_name    VARCHAR2(40 CHAR),
    last_name     VARCHAR2(50 CHAR)
        CONSTRAINT employees_demo_last_nn NOT NULL,
    email         VARCHAR2(254 CHAR),
    hire_date     DATE DEFAULT SYSDATE,
    salary        NUMBER(10,2),
    CONSTRAINT employees_demo_pk PRIMARY KEY (employee_id),
    CONSTRAINT employees_demo_email_uk UNIQUE (email),
    CONSTRAINT employees_demo_salary_ck CHECK (salary >= 0)
);

The unquoted name employees_demo follows normal Oracle identifier behavior. Unquoted identifiers are case-insensitive in SQL and are represented in normalized uppercase form in the data dictionary. Quoted mixed-case names are legal in many contexts, but they require exact double-quoted spelling in later statements and are usually unnecessary for ordinary application objects.

Each column definition begins with a name and an Oracle data type. NUMBER(6) permits up to six digits of numeric precision. NUMBER(10,2) permits ten digits of precision with two digits to the right of the decimal point. Precision and scale should represent the business domain; the salary definition is an example, not a universal model for monetary values.

VARCHAR2(40 CHAR) and VARCHAR2(50 CHAR) store variable-length character data with explicit character-length semantics. Oracle advises using VARCHAR2 rather than VARCHAR. Although the two names are currently synonymous, Oracle reserves the possibility of redefining VARCHAR in a future release. A VARCHAR2 declaration must include a size.

Oracle DATE stores a date and time through whole seconds. Applications that require fractional seconds or additional timestamp capabilities can use an appropriate TIMESTAMP type. The clause DEFAULT SYSDATE supplies the current database date when an insert omits hire_date or explicitly requests the default. It does not prohibit an explicit null; add NOT NULL when the business rule requires a value in every row.

The example creates an empty table. Oracle can also create a table from a query with CREATE TABLE ... AS subquery, commonly called CTAS. That operation combines definition and population, but its handling of column definitions, defaults, indexes, and constraints differs from a fully declared table. A later lesson should address those details before CTAS is used as a substitute for an explicit production design.

A constraint is a database-enforced integrity rule that restricts values. Application validation remains useful for clear user feedback, but it cannot protect a table from every application, integration, script, loading tool, or administrative session. A database constraint applies the rule at the shared point where all of those writers meet.

Constraint	Rule enforced	Null or structural behavior
NOT NULL	The column must contain a value.	It must be declared inline as part of its column definition.
PRIMARY KEY	Each row has a unique, non-null identifier.	A table has one primary key, which can consist of one or several columns.
UNIQUE	Non-null key values or combinations cannot be duplicated.	Nulls are permitted according to Oracle's unique-key null semantics.
FOREIGN KEY	A child key must correspond to a referenced parent or unique key.	The child columns can be null unless another constraint prohibits nulls.
CHECK	A row must not make the specified condition false.	Use NOT NULL separately when the condition must also reject unknown results caused by nulls.

Constraints can be written inline with a column or out of line at table level. NOT NULL must be inline. Other common constraints can be inline or out of line, while a composite key must be declared out of line because it names several columns. Explicit names such as employees_demo_pk make maintenance statements and error investigation clearer than system-generated names such as SYS_Cn.

An enabled primary key or unique constraint needs an index structure for enforcement. Oracle may use a suitable existing index or create one. This relationship does not make constraints and indexes interchangeable: the constraint declares the rule, while the index is a physical access structure used to support enforcement and potentially improve retrieval.

A foreign key does not automatically create an index on its child columns. Such an index is often useful for joins and can affect locking and concurrency when parent keys change, but its value depends on the application's access patterns and operations. It should be designed and measured rather than presented as an automatic side effect of the constraint.

Oracle also provides constraint states such as ENABLE, DISABLE, VALIDATE, NOVALIDATE, DEFERRABLE, and RELY. Those states control enforcement, existing-data validation, transaction timing, and optimizer trust in different ways. Disabling a constraint is not the same as dropping it: a disabled definition can remain in the data dictionary, whereas dropping the constraint removes its definition.

Requirements change after deployment. ALTER TABLE can add, modify, rename, make unused, or drop columns and can manage constraints, partitions, storage, and other table properties. The following examples introduce common column and constraint changes without attempting to reproduce the statement's complete grammar.

ALTER TABLE employees_demo
    ADD (department_id NUMBER(4));

ALTER TABLE employees_demo
    MODIFY (first_name VARCHAR2(60 CHAR));

ALTER TABLE employees_demo
    ADD CONSTRAINT employees_demo_dept_fk
        FOREIGN KEY (department_id)
        REFERENCES departments_demo (department_id);

The first statement adds a nullable column, so existing rows do not need an immediate department value. The second widens a character column, which is a clearer introductory modification than changing an identifier directly from numeric to character data. The foreign-key statement assumes that departments_demo already exists and that department_id is a suitable referenced primary or unique key.

A column modification is not automatically safe because its syntax parses. Existing values must fit the new definition, and defaults, constraints, indexes, generated expressions, partitions, and other dependencies can restrict the operation. Decreasing a character length, changing a data type, or adding a non-null rule to a populated table requires data and dependency analysis before the DDL is executed.

Oracle uses DROP COLUMN to remove a column; DELETE COLUMN is not the statement. Dropping a column can affect indexes, constraints, views, PL/SQL, application queries, reports, and integrations that reference it. Production DDL therefore belongs in a reviewed, tested deployment plan with an appropriate recovery strategy.

A table is a logical schema object. When storage is allocated for its rows, its segment is made of extents, and each extent consists of Oracle data blocks. The containing tablespace is a logical storage container backed by one or more physical datafiles. These relationships can be summarized as follows:

• table -> segment -> extents -> Oracle data blocks
• tablespace -> one or more datafiles

The two chains describe different dimensions. The first follows storage allocated to a schema object; the second connects a logical database storage container to operating-system or managed-storage files. Many table and index segments can share one tablespace and its datafiles. A table does not receive a dedicated datafile merely because it was created.

Segment creation can also be deferred, so an empty table does not necessarily receive its segment immediately. Oracle may create the segment when the first row is inserted or when another operation requires storage. This distinction is important when interpreting data dictionary views: a table definition can exist even though no table segment has yet been allocated.

Later lessons can examine tablespace placement, extent allocation, block use, compression, logging, partitioning, and other physical properties. Modern databases usually rely on locally managed tablespaces and automatic segment-space management rather than a manual storage formula for every table. Cloud and managed services can automate or restrict additional parts of the physical storage layer.

A table belongs to a schema. To create a relational table in your own schema, you need the CREATE TABLE system privilege, and the schema owner needs an appropriate tablespace quota or the UNLIMITED TABLESPACE system privilege. Altering or dropping another schema's table requires additional object or system privileges. Broad privileges such as ALTER ANY TABLE and DROP ANY TABLE should be restricted because they authorize structural changes beyond the current schema.

Successful DDL records the definition in Oracle's data dictionary. Querying dictionary views verifies what Oracle created instead of relying on a script, diagram, or success message as the final description. The following query reviews the common column properties of the demonstration table. Dictionary names for unquoted identifiers are normally supplied in uppercase.

SELECT column_id,
       column_name,
       data_type,
       data_length,
       char_used,
       nullable,
       data_default
FROM   user_tab_columns
WHERE  table_name = 'EMPLOYEES_DEMO'
ORDER  BY column_id;

Constraint definitions can be inspected separately. STATUS indicates whether a constraint is enabled, while VALIDATED reports whether existing data has been validated under the constraint state. The values should be interpreted together rather than reduced to a single “constraint exists” test.

SELECT constraint_name,
       constraint_type,
       status,
       validated
FROM   user_constraints
WHERE  table_name = 'EMPLOYEES_DEMO'
ORDER  BY constraint_name;

These USER_ views describe objects owned by the current user. Broader ALL_, DBA_, or container-aware views serve different administrative scopes and require suitable privileges. Later lessons can select the view that matches the user's responsibility and the database's multitenant context.

DROP TABLE removes a table definition and its rows. Oracle also removes indexes and triggers defined on the table and revokes object privileges on it; dependent objects can become invalid. This is materially different from using DELETE to remove selected rows or TRUNCATE TABLE to remove all rows while retaining the table definition.

DROP TABLE employees_demo;

Without PURGE, an eligible ordinary table normally moves to the recycle bin. Its allocated space can continue to count against the owner's quota until the object is purged or Oracle reclaims the space. Recycle-bin retention can make flashback recovery possible, but it should not replace backups, change control, or confirmation that dependent applications no longer need the table.

DROP TABLE employees_demo CASCADE CONSTRAINTS PURGE;

The second form is intentionally destructive. CASCADE CONSTRAINTS removes referential constraints in other tables that depend on primary or unique keys in the dropped table. It does not mean the same thing as ON DELETE CASCADE, which governs row deletion through a foreign key. PURGE bypasses recycle-bin recovery. Use both clauses only when their consequences have been reviewed and are intended.

By the end of Module 7, you should be able to:

1. Identify the essential components of an Oracle CREATE TABLE statement.
2. Select appropriate Oracle data types and define column defaults.
3. Explain how tables, segments, extents, blocks, tablespaces, and datafiles relate.
4. Distinguish modern automatic storage management from explicit storage clauses.
5. Explain the integrity benefits of named primary key, foreign key, unique, check, and NOT NULL constraints.
6. Distinguish constraints from the indexes that may enforce or support them.
7. Add and modify columns and constraints with ALTER TABLE.
8. Predict the effects of disabling, dropping, or validating a constraint.
9. Explain the effects and recovery implications of DROP TABLE, CASCADE CONSTRAINTS, and PURGE.

Table DDL is the bridge between a logical data model and enforceable Oracle schema objects. CREATE TABLE defines columns and initial rules, ALTER TABLE evolves the definition, and DROP TABLE removes the object when its lifecycle ends. Constraints keep core business rules close to the data, while indexes and storage structures support enforcement, access, and physical organization.

The remainder of Module 7 develops these concepts in detail. The next lesson begins with the components of CREATE TABLE, building from the table name and column list toward the constraints and storage choices needed for a maintainable Oracle AI Database 26ai design.