In this lesson, you will learn how to use UNIQUE constraints to enforce data integrity by preventing duplicate entries for business-critical information.
What is a Business Key?
In many databases, we use an auto-incrementing ID (a surrogate key) to track rows. However, in the real world, entities often have "natural" identifiers that business rules define as unique. For example, in an Employees table, you might have an employee_id (a number for the database), but you also have a tax_id or a company_email that must never be shared between two people. These are your Business Keys.
If you don't enforce these rules at the database level, it becomes very easy for a data entry error to accidentally create two records for the same person, leading to corrupted reports and broken logic.
Implementing Unique Constraints
A UNIQUE constraint acts as a "bouncer" for your table. When you apply it to a column, the database engine checks every INSERT or UPDATE operation. If the new value already exists in that column, the database rejects the change and returns an error.
You can add this when you create a table:
CREATE TABLE Employees (
employee_id INT PRIMARY KEY,
full_name VARCHAR(100),
email VARCHAR(255) UNIQUE
);
In this example, if someone tries to register a second account using an email address that is already in the email column, the database will block the entry.
Composite Unique Constraints
Sometimes, a business key isn't just one column, but a combination of several. For instance, if you are tracking LibraryBooks, a book might be identified by a barcode and a shelf_location. You might allow multiple copies of the same book, but you cannot have two books with the exact same barcode in the same shelf_location. You can enforce this by grouping columns in your constraint:
ALTER TABLE LibraryBooks
ADD CONSTRAINT uq_barcode_location UNIQUE (barcode, shelf_location);
By defining these constraints, you shift the burden of data quality from the application code to the database engine itself, ensuring that your business rules are always respected.
Key Takeaway: Use UNIQUE constraints to protect your data integrity by ensuring that specific columns or combinations of columns act as reliable, non-duplicate identifiers for your business entities.