Constraints (Primary Key, Foreign Key, Check & Unique)
7 questions found
What is a primary key constraint, and why is it important for a database table?
Beginner
A primary key constraint uniquely identifies each row in a table, ensuring no two rows can have the same value in that column, and it also automatically prevents that column from containing NULL values, making it the reliable way other tables can reference a specific row.
CREATE TABLE Customers (
CustomerId INT PRIMARY KEY,
Name VARCHAR(100)
);
Real-world example
An online store uses CustomerId as the primary key for its customers table, guaranteeing that every customer record can always be uniquely identified and referenced by other tables like orders.
Common follow-ups: Can a table have more than one primary key column?;What happens if you try to insert a duplicate primary key value?
Data Types & Schema Design;Normalization
What is a foreign key constraint, and how does it help maintain data integrity between two related tables?
Beginner
A foreign key constraint links a column in one table to the primary key of another table, ensuring that any value entered in that column must already exist in the referenced table, which prevents situations like an order being created for a customer id that does not actually exist.
CREATE TABLE Orders (
OrderId INT PRIMARY KEY,
CustomerId INT,
FOREIGN KEY (CustomerId) REFERENCES Customers(CustomerId)
);
Real-world example
An order management system uses a foreign key to guarantee every order is linked to a valid, existing customer, preventing orphaned orders that reference customers who were never actually added to the system.
Common follow-ups: What happens if you try to delete a customer who still has related orders?;Can a foreign key reference a column in the same table it is defined on?
Normalization;Joins
What is a check constraint, and how do you use one to enforce a specific business rule at the database level?
Intermediate
A check constraint defines a condition that every value in a column, or combination of columns, must satisfy before a row can be inserted or updated, letting you enforce business rules like requiring a price to always be positive directly in the database, rather than relying solely on application code to catch invalid data.
CREATE TABLE Products (
ProductId INT PRIMARY KEY,
Price DECIMAL(10,2) CHECK (Price > 0)
);
Real-world example
A product catalog enforces that every product's price must always be greater than zero directly at the database level, preventing accidental negative pricing even if a bug exists somewhere in the application code.
Common follow-ups: Can a check constraint reference more than one column at the same time?;What happens if existing data violates a new check constraint being added?
Data Types & Schema Design;Error Handling with TRY CATCH
How is a unique constraint different from a primary key constraint, and when would you use one instead of the other?
Intermediate
A unique constraint, like a primary key, ensures every value in a column is distinct, but unlike a primary key it does allow a single NULL value and a table can have multiple unique constraints, making it useful for enforcing uniqueness on columns like an email address that are not the table's main identifying key.
CREATE TABLE Customers (
CustomerId INT PRIMARY KEY,
Email VARCHAR(255) UNIQUE
);
Real-world example
A customer database uses a unique constraint on the email column to prevent duplicate customer accounts from being created with the same email address, while still using CustomerId as the actual primary key.
Common follow-ups: How many unique constraints can a single table have?;Does a unique constraint automatically create an index behind the scenes?
Data Types & Schema Design;Indexes
How do cascading actions work with foreign key constraints, such as ON DELETE CASCADE, and what risks come with using them?
Advanced
Cascading actions automatically apply a related change to child rows when the parent row is modified or deleted, such as automatically deleting all of a customer's orders when that customer is deleted, which can be convenient but also risky, since a single delete statement could unexpectedly remove far more data than intended if not carefully planned.
CREATE TABLE Orders (
OrderId INT PRIMARY KEY,
CustomerId INT,
FOREIGN KEY (CustomerId) REFERENCES Customers(CustomerId) ON DELETE CASCADE
);
Real-world example
A team enables ON DELETE CASCADE between customers and orders so that removing a test customer account automatically cleans up all of their related test orders without requiring several separate delete statements.
Common follow-ups: What other cascading actions are available besides CASCADE?;How do you audit which tables have cascading deletes configured before making a risky change?
Normalization;Transactions & ACID
How would you add a new constraint to an existing table that already contains data, especially if some existing rows might violate that constraint?
Advanced
You first identify and fix any existing data that would violate the new constraint, then add the constraint using an ALTER TABLE statement, optionally using WITH NOCHECK if you need to add it without validating existing data immediately, though this leaves the constraint untrusted until it is explicitly checked later.
-- First, identify violating rows
SELECT * FROM Products WHERE Price <= 0;
-- Then add the constraint after cleaning up the data
ALTER TABLE Products
ADD CONSTRAINT CK_Price CHECK (Price > 0);
Real-world example
A team cleans up a handful of products with incorrect zero dollar prices before successfully adding a check constraint that prevents this type of pricing error from happening again in the future.
Common follow-ups: What does it mean for a constraint to be untrusted in SQL Server?;How do you later validate an untrusted constraint against all existing data?
Data Types & Schema Design;Error Handling with TRY CATCH
How do you temporarily disable a constraint to perform a bulk data load, and how do you safely re-enable it afterward?
Intermediate
You use ALTER TABLE with the NOCHECK CONSTRAINT option to temporarily disable a specific constraint, perform your bulk data operation, and then re-enable it using CHECK CONSTRAINT, ideally with the WITH CHECK option to also validate that all the newly loaded data actually satisfies the constraint.
ALTER TABLE Orders NOCHECK CONSTRAINT FK_Orders_Customers;
-- Perform bulk insert here
ALTER TABLE Orders WITH CHECK CHECK CONSTRAINT FK_Orders_Customers;
Real-world example
A data migration team temporarily disables a foreign key constraint to quickly bulk load historical order data, then re-enables and validates the constraint afterward to confirm all the loaded data is actually consistent.
Common follow-ups: What is the performance benefit of disabling constraints during a bulk load?;How do you find out if a constraint is currently trusted or untrusted after being re-enabled?
Query Optimization & Plans;Data Types & Schema Design