CREATE TABLE Syntax in Oracle AI Database 26ai

Lesson 1 introduced the lifecycle of an Oracle table. This lesson examines the first step in that lifecycle: converting a logical table design into a relational table definition with CREATE TABLE. The statement names the table, defines its columns and data types, and can establish defaults and integrity constraints before the first row is stored.

The complete Oracle grammar supports relational, object, XML, JSON collection, temporary, partitioned, immutable, blockchain, and other table forms. A useful introduction should not reproduce that entire grammar. This lesson concentrates on the common relational-table form used by application schemas and establishes the vocabulary required for the more specialized clauses discussed later in Module 7.

The syntax and terminology here target Oracle AI Database 26ai and follow the July 30, 2026 Oracle AI Database SQL Language Reference. The goal is not merely to produce a statement that parses. Each column, default, and constraint should represent a deliberate fact or business rule from the underlying data model.

A relational table is a schema object and the basic Oracle structure for holding application data. Executing CREATE TABLE records the table definition in the data dictionary. The definition identifies the table's owner, its columns, the data domain of each column, and any initial rules or physical properties included in the statement.

An ordinary CREATE TABLE statement creates the definition without inserting rows. Oracle can create and populate a table from a query by using an AS subquery clause, commonly called CTAS, but that is a different form with important rules for inferred column definitions, constraints, defaults, and indexes. This lesson begins with an explicitly defined empty table so every component remains visible.

Creating the logical object also does not guarantee that Oracle immediately allocates a table segment. With deferred segment creation, the table can exist in the data dictionary before its first data segment is allocated. Later lessons connect the table to segments, extents, data blocks, tablespaces, and datafiles; those storage structures are related to the definition but are not the table's logical identity.

The following teaching grammar shows the essential relational-table form. Square brackets mean that an element is optional, and the ellipsis means that the pattern can repeat. Neither the brackets nor the ellipsis is typed in an executable Oracle statement.

CREATE TABLE [schema.]table_name (
    column_name datatype [DEFAULT expression] [inline_constraint],
    ...,
    [out_of_line_constraint]
);

Component	Purpose	Important Oracle qualification
CREATE TABLE	Begins the DDL statement that creates the schema object.	Ordinary schema-object DDL affects transaction boundaries.
[schema.]table_name	Identifies the table and, optionally, the schema that will contain it.	If the schema is omitted, Oracle creates the table in the current user's schema.
column_name datatype	Names a column and defines the domain of values it can store.	Every ordinary stored column requires a name and data type.
DEFAULT expression	Supplies a value when an insert omits the column or explicitly requests its default.	An ordinary default does not replace an explicitly supplied null.
Inline constraint	Declares an integrity rule as part of one column definition.	NOT NULL must be declared inline.
Out-of-line constraint	Declares a named rule as a separate element of the table definition.	Composite keys must use this form because they identify multiple columns.

The opening parenthesis begins a comma-separated list of relational properties, and the closing parenthesis ends that list. A comma belongs between adjacent column definitions or constraints; it is not part of a column name or data type. The semicolon terminates the complete SQL statement in clients that use it as a statement delimiter.

The following statement defines a table for the House-O-Pets course project. It uses unquoted identifiers, current Oracle data types, named constraints, and explicit character-length semantics. It is complete and executable in a schema that has the required privilege and quota.

CREATE TABLE customer_sale (
    sales_id          NUMBER(10),
    cust_id           NUMBER(10)
        CONSTRAINT customer_sale_cust_nn NOT NULL,
    total_item_amount NUMBER(10,2) DEFAULT 0
        CONSTRAINT customer_sale_item_nn NOT NULL,
    tax_amount        NUMBER(10,2) DEFAULT 0
        CONSTRAINT customer_sale_tax_nn NOT NULL,
    total_sale_amount NUMBER(10,2),
    sales_date        DATE DEFAULT SYSDATE,
    shipping_method   VARCHAR2(25 CHAR) DEFAULT 'UPS',
    CONSTRAINT customer_sale_pk PRIMARY KEY (sales_id),
    CONSTRAINT customer_sale_item_ck CHECK (total_item_amount >= 0),
    CONSTRAINT customer_sale_tax_ck CHECK (tax_amount >= 0)
);

Because the statement omits a schema qualifier, Oracle creates customer_sale in the current user's schema. Unquoted identifiers are case-insensitive in SQL and appear in normalized uppercase form in data dictionary views. Quoted mixed-case identifiers are legal in many contexts, but they require exact double-quoted spelling in later statements and add unnecessary complexity to a foundational example.

