Cheat sheet / Oracle DDL operations

List of basic commands for defining data structures.

Create table

CREATE TABLE <TABLE_NAME> (
  <COLUMN_NAME> <TYPE>
);

Add column

ALTER TABLE <TABLE_NAME> ADD (<COLUMN_NAME> <TYPE>);

Change data type

ALTER TABLE <TABLE_NAME>
  MODIFY <COLUMN_NAME> <NEW_TYPE>
;

NOT NULL constraint

ALTER TABLE <TABLE_NAME>
  MODIFY <COLUMN_NAME> <TYPE> NOT NULL
;

Foreign key

ALTER TABLE <TABLE_NAME>
  ADD CONSTRAINT <FK_NAME>
  FOREIGN KEY (<COLUMN_NAME>)
  REFERENCES <PARENT_TABLE> (<COLUMN_NAME>)
;

Index

CREATE INDEX <INDEX_NAME>
  ON <TABLE_NAME>(<COLUMN_NAME>)
;

Check constraint

ALTER TABLE <TABLE_NAME>
  ADD CONSTRAINT <CHK_NAME>
  CHECK (<COLUMN_NAME> IN (<CONDITION>))
;

Drop table

DROP TABLE <TABLE_NAME>;