Search⌘ K
AI Features
Home
Courses
Database Administration for Beginners

Keys: Primary, Foreign, Unique

Explore the essential database keys used in MySQL—primary keys for unique record identification, foreign keys for linking related tables, and unique keys for maintaining distinct values. Understand how these keys enforce data integrity and relationships, helping you design reliable and organized databases.

We'll cover the following...

Imagine we’re managing our OnlineStore database, brimming with information about thousands of customers, a vast array of products, and a constant stream of orders. How do we ensure every customer is uniquely identified so we don’t accidentally mix up the records of John Smith with another John Smith? How do we make sure that when we process an order, it correctly links to an existing customer and the specific products they purchased? And how can we guarantee that no two products are listed with the same name, which could confuse our staff and customers? This is precisely where the power of keys comes into play! Keys are special columns in our tables that help us enforce rules, maintain data integrity, and build relationships between tables. They are fundamental to keeping our data organized, accurate, and reliable.

In this lesson, we’ll explore three essential types of keys in MySQL:

  • We’ll learn about primary keys and how they uniquely identify each record in a table.

  • We’ll discover foreign keys and their role in linking related data across different tables.

  • We’ll understand unique keys and how they ensure that specific information, like email addresses or product names, remains distinct.

By the end of this lesson, we’ll be able to appreciate why these keys are so important and how to define them when we design and create our database tables. Let’s dive in and unlock the potential of keys!

Primary keys: The unique identifiers

Primary keys are the cornerstone of well-structured relational databases. Their main job is to uniquely identify each row within a table. Think of them as a person’s national ID or a product’s serial number. Each is unique and helps us pinpoint the record we’re interested in. We might have duplicate records without primary keys, leading to confusion and data errors. For instance, if two customers had the same ID, how would we know whose order is whose? Primary keys prevent this by enforcing uniqueness. They also play a crucial role in establishing relationships between tables, which we’ll see when we discuss foreign keys.

A primary key is a column, or a set of columns, in a table whose values uniquely identify every row. There are a couple of important rules for primary keys:

  1. Uniqueness: Every value in the primary key column (or combination of columns) must be unique. No two rows can have the same primary key value.

  2. Non-NULL: Primary key values cannot be NULL. Every row must have a primary key value.

  3. One per table: A table can have only one primary key.

Often, primary key columns use integer data types like INT and are set to AUTO_INCREMENT. This means MySQL automatically assigns a new, unique number when adding a new row, saving us the trouble of manually generating unique IDs.

Sample scenario: Assigning unique customer IDs

In our OnlineStore, every time a new customer signs up, we need to assign them a unique identifier. This identifier will track their orders, preferences, and other information. The CustomerID in the Customers table serves this purpose perfectly as a primary key.

Example from the OnlineStore database

Let’s look at our Customers table. The CustomerID column is defined as the primary key.

MySQL
-- Snippet from Customers table creation
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY AUTO_INCREMENT, -- Defines CustomerID as the primary key, automatically generates a unique ID for new customers
    CustomerName VARCHAR(50) NOT NULL,
    Email VARCHAR(50),
    -- ... other columns
);

In this definition:

  • CustomerID INT declares CustomerID as an integer.

  • PRIMARY KEY designates it as the primary key for the Customers table.

  • AUTO_INCREMENT tells MySQL to automatically generate a new unique number for CustomerID whenever a new customer record is inserted.

If we try to insert two customers with the same CustomerID (and it wasn’t AUTO_INCREMENT), or try to insert a customer with a NULL CustomerID, MySQL would raise an error, protecting our data’s integrity.

MySQL 8.0
-- Let's insert some customers (assuming AUTO_INCREMENT handles CustomerID)
INSERT INTO Customers (CustomerName, Email, Phone, Address)
VALUES ('Alice Wonderland', 'alice@wonderland.com', '555-1234', '12 Magical Lane');
INSERT INTO Customers (CustomerName, Email, Phone, Address)
VALUES ('Bob The Builder', 'bob@builder.com', '555-5678', '34 Construction Drive');
-- Let's try to see our customers
SELECT CustomerID, CustomerName, Email FROM Customers WHERE CustomerName LIKE 'Alice%' OR CustomerName LIKE 'Bob%';

If we were to manually try to insert a customer with an existing CustomerID, like this (assuming CustomerID 1 already exists and AUTO_INCREMENT was not used or was overridden):

