DDL Overview
Data Definition Language (DDL) commands are used to define and manage the structure of database objects such as tables, indexes, and views. These commands affect the schema of the database, providing the framework for organizing and maintaining data.
List of DDL Commands
| Command | Description |
|---|---|
CREATE | Creates new database objects, such as tables and indexes. |
ALTER | Modifies the structure of an existing database object, like adding or removing columns. |
DROP | Deletes an existing database object permanently from the database. |
TRUNCATE | Removes all records from a table but keeps the table structure. |
RENAME | Renames an existing database object, such as a table. |
COMMENT | Adds comments to the database schema for documentation purposes. |
COMMENT ON | Adds or modifies comments on a table or column for clarity. |
REPLACE | Replaces an existing database object, like a view, with a new definition. |
Common Constraints in DDL
Constraints are rules enforced on data in a table to maintain data integrity. They define what values are allowed in a table's columns and ensure the reliability and accuracy of the data.
| Constraint | Description |
|---|---|
PRIMARY KEY | Ensures that each row in a table has a unique identifier. A table can only have one primary key, which can be a single column or a combination of columns. |
FOREIGN KEY | Enforces a link between two tables, maintaining referential integrity. It ensures that the value in a column corresponds to a value in a column of another table. |
NOT NULL | Prevents NULL values from being inserted into a column, ensuring that a value is always provided for that column. |
UNIQUE | Ensures that all values in a column are unique. Unlike the primary key, multiple unique constraints can be applied to a table. |
CHECK | Specifies a condition that values in a column must satisfy. If the condition evaluates to false, the data modification is not allowed. |
DEFAULT | Specifies a default value for a column if no value is provided when a record is inserted. |
INDEX | Improves the performance of queries on a table by creating a data structure that speeds up data retrieval operations. |
Why Use Constraints?
- To maintain data accuracy and consistency.
- To enforce rules for data entry, ensuring that only valid data is stored.
- To establish relationships between tables, which help manage data dependencies.