Each column begins with a name and data type. NUMBER(10) allows up to ten digits of decimal precision. NUMBER(10,2) allows ten digits of precision, with two digits to the right of the decimal point. Precision and scale limit stored values; they do not calculate the sale. This lesson therefore does not invent a formula for total_sale_amount, which could depend on discounts, shipping, fees, refunds, and rounding rules not supplied by the data model.

VARCHAR2(25 CHAR) stores variable-length text and explicitly measures the declared length in characters. Oracle also permits BYTE semantics. Stating the unit prevents the definition from silently depending on the session's NLS_LENGTH_SEMANTICS setting and makes the intended business limit easier to review in a multibyte database character set.

The ordinary defaults of zero, SYSDATE, and 'UPS' apply when an insert omits their columns or explicitly uses the DEFAULT keyword. They do not replace an explicitly supplied null. The item and tax columns also have named NOT NULL constraints, so null is prohibited there. The date and shipping columns remain nullable because the example does not declare them NOT NULL. A default and a nullability rule answer different questions.

The primary key and check constraints are written out of line. The primary key makes sales_id unique and non-null. The check constraints reject negative item and tax amounts. The separate names make the rules recognizable in error messages and maintenance DDL. The column cust_id is not given a foreign key yet because this lesson has not established the exact parent table and referenced key. A later constraint lesson can add that relationship once its prerequisite is explicit.

A data type determines how Oracle represents a value and which operations are meaningful for it. The following table summarizes types relevant to an introductory relational design. It is intentionally selective; Oracle also supports many specialized, user-defined, spatial, object-relational, XML, and domain types.

Data type	Appropriate use	Key qualification
VARCHAR2(n CHAR) or VARCHAR2(n BYTE)	Variable-length text in the database character set.	A size is required; state the intended character or byte semantics.
CHAR(n CHAR) or CHAR(n BYTE)	Text whose domain is genuinely fixed-width.	Oracle uses fixed-length, blank-padded storage semantics.
NUMBER(p,s)	Exact decimal values.	Precision ranges from 1 through 38; choose precision and scale from the business domain.
DATE	Calendar date and time through whole seconds.	It has neither fractional seconds nor a time-zone component.
TIMESTAMP family	Date-time values requiring fractional seconds or time-zone behavior.	Select the specific timestamp type according to the required semantics.
CLOB or NCLOB	Large character content.	Use these instead of creating new LONG text columns.
BLOB	Large binary content managed by the database.	Use it instead of creating new LONG RAW columns.
RAW(n)	Bounded byte strings.	Oracle does not apply character-set conversion to the stored bytes.
BFILE	A locator for binary content stored outside the database.	The external file bytes are not stored inside the table row.
JSON	Native JSON documents.	Use it when the data model genuinely requires JSON storage and operations.
BOOLEAN	SQL truth values.	It supports true, false, and null unless a NOT NULL constraint prohibits null.
VECTOR	Vector embeddings used by AI Vector Search.	It is not a substitute for an ordinary collection of unrelated numeric values.

Oracle explicitly recommends VARCHAR2 rather than VARCHAR. Under MAX_STRING_SIZE=STANDARD, a SQL VARCHAR2 column has a maximum of 4000 bytes; under EXTENDED, the maximum can be 32767 bytes. A character-based declaration remains subject to the applicable byte maximum, so “characters” and “guaranteed storage bytes” must not be treated as interchangeable.

Oracle DATE stores year, month, day, hour, minute, and second. Use an appropriate TIMESTAMP type when fractional seconds or time-zone behavior forms part of the business requirement. Applications should also use explicit conversion functions and format models when converting text to dates or numbers instead of relying on session-dependent implicit conversion.

LONG and LONG RAW remain available for backward compatibility, but they should not be selected for new table designs. LONG stores character data, not a generic binary file. Current designs use LOB types for large character or binary values because LOBs provide current functionality and avoid many restrictions attached to the legacy types.

A constraint is a database-enforced rule that restricts stored values. Data types define broad domains, while constraints express requirements such as mandatory values, key uniqueness, parent-child relationships, and acceptable ranges. Application validation can improve the user experience, but database constraints protect the rule across every application, script, integration, loading process, and administrative session that writes to the table.

