DDL, Data Types, Constraints, Indexes and Views

Oracle DBA Certification 15 min min read Updated: Nov 16, 2026 Beginner
DDL, Data Types, Constraints, Indexes and Views
Beginner Topic 4 of 5

Common Data Types

TypeUseExample
VARCHAR2(n)Text up to n bytes or charactersVARCHAR2(60)
NUMBER(p,s)Numbers, p total digits, s after the decimalNUMBER(10,2)
DATEDate and time to the secondSYSDATE
TIMESTAMPDate and time with fractions of a secondSYSTIMESTAMP
CLOBLarge textDocuments, JSON text
BLOBLarge binary dataImages, files
Oracle quirk: Oracle treats an empty string '' as NULL. This surprises people coming from other databases.

Constraints

ConstraintRule
PRIMARY KEYUnique and not null. Identifies a row.
FOREIGN KEYValue must exist in another table
UNIQUENo duplicates (NULLs allowed)
NOT NULLA value is required
CHECKA custom rule, for example price > 0
sql CREATE TABLE employees ( emp_id NUMBER GENERATED ALWAYS AS IDENTITY, emp_name VARCHAR2(60) NOT NULL, salary NUMBER(10,2), dept_id NUMBER, CONSTRAINT pk_employees PRIMARY KEY (emp_id), CONSTRAINT ck_emp_salary CHECK (salary > 0) );
Tip: Always name your constraints (pk_employees). When one fails, the error message shows the name, which tells you instantly what went wrong.

ALTER TABLE

sql ALTER TABLE employees ADD (hire_date DATE DEFAULT SYSDATE); ALTER TABLE employees MODIFY (emp_name VARCHAR2(100)); ALTER TABLE employees RENAME COLUMN emp_name TO full_name; ALTER TABLE employees DROP COLUMN hire_date; ALTER TABLE employees ADD CONSTRAINT uq_emp_name UNIQUE (full_name);

Indexes

An index works like the index of a book. Instead of reading every page (full table scan), Oracle jumps to the right place.

sql CREATE INDEX idx_orders_customer ON orders (customer_id); -- see indexes on a table SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'ORDERS';
Note: Primary key and unique constraints create their indexes automatically. Foreign key columns are not indexed automatically, so index them yourself.

Sequences

sql CREATE SEQUENCE invoice_seq START WITH 1000 INCREMENT BY 1 CACHE 20; SELECT invoice_seq.NEXTVAL FROM dual; SELECT invoice_seq.CURRVAL FROM dual;

Identity columns (GENERATED ALWAYS AS IDENTITY) use a sequence behind the scenes, so most new tables do not need a manual sequence.

Views

A view is a saved query that behaves like a table. Use it to hide complexity or restrict which columns people see.

sql CREATE OR REPLACE VIEW v_order_details AS SELECT o.order_id, c.full_name, p.product_name, o.quantity, o.status FROM orders o JOIN customers c ON c.customer_id = o.customer_id JOIN products p ON p.product_id = o.product_id; SELECT * FROM v_order_details WHERE status = 'NEW';

Drop and Truncate

CommandEffectRollback?
DELETE FROM tRemoves rows one by oneYes, until commit
TRUNCATE TABLE tRemoves all rows instantly, resets spaceNo
DROP TABLE tRemoves the table (goes to the recycle bin)Can be restored with FLASHBACK

Conclusion

Pick the right data type, name your constraints, index foreign keys and use views to simplify queries. Remember that DDL commits automatically.

Quick Summary: Constraints protect data, indexes speed up reads, sequences and identity create ids, views save queries.

Get Newsletter

Subscibe to our newsletter and we will notify you about the newest updates on Edugators