MySQL 8.0
-- This would typically cause an error if CustomerID 1 already exists:
INSERT INTO Customers (CustomerID, CustomerName, Email)
VALUES (1, 'Charlie Brown', 'charlie@peanuts.com');
-- Error: Duplicate entry '1' for key 'PRIMARY'

This error demonstrates the primary key enforcing uniqueness.

Foreign keys: Building relationships

A foreign key is a column (or set of columns) used to link data between tables in a relational database. Now that we understand how to uniquely identify records within a single table using primary keys, let’s explore how we connect related information across different tables. This is where foreign keys come into the picture.

Foreign keys are essential for maintaining referential integrity, ensuring that values in one table always match valid values in a related table. Referential integrity means that relationships between tables remain consistent. For example, if we have an Orders table and a Customers table, we want to ensure that every order in the Orders table belongs to an actual customer in the Customers table. A foreign key prevents us from adding an order for a non-existent customer or deleting a customer who still has active orders (depending on how the foreign key is configured). They are the glue that links our relational database tables together, allowing us to build a meaningful and interconnected data model.

The table containing the foreign key is called the child table, while the table it references is called the parent table. The values in the foreign key column of the child table must match actual values in the primary key column of the parent table, or they can be NULL if the foreign key column allows NULLs.

For example, in our OnlineStore database:

  • The Products table has a CategoryID column. This CategoryID is a foreign key that references the CategoryID (which is the primary key) in the Categories table. This ensures that every product belongs to a valid category.

  • The Orders table has a CustomerID column. This CustomerID is a foreign key that references the CustomerID (primary key) in the Customers table, linking each order to the customer who placed it.

Sample scenario: Linking products to categories

Imagine we’re adding a new product, say Organic Green Tea, to our Products table. We want to categorize it under Beverages. The foreign key ensures that the Beverages category actually exists in our Categories table. If we tried to assign it to a CategoryID that doesn’t exist, the database would prevent this, maintaining data accuracy.

Example from OnlineStore database

Let’s examine how the Products table links to the Categories table using CategoryID.

First, here’s a simplified Categories table definition:

MySQL
-- Snippet from Categories table creation
CREATE TABLE Categories (
    CategoryID INT PRIMARY KEY AUTO_INCREMENT, -- Primary key for Categories
    CategoryName VARCHAR(50) NOT NULL UNIQUE
);

And now, the Products table definition showing the foreign key:

MySQL
-- Snippet from Products table creation
CREATE TABLE Products (
    ProductID INT PRIMARY KEY AUTO_INCREMENT,
    ProductName VARCHAR(50) NOT NULL UNIQUE,
    CategoryID INT, -- This will be the Foreign Key
    Price DECIMAL(10, 2) NOT NULL,
    Stock INT NOT NULL,
    FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID) -- Defines CategoryID as a foreign key linking to Categories table
);

This is how foreign keys are defined in SQL and MySQL to enforce relationships between tables. In the Products table, FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID) tells MySQL that CategoryID in Products must match a CategoryID in the Categories table.

Let’s try an example. First, we insert a category:

MySQL 8.0
-- Ensure the Categories table exists and insert a category
-- (Assuming Categories table is already created as per the course database script)
INSERT INTO Categories (CategoryName) VALUES ('Beverages');
-- Let's find the CategoryID for 'Beverages' (assuming it's auto-incremented)
-- For this example, let's assume 'Beverages' got CategoryID 9 if we add it after the initial 8 categories.
-- To be sure, we would typically run: 
SELECT CategoryID FROM Categories WHERE CategoryName = 'Beverages';

Now, let’s insert a product into this category:

MySQL 8.0
-- Insert a product into an existing category
-- First, find the actual CategoryID for 'Beverages' (which is 9 in our sample data)
-- SELECT CategoryID FROM Categories WHERE CategoryName = 'Beverages'; -- This would return 9
INSERT INTO Products (ProductName, CategoryID, Price, Stock)
VALUES ('Organic Green Tea', 9, 20.99, 150); -- Assuming CategoryID 9 (Beverages) exists.

What happens if we try to insert a product with a CategoryID that doesn’t exist in the Categories table?

MySQL 8.0
-- Attempt to insert a product with a non-existent CategoryID (e.g., 99)
-- This INSERT statement will likely fail if CategoryID 99 does not exist in the Categories table.
INSERT INTO Products (ProductName, CategoryID, Price, Stock)
VALUES ('Magic Wand', 99, 199.99, 10);
-- Expected Error: Cannot add or update a child row: a foreign key constraint fails

