What are PRIMARY KEY, FOREIGN KEY, and UNIQUE constraints in SQL?
Constraints enforce rules on data in a table to ensure data integrity:
• PRIMARY KEY uniquely identifies each record in a table. It cannot contain NULL values, and each table can have only one primary key (which can consist of multiple columns). • UNIQUE constraint ensures all values in a column or set of columns are distinct, but unlike primary keys, allows NULLs (in most dialects, one or multiple NULLs). • FOREIGN KEY creates a relationship linking a column to the primary or unique key of another table, preventing orphaned records and enforcing referential integrity.
CREATE TABLE Users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE
);
CREATE TABLE Profiles (
user_id INT PRIMARY KEY,
bio TEXT,
CONSTRAINT fk_profile_user
FOREIGN KEY (user_id)
REFERENCES Users(id)
ON DELETE CASCADE
);The `ON DELETE CASCADE` rule guarantees that if a row in `Users` is deleted, the corresponding profile in `Profiles` is automatically cleaned up by the engine.
Ready for Set Operations (UNION/INTERSECT)?
Take a quick test to evaluate yourself, or move straight to the next module.