Creating Tables

Basic Table Creation

CREATE TABLE customers (
  id         NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name       VARCHAR2(100) NOT NULL,
  email      VARCHAR2(150) NOT NULL UNIQUE,
  city       VARCHAR2(80),
  created_at DATE DEFAULT SYSDATE
);

VARCHAR2 is Oracle's text type. GENERATED BY DEFAULT AS IDENTITY auto-generates surrogate keys, the modern replacement for the older SEQUENCE + TRIGGER pattern.

Core Oracle Data Types

• VARCHAR2(n): variable-length text.
• NUMBER(p,s): exact decimal with precision p and scale s.
• DATE: date plus time down to seconds.
• TIMESTAMP: date with fractional seconds.
• CLOB: large character data.
• BLOB: binary data.

Managing Constraints

ALTER TABLE customers ADD CONSTRAINT chk_email CHECK (email LIKE '%@%');
ALTER TABLE orders ADD CONSTRAINT fk_order_customer FOREIGN KEY (customer_id) REFERENCES customers(id);

Describing a Table

DESC customers;
SELECT column_name, data_type FROM user_tab_columns WHERE table_name = 'CUSTOMERS';

Key Points

• VARCHAR2 and NUMBER are the workhorse types.
• IDENTITY columns replace sequences for primary keys.
• SYSDATE is the current server timestamp.
• user_tab_columns exposes metadata about your tables.