Constraint	Rule	Declaration note
NOT NULL	The column must contain a value.	It must be declared inline as part of its column definition.
PRIMARY KEY	The key is unique and non-null.	A table has one primary key; a composite primary key is out of line.
UNIQUE	Non-null key values or combinations cannot be duplicated.	It can be inline or out of line; composite keys require the latter.
FOREIGN KEY	A non-null child key must match a referenced primary or unique key.	It can be inline or out of line; child columns remain nullable unless separately constrained.
CHECK	The condition must not evaluate to false.	Add NOT NULL separately when null must also be rejected.

An enabled primary key or unique constraint requires an index structure for enforcement. Oracle may create an index or use a suitable existing one. The logical rule and physical access structure are not interchangeable: the constraint defines integrity, while the index supports access and enforcement. A foreign key does not automatically create an index on its child columns.

A schema-qualified name identifies the schema that will contain the table. The following example names sales_app as the owner. It is shown to explain the syntax, not to recommend routine cross-schema object creation.

CREATE TABLE sales_app.customer_sale (
    sales_id NUMBER(10)
);

Creating a relational table in one's own schema requires the CREATE TABLE system privilege. Creating it in another user's schema requires CREATE ANY TABLE. The schema owner must also have quota on the target tablespace or the UNLIMITED TABLESPACE system privilege. Merely placing a schema name before the table name does not supply ownership authority or storage quota.

Application deployments generally create objects while connected as the intended schema owner or through a controlled deployment identity. Broad ANY privileges should not be normalized merely to make examples convenient. Privilege design and tablespace placement are part of the database's security and resource-management model.

Oracle AI Database 26ai supports CREATE TABLE IF NOT EXISTS. If the named table is absent, Oracle creates it. If the table already exists, Oracle leaves that table in place rather than creating a replacement.

CREATE TABLE IF NOT EXISTS ddl_practice (
    practice_id NUMBER(10)
);

This form can make a controlled setup script repeatable, but it is not schema migration or drift detection. Oracle does not compare an existing ddl_practice table with the requested columns, data types, defaults, constraints, or storage properties and then reconcile the differences. Deployment tooling must still verify that an existing definition is the intended definition.

The ordinary CREATE TABLE form remains valuable while learning and during reviewed deployments because an object-existence error can reveal an unexpected state. Suppressing that error without checking the existing object could allow an incompatible schema to remain unnoticed.

After creating customer_sale, query the data dictionary rather than assuming that a client display or generated script represents the final definition. USER_TAB_COLUMNS reports columns owned by the current user.

SELECT column_id,
       column_name,
       data_type,
       data_precision,
       data_scale,
       char_length,
       char_used,
       nullable,
       data_default
FROM   user_tab_columns
WHERE  table_name = 'CUSTOMER_SALE'
ORDER  BY column_id;

The uppercase search value reflects how Oracle records an ordinary unquoted object name. CHAR_USED helps distinguish character from byte length semantics, while NULLABLE and DATA_DEFAULT show two independent parts of the column definition. Displayed metadata depends on the statement actually executed and the database configuration, so the lesson does not invent a fixed result set.

Use USER_CONSTRAINTS to inspect the named integrity rules:

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

In this view, P identifies a primary key, C includes check and NOT NULL constraints, U identifies a unique constraint, and R identifies a referential constraint. The example has no R row because it deliberately postpones the foreign key until the referenced parent table and key are established.

Ordinary schema-object DDL implicitly commits the current transaction before Oracle executes the DDL. When the statement completes successfully, Oracle commits the definition change. A failure while creating the table does not restore unrelated DML that the pre-DDL commit already made permanent.

Do not experiment with CREATE TABLE in a session that contains uncommitted business changes. Use a controlled development schema, review the intended owner and tablespace, and verify the result through the data dictionary. These practices matter even when a graphical tool or deployment framework generates the SQL, because generated DDL still represents structural and transactional decisions.

A basic relational CREATE TABLE statement combines a schema-object name with a parenthesized, comma-separated list of column definitions and constraints. Each ordinary column has a name and data type. A default can supply a value under documented insert conditions, while a constraint enforces an integrity rule. Inline and out-of-line forms determine where those rules appear in the table definition.

The customer_sale example demonstrates current Oracle syntax without hiding undefined prerequisites. It uses purposeful numeric and character definitions, distinguishes defaults from nullability, names its constraints, and postpones the foreign key until the referenced parent key is known. Privileges and quota determine whether and where the table can be created, and data dictionary views reveal the definition Oracle actually recorded.

Later Module 7 lessons extend this foundation with tablespace and storage clauses, indexes, more detailed constraint syntax, table alteration, and object removal. Those features add capabilities to the statement, but they do not change its central design obligation: the table definition must translate verified business rules into durable database structure.