Data Definition Language

Data Definition Language (DDL) in databases comprises a set of commands used to define the structure and properties of data within a database. These commands play a crucial role in creating, modifying, and deleting database objects.

Key DDL Commands:

DEFAULT: Sets a default value for a column.
CONSTRAINT name_PK PRIMARY KEY (fieldname): Establishes a primary key constraint to uniquely identify rows in a table.
CONSTRAINT name_FK FOREIGN KEY (fieldname): Enforces referential integrity by linking one table’s key to another table’s key.
CONSTRAINT name_NN NOT NULL (fieldname): Ensures that a column cannot contain NULL values.
CONSTRAINT name_CK CHECK (fieldname IN(x,y)): Adds a condition to check the data before inserting or updating it.
CONSTRAINT name_UQ UNIQUE (fieldname): Ensures that all values in a column are unique.

When DDL commands are executed, an implicit commit occurs, committing the transaction even if the command fails or is rolled back.

Common DDL Operations:

CREATE DATABASE: Initiates the creation of a new database.
CREATE TABLE: Defines the structure of a new table including its columns, data types, and constraints.
ALTER TABLE: Modifies the structure of an existing table by adding, modifying, or dropping columns.
ALTER TABLE RENAME: Changes the name of a column in a table.
ALTER TABLE DROP: Removes a column from a table.
ALTER TABLE SET UNUSED COLUMN: Marks a column as unused to improve performance without deleting the column.

Constraints and Validation:

ENABLE: Enforces constraints on data to maintain data integrity.
DISABLE: Allows data to be entered even if it violates constraints.
VALIDATE: Checks existing data against constraints to ensure they are met.
NOVALIDATE: Skips the validation process for existing data against constraints.

Views and Sequences:

CREATE VIEW: Defines a virtual table based on the result of a query for data abstraction and security purposes.
CREATE SEQUENCE: Generates unique numeric values typically used for generating primary key values.

Indexes and Synonyms:

CREATE INDEX: Enhances the performance of queries by speeding up data retrieval.
CREATE UNIQUE INDEX: Ensures the uniqueness of values in a column to prevent duplicate entries.
CREATE SYNONYM: Provides an alternative name or alias for a table, view, sequence, or other schema objects.

Additional DDL Operations:

GRANT: Assigns specific access privileges to users or roles to manipulate database objects.
FLASHBACK: Allows the restoration of database objects to a previous state, enabling point-in-time recovery.

Example of External Table Creation:

CREATE TABLE statments
    ORGANIZATION EXTERNAL
    (TYPE ORACLE_LOADER
    ACCESS PARAMETERS
    (RECORDS DELIMITED BY NEWLINE
    SKIP 2
    FIELDS ( fieldname fieldtype)
    )
    LOCATION 'filepath'