MySQL would prevent this insertion, displaying an error because the foreign key constraint is violated. This is how foreign keys help maintain the integrity of our database relationships.

Unique keys: Ensuring distinct values

We’ve seen that primary keys ensure every row has a unique identifier. But what if we need to ensure that values in other columns are also unique? For instance, we wouldn’t want two different products to have the exact same ProductName, or two customers to share the same Email address. This is where unique keys (or unique constraints) are useful.

Unique keys help us maintain data accuracy by preventing duplicate entries in columns that aren’t the primary key but still require uniqueness. This could be an email address, a product code, a social security number (if stored), or any other piece of information that should be distinct for each record. While a table can only have one primary key, it can have multiple unique keys.

A unique key (or UNIQUE constraint) ensures that all values in a specific column (or a set of columns) are distinct from one another. Unlike primary keys, a column with a unique constraint can, by default, accept NULL values. In MySQL, a column with a UNIQUE constraint can actually store multiple NULL values, because NULL is not considered equal to any other value, including another NULL. However, if there are non-NULL values, they must all be unique.

Sample scenario: Ensuring unique product names

In our OnlineStore, it’s crucial that each product has a unique name to avoid confusion for customers and for inventory management. If we had two different items both named Men’s Cotton T-Shirt, it would be problematic. A unique key on the ProductName column in the Products table prevents this.

Example from OnlineStore database

Let’s look at the Products table again. The ProductName column has a UNIQUE constraint.

MySQL
-- Snippet from Products table creation illustrating a UNIQUE key
CREATE TABLE Products (
    ProductID INT PRIMARY KEY AUTO_INCREMENT,
    ProductName VARCHAR(50) NOT NULL UNIQUE, -- Ensures every product name is unique
    CategoryID INT,
    Price DECIMAL(10, 2) NOT NULL,
    Stock INT NOT NULL,
    LastRestockDate DATE NULL,
    MonthlySales INT DEFAULT 0,
    InventoryTurnoverRate DECIMAL(5,2) GENERATED ALWAYS AS (
        CASE WHEN Stock > 0 THEN MonthlySales / Stock ELSE 0 END
    ) STORED,
    FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
);

The UNIQUE keyword after ProductName VARCHAR(50) NOT NULL ensures that no two products can have the same name.

Similarly, in the Categories table:

MySQL
-- Snippet from Categories table creation illustrating a UNIQUE key
CREATE TABLE Categories (
    CategoryID INT PRIMARY KEY AUTO_INCREMENT,
    CategoryName VARCHAR(50) NOT NULL UNIQUE -- Ensures every category name is unique
);

The CategoryName is also unique.

Let’s see this in action. We know the Laptop is already on our Products table.

-- This product is already in our sample data:
INSERT INTO Products (ProductName, CategoryID, Price, Stock)
VALUES ('Laptop', 1, 1200.00, 50);
-- Now, let's try to add another product with the exact same name 'Laptop'
-- This INSERT statement will fail due to the UNIQUE constraint on ProductName.
INSERT INTO Products (ProductName, CategoryID, Price, Stock)
VALUES ('Laptop', 1, 1250.00, 30); -- Different price/stock, but same name
-- Expected Error: Duplicate entry 'Laptop' for key 'ProductName' (or a similar constraint name)

What happens if we try to insert a product with a name that already exists in the Products table with the same name?

MySQL 8.0
-- Inserting a product that already exists with the same name
INSERT INTO Products (ProductName, CategoryID, Price, Stock)
VALUES ('Laptop', 1, 1250.00, 30);

MySQL will prevent the second INSERT because the ProductName Laptop already exists, thanks to the UNIQUE constraint. This helps us maintain cleaner and more reliable product data.

Quiz

Let’s test our understanding of keys with a few questions.

Technical Quiz
1.

Which type of key is primarily used to uniquely identify each row in a table and strictly disallows NULL values?

A.

Foreign Key

B.

Unique Key

C.

Primary Key

D.

Composite Key

1 / 3

Congratulations on making it through this lesson on keys! We’ve covered a lot of ground, from understanding the vital role of primary keys in uniquely identifying records, to seeing how foreign keys build bridges between our tables to maintain referential integrity, and finally, how unique keys ensure that specific data points like email addresses or product names remain distinct. These concepts are absolutely fundamental to designing robust and reliable databases. 

Keep up the fantastic work! Building a solid understanding of these core concepts now will make more advanced topics much easier to grasp. Happy learning!