Constraints in SQL

The Database Guardrail Layer: Constraints In relational database design, application code can occasionally try to write malformed or conflicting data due to software bugs or race conditions. Constraints are strict rules applied directly to a table's column schema definition. The database engine enforces these rules at the engine layer, acting as a final line of defense. Any write operation ( INSERT or UPDATE ) that violates a constraint is instantly blocked, and the transaction is rolled back before it can corrupt your data on disk. 1. Structural Constraints Structural constraints define how data is uniquely identified and how different tables link together across the database schema. A. PRIMARY KEY (PK) The PRIMARY KEY uniquely identifies every single row in a table. The Rule: A table can have exactly one Primary Key. It automatically enforces that values in the column must be completely unique, and it cannot contain NULL values . Behind the Scenes: The engine automatically builds a Clustered Index on this column, optimizing lookups. B. FOREIGN KEY (FK) A FOREIGN KEY is a column in a child table that points directly to a Primary Key inside a parent table. This constraint forms the structural link that binds different tables together, enforcing Referential Integrity . SQL CREATE TABLE operational_nodes ( node_id BIGINT GENERATED ALWAYS AS IDENTITY, parent_company_id INT, PRIMARY KEY (node_id), -- Link child company ID pointer directly back to the parent companies table FOREIGN KEY (parent_company_id) REFERENCES corporate_entities(company_id) ); Referential Integrity Actions When a row in the parent table is deleted or updated, any child rows pointing to it will react based on the Foreign Key's configuration: ON DELETE RESTRICT / NO ACTION (Default): The engine blocks you from deleting the parent row as long as child records are still pointing to it. ON DELETE CASCADE : Deleting the parent row automatically deletes all matching rows in the child table. ON DELETE SET NULL : Deleting the parent row automatically sets the foreign key column in the child rows to NULL . 2. Data Validation & Value Constraints Value constraints validate the actual data states being written into individual column fields. A. UNIQUE Enforces that all values inside a column must be completely unique across the entire table. Primary Key vs. Unique Key: Unlike a Primary Key, a table can contain multiple separate UNIQUE columns, and they are allowed to accept NULL values (representing an unassigned or missing state). Multiple rows can contain NULL because NULL represents an unknown state, meaning it never mathematically matches another null. B. CHECK Evaluates a custom boolean validation rule before allowing a write operation to succeed. If the expression returns FALSE , the write is blocked. SQL ALTER TABLE operational_nodes ADD CONSTRAINT check_positive_weight CHECK (system_weight > 0); C. DEFAULT Automatically assigns a fallback value to the column if an INSERT command doesn't specify one. This is highly useful for managing default state flags or creation timestamps. SQL ALTER TABLE operational_nodes ALTER COLUMN is_active SET DEFAULT TRUE, ALTER COLUMN created_at SET DEFAULT CURRENT_TIMESTAMP; D. NOT NULL Forces the column to always receive a valid data value. The engine will throw an error if you attempt to save or update a row with this field left empty or unassigned. Production Constraint Syntax Blueprint Constraints can be declared inline next to the column data type definition, or defined explicitly at the bottom as a table-level constraint . Assigning a custom name to a constraint using the CONSTRAINT keyword is a production standard, making errors much easier to debug when a data write fails. SQL CREATE TABLE network_interfaces ( interface_id INT GENERATED ALWAYS AS IDENTITY, node_id BIGINT NOT NULL, mac_address VARCHAR(17) NOT NULL, port_number INT NOT NULL, interface_status VARCHAR(20) DEFAULT 'PROVISIONING', -- Table-Level Named Constraints CONSTRAINT pk_network_interfaces PRIMARY KEY (interface_id), CONSTRAINT fk_interfaces_to_nodes FOREIGN KEY (node_id) REFERENCES operational_nodes(node_id) ON DELETE CASCADE, CONSTRAINT uq_mac_address UNIQUE (mac_address), CONSTRAINT chk_valid_port_range CHECK (port_number BETWEEN 1 AND 65535) ); Database Constraints Reference Matrix Constraint Name Target Scope Null Value Compatibility Primary Production Safeguard Role PRIMARY KEY Table Layer Strictly Forbidden. Guarantees an absolute, unique identifier for every row record while driving physical table sorting. FOREIGN KEY Relationship Layer Permitted (represents an unlinked child). Enforces referential integrity, blocking orphaned records across dependent tables. UNIQUE Column Layer Permitted. Prevents duplicate business values (like emails or network keys) without reordering physical disk layouts. CHECK Column / Table Permitted. Hardens data integrity by validating custom business rules (e.g., verifying price is greater than zero). DEFAULT Column Layer Permitted. Automates data population by injecting fallback values when a field is missing from an insert statement. NOT NULL Column Layer Strictly Forbidden. Guarantees that essential fields are always filled, preventing missing data states down the line.