3.2SQL fundamentals and DDL

SQL is broadly divided into DDL (Data Definition Language: schema definition), DML (Data Manipulation Language: data manipulation), and DCL (Data Control Language: privilege control). Within DDL, the area a database specialist must watch most closely is constraint design. Constraints trade off the cost of "fixing data inconsistencies discovered later" against the cost of "rejecting invalid data up front," and how strictly to enforce them depends on business requirements. This section covers practical decisions around DDL, constraints, and DCL.

3.2.1DDL: CREATE, ALTER, DROP

• CREATE defines a new object such as a table, view, or index. CREATE TABLE declares column names and data types along with the constraints described below. ALTER changes an existing object's definition (adding a column with ADD COLUMN, dropping one with DROP COLUMN, adding a constraint, etc.). DROP permanently removes an object—data is lost, and in some products this cannot be rolled back.
• Adding a column to a large production table with ALTER TABLE can, depending on the product, trigger a full table rewrite (with a lock), causing a long exclusive lock that blocks other operations. A DBA must check in advance whether to schedule the change during a late-night maintenance window or whether the product offers an online-DDL capability.

3.2.2Constraint design

• A primary key constraint identifies each row uniquely; it implies both uniqueness and NOT NULL automatically. A foreign key constraint (referential constraint) references another table's primary (or unique) key, preventing the insertion of a nonexistent value or the deletion of a still-referenced row, thereby preserving referential integrity.
• A CHECK constraint guarantees that a column's value satisfies a specific condition (a range, a pattern, etc.). A unique constraint (UNIQUE) prohibits duplicates in a non-primary-key column too (e.g., an email address column). A NOT NULL constraint disallows a missing (NULL) value in that column.
• The ON DELETE clause attached to a foreign key constraint offers CASCADE (deleting the parent cascades to delete the children), RESTRICT/NO ACTION (deletion of the parent is refused as long as children exist), and SET NULL (the child's foreign-key column is set to NULL)—which behavior to choose requires careful design judgment matched to business requirements (mistakenly choosing CASCADE can cause unintended cascading deletion and massive data loss).

3.2.3DCL: GRANT, REVOKE, and transaction finalization

• GRANT grants a user or role the privilege to perform operations (SELECT/INSERT/UPDATE/DELETE, etc.) on an object. REVOKE withdraws a previously granted privilege. Following the principle of least privilege—granting only the operations actually needed for the job—is the safe design.
• COMMIT (finalizing a transaction's changes) and ROLLBACK (undoing changes), sometimes classified under DCL, are often called Transaction Control Language (TCL) statements. In some products, a DDL statement (such as CREATE) implicitly commits the preceding transaction (auto-commit), so care is needed when mixing the order of DDL with DML/TCL.

Suppose a DBA is asked, while reworking an order-management system, "there is a foreign key from the orders table referencing the customers table—how should this be handled when a customer withdraws (closes their account)?" Simply setting ON DELETE CASCADE means that every time a customer withdraws, all of that customer's order history is cascade-deleted, risking the loss of historical data needed for sales aggregation or audit logs. Conversely, ON DELETE RESTRICT means any customer with even one remaining order record cannot be physically deleted, so the withdrawal process itself would fail. A design commonly adopted in practice is to add an is_deleted (soft-delete flag) or retired_at (withdrawal timestamp) column to the customers table and represent withdrawal via a soft delete rather than a physical delete. In that case, the foreign key constraint can remain ON DELETE RESTRICT (since a soft delete never issues an actual DELETE statement, no referential-integrity violation occurs). Additionally, if there is a requirement to allow a withdrawn customer's email address to be reused, the DBA may need to change the email column's unique constraint to a partial unique constraint scoped to "unique only among non-withdrawn customers" (implemented via a partial index in products that support it). Designing the foreign key's ON DELETE behavior and the strictness of constraints in light of what deletion actually means (physical vs. soft) and future data-reuse requirements is the role of a database specialist.

3.2.4Section summary

• DDL (CREATE/ALTER/DROP) defines schema; check for lock impact before ALTER on large production tables
• Choose a foreign key's ON DELETE behavior (CASCADE/RESTRICT/SET NULL) based on what deletion actually means (physical vs. soft)
• GRANT/REVOKE should follow the principle of least privilege, permitting only the operations actually needed