DDL and DML
Constraints — PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL
Constraints are rules applied to columns that enforce data integrity and define the relationship between tables. By establishing these boundaries, you ensure that the data stored remains consistent, accurate, and reliable throughout the lifecycle of your application. You should implement these constraints during the design phase of your database schema to prevent invalid data states before they occur.
NOT NULL: Ensuring Mandatory Data
The NOT NULL constraint is the foundational building block for data reliability, explicitly defining which columns must contain a value for a record to be valid. When you mark a column as NOT NULL, you are effectively telling the database engine that any attempt to insert a record without providing data for this specific column should result in an immediate rejection of the entire transaction. This is critical for fields that define the core identity of an entity, such as a user's email address or a product's name. Without this constraint, your database would likely fill with 'orphan' records that lack essential attributes, making your analytical queries and application logic highly unreliable. By enforcing presence at the schema level, you eliminate the need to write redundant 'null-check' validation logic inside your application code, shifting the burden of data quality to the source where it belongs for optimal performance.
-- Creating a table where the username and email are mandatory
CREATE TABLE Users (
user_id INT,
username VARCHAR(50) NOT NULL, -- Prevents missing usernames
email VARCHAR(100) NOT NULL -- Ensures every user is reachable
);UNIQUE: Guaranteeing Data Distinctness
The UNIQUE constraint serves as a guardrail against duplicate information within a dataset, ensuring that no two rows possess the same value for a designated column or set of columns. From a logical perspective, this is vital for entities that must be identifiable by human-readable attributes, such as social security numbers, passport numbers, or account usernames. While a primary key also forces uniqueness, the UNIQUE constraint is specifically designed for secondary identifiers that should not be repeated. When the database engine attempts an insertion, it performs an implicit scan of the existing index to ensure the new value does not conflict with what is already stored. This mechanism is computationally efficient because databases build indexes behind the scenes for unique columns, making subsequent lookups for those values extremely fast. This is the optimal way to handle business logic requirements where data integrity is paramount, preventing accidental duplicates before they can compromise your reporting.
-- Ensuring usernames are unique across the system
CREATE TABLE Employees (
employee_id INT,
tax_id VARCHAR(20) UNIQUE, -- Prevents duplicate tax IDs
email VARCHAR(100) UNIQUE -- Ensures email addresses are distinct
);PRIMARY KEY: Defining Row Identity
A PRIMARY KEY is a specific type of constraint that serves as the definitive identifier for every individual row in a table. It is essentially a combination of a NOT NULL constraint and a UNIQUE constraint, with the additional requirement that the value must never change once assigned to a record. The purpose of a primary key is to provide a stable anchor for the row, allowing other parts of the database—or external applications—to reference this specific record reliably. Because it acts as an identity, the database automatically creates a clustered index on the primary key, which physically orders the data on the disk based on this key. This makes retrieval operations by the ID incredibly performant. When designing a database, selecting a stable, immutable, and minimal primary key is perhaps the most important architectural decision you can make, as it directly impacts both the speed of your joins and the long-term maintainability of your data model.
-- Defining a primary key to serve as the row identifier
CREATE TABLE Products (
product_id INT PRIMARY KEY, -- Unique ID that cannot be NULL
sku VARCHAR(50) NOT NULL
);FOREIGN KEY: Maintaining Relational Integrity
FOREIGN KEY constraints are the mechanism by which we establish and preserve relationships between different tables in a relational database. By declaring that a column in one table must match a value present in the primary key of another table, you create a 'referential integrity' contract. This prevents the creation of dangling references—records that point to data that does not exist—which would otherwise cause severe inconsistencies. For instance, if you have an 'Orders' table, a foreign key linking to a 'Customers' table ensures that you cannot create an order for a customer who is not yet in the system. This constraint forces the database to participate in the enforcement of business rules; it validates that every child record is properly associated with a valid parent. This is essential for complex databases where you need to perform cascading operations, such as ensuring that deleting a customer record triggers the appropriate handling of their linked orders.
-- Linking orders to customers with a foreign key
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
customer_id INT,
-- Ensures the customer_id must exist in the Customers table
FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);Advanced Interaction: Combining Constraints
In sophisticated database schemas, you will often find that single constraints are insufficient to model complex reality, leading developers to combine multiple constraints to achieve precise data control. For example, you might combine a UNIQUE constraint on a set of columns (a composite key) with a NOT NULL constraint to ensure that a combination of values—like an area code and a phone number—remains unique and present. Furthermore, the combination of FOREIGN KEY constraints with cascading rules allows for automated data cleanup, where deleting a parent row can automatically remove all dependent child rows. Understanding how these constraints interact requires you to visualize the database as a set of interconnected logical nodes where every data point is strictly bound by rules. By thoughtfully layering these constraints, you move the complexity of data integrity from the application code into the database layer, which is far more efficient at checking these rules at the moment of ingestion.
-- Using a composite unique constraint and foreign keys
CREATE TABLE OrderItems (
order_id INT,
item_id INT,
PRIMARY KEY (order_id, item_id), -- Composite primary key
FOREIGN KEY (order_id) REFERENCES Orders(order_id)
);Key points
- NOT NULL ensures essential data is captured for every record.
- UNIQUE constraints prevent duplicate values in non-primary columns.
- PRIMARY KEY uniquely identifies each row and enables fast data retrieval.
- FOREIGN KEY maintains referential integrity between parent and child tables.
- Clustered indexes are automatically generated by the primary key to optimize storage.
- Constraints should be applied during schema creation to prevent invalid data ingestion.
- Composite keys allow you to enforce uniqueness across multiple columns simultaneously.
- Foreign keys can be configured to cascade changes to maintain consistency across tables.
Common mistakes
- Mistake: Allowing NULL values in a PRIMARY KEY. Why it's wrong: A PRIMARY KEY must uniquely identify a row; NULL represents an unknown value and cannot satisfy uniqueness. Fix: Always ensure the column(s) defined as PRIMARY KEY have a NOT NULL constraint applied.
- Mistake: Assuming a UNIQUE constraint allows multiple NULL values. Why it's wrong: In most standard SQL implementations, multiple NULLs are often allowed, but treating them as unique data can lead to logic errors in reporting. Fix: Use a trigger or a filtered index if you require strict uniqueness including NULLs.
- Mistake: Creating a FOREIGN KEY that references a column that is not a PRIMARY KEY or UNIQUE. Why it's wrong: A foreign key must map to a target that provides a guaranteed unique identity, otherwise, the referential integrity cannot be enforced. Fix: Ensure the referenced column in the parent table has a PRIMARY KEY or UNIQUE constraint.
- Mistake: Using PRIMARY KEY on a column that is expected to change frequently. Why it's wrong: PRIMARY KEYs are often used for indexing; changing them causes fragmentation and requires cascading updates to all foreign key references. Fix: Use an immutable surrogate key (like an auto-incrementing integer) as the PRIMARY KEY.
- Mistake: Forgetting to name constraints during definition. Why it's wrong: If a constraint is unnamed, the database generates a random identifier, making it extremely difficult to drop or modify the constraint later. Fix: Use the CONSTRAINT keyword followed by a descriptive name (e.g., CONSTRAINT fk_order_customer).
Interview questions
What is the fundamental purpose of the NOT NULL constraint, and why should developers be mindful of its application during database design?
The NOT NULL constraint is a fundamental rule in SQL that prevents a column from accepting NULL values, ensuring that every row in the table contains valid data for that specific field. Developers must use it because NULL represents an unknown or missing value, which can lead to unpredictable results in arithmetic operations, aggregate functions like SUM or AVG, and complex JOIN logic. By mandating a value at the schema level, you enforce data integrity and ensure that the database reflects business requirements accurately. For example, 'CREATE TABLE Users (id INT PRIMARY KEY, username VARCHAR(50) NOT NULL);' guarantees that no user can exist without an associated username, preventing logic errors in application code that relies on that identifier.
Explain the role of the UNIQUE constraint and how it differs from a PRIMARY KEY in terms of functional capability.
The UNIQUE constraint ensures that all values in a column or a combination of columns are distinct across the entire table, preventing duplicate entries. While it enforces uniqueness, it differs significantly from a PRIMARY KEY because it allows for NULL values, depending on the specific SQL implementation. Furthermore, a table can possess multiple UNIQUE constraints, whereas it can have only one PRIMARY KEY. We use UNIQUE for fields like email addresses or phone numbers where duplicates are prohibited, but the field does not necessarily serve as the primary identity for the row. This provides flexibility while maintaining strict data governance across non-key columns.
How does the PRIMARY KEY constraint maintain the integrity of a table, and what two specific properties must a column possess to be a valid primary key?
A PRIMARY KEY is the definitive identifier for a row within a SQL table. To serve as a valid primary key, a column must satisfy two strict properties: it must be NOT NULL, ensuring every row has an identity, and it must be UNIQUE, ensuring that no two rows share the same identifier. By combining these, the database engine can index the column efficiently, allowing for rapid retrieval of specific records. The PRIMARY KEY is essential because it is the target for FOREIGN KEY references from other tables, serving as the relational anchor that links complex datasets together in a normalized structure.
Explain the concept of a FOREIGN KEY constraint and its impact on referential integrity within a relational database.
A FOREIGN KEY is a column or set of columns that links a row in one table to a PRIMARY KEY in another table, establishing a parent-child relationship between them. The primary role of this constraint is to enforce referential integrity, ensuring that no child record can exist without a corresponding parent. For instance, if you have an 'Orders' table, a 'customer_id' column acting as a FOREIGN KEY prevents the entry of an order for a non-existent customer. This mechanism prevents 'orphaned' records, which are inconsistent entries that lack a valid reference, thereby maintaining the structural consistency and logical relationships required for high-quality relational database performance.
Compare the approach of using a Surrogate Key versus a Natural Key for a PRIMARY KEY constraint; which is generally preferred and why?
A Natural Key is a column containing data that already exists in the real world, such as a Social Security Number or a product code, whereas a Surrogate Key is a system-generated, artificial value like an auto-incrementing integer. I generally prefer Surrogate Keys because Natural Keys can be volatile; for example, a business rule might change, forcing an update to the key across all related tables, which is expensive. Surrogate Keys are immutable and typically smaller, leading to better performance in index lookups and joins. While Natural Keys make sense for human readability, Surrogate Keys offer superior stability and performance, which are the highest priorities when designing scalable database schemas.
When designing a database, how do you determine whether to use a UNIQUE constraint or a PRIMARY KEY for a column that appears to be unique, and what are the architectural consequences of your choice?
Choosing between them involves deciding whether the column identifies the record entity itself or merely enforces a business rule. I use a PRIMARY KEY when the column acts as the unique identifier for that specific table's relational integrity. If I have a 'Users' table, the 'user_id' is the PRIMARY KEY, while the 'email_address' gets a UNIQUE constraint. The consequences are architectural: the PRIMARY KEY is physically clustered by most database engines to optimize record retrieval. If you accidentally make an email the primary key instead of an integer ID, you incur a performance penalty on joins, as the database must perform comparisons on large strings rather than compact, indexed integers, significantly degrading query speed in large datasets.
Check yourself
1. What happens if you attempt to insert a duplicate value into a column defined with a UNIQUE constraint that currently contains no NULL values?
- A.The database allows the insert but marks it with a warning.
- B.The database truncates the duplicate value to fit.
- C.The database rejects the insert and throws an integrity violation error.
- D.The database automatically creates a new row with a modified value.
Show answer
C. The database rejects the insert and throws an integrity violation error.
A UNIQUE constraint enforces data integrity by preventing identical values in a column. Option 2 is correct because the database engine will block the transaction. Options 0, 1, and 3 are incorrect because SQL constraints are absolute rules that do not permit data modification or warnings to bypass the uniqueness requirement.
2. Consider a table 'Orders' with a FOREIGN KEY referencing 'Customers(id)'. What occurs if you try to delete a customer record that still has associated orders?
- A.The customer is deleted and orders are deleted automatically.
- B.The deletion is blocked to prevent orphaned records in the Orders table.
- C.The orders are moved to a temporary archive table.
- D.The foreign key is temporarily disabled to allow the deletion.
Show answer
B. The deletion is blocked to prevent orphaned records in the Orders table.
Referential integrity dictates that a child record cannot exist without a valid parent. Option 1 is correct because the database prevents the deletion. Options 0, 2, and 3 are incorrect because cascading is not automatic unless explicitly defined (ON DELETE CASCADE), and the database does not archive or disable constraints on its own.
3. Why is it best practice to define a column as NOT NULL even if a UNIQUE constraint exists on it?
- A.It increases the storage efficiency of the database table.
- B.It allows the query optimizer to perform faster index scans.
- C.It prevents ambiguity regarding the identity of the records.
- D.It automatically upgrades the UNIQUE constraint to a PRIMARY KEY.
Show answer
C. It prevents ambiguity regarding the identity of the records.
A primary goal of database design is ensuring every record is identifiable. Option 2 is correct because NOT NULL forces explicit data entry, avoiding the ambiguity of NULL values. Options 0 and 1 are secondary performance concerns, while option 3 is false as a UNIQUE constraint never automatically becomes a PRIMARY KEY.
4. If a table has a composite PRIMARY KEY consisting of (OrderID, ProductID), what does this imply?
- A.Each OrderID must be unique across the entire table.
- B.Each ProductID must be unique across the entire table.
- C.The combination of OrderID and ProductID must be unique for every row.
- D.The table cannot have any foreign keys referencing it.
Show answer
C. The combination of OrderID and ProductID must be unique for every row.
A composite PRIMARY KEY treats the group of columns as a single identifier. Option 2 is correct because it ensures that the tuple (OrderID, ProductID) is unique. Options 0 and 1 are incorrect because individual columns in a composite key can repeat as long as the pair does not. Option 3 is incorrect as composite keys can be referenced by foreign keys.
5. Which scenario best describes the intended use of a FOREIGN KEY constraint?
- A.To ensure that values in a child table correspond to existing values in a parent table.
- B.To make sure that column names are consistent across all tables in the database.
- C.To allow a column to store multiple values in a single row.
- D.To replace the need for a JOIN statement when querying data.
Show answer
A. To ensure that values in a child table correspond to existing values in a parent table.
The FOREIGN KEY is the primary mechanism for establishing relationships between tables. Option 0 is correct because it links tables through referential integrity. Option 1 relates to naming conventions, option 2 describes arrays (which SQL tables don't use), and option 3 is false because foreign keys facilitate joins rather than replace them.