Webbo3 Data Analysis Bootcamp · SQL Module · Lesson 4

Table Relationships: Primary Keys, Foreign Keys, Cardinality, ERDs, and Referential Integrity

A foundational lesson on how tables connect to each other through keys, the types of relationships between entities, how to visualize them, and how databases enforce consistency across related tables.

So far you have worked with single tables. You created a customers table, queried it, filtered it, and retrieved data from it. But real databases never contain just one table. A retail system has customers, orders, products, categories, payments, and shipping records. A hospital system has patients, doctors, appointments, prescriptions, and medical histories. Each of these tables holds one type of entity, and the relationships between them are what turn isolated data into a coherent system. Without relationships, you would store customer names inside every order row, creating endless duplication and inevitable inconsistency. With relationships, you store each customer once, reference them by a unique identifier in every order, and let the database enforce that the reference is always valid. This lesson teaches you to design, understand, and enforce those relationships. It is the bridge between writing queries and designing databases.

1. Primary Keys: Uniquely Identifying Each Row

Every table in a relational database should have a primary key. This is a column or combination of columns whose value is guaranteed to be unique for every single row in the table. The primary key is the anchor. It is how you refer to a specific record without ambiguity. Without it, you cannot reliably update a row, delete a row, or link that row to another table.

Natural keys versus surrogate keys. A natural key is a column that already exists in your data and happens to be unique, like a national ID number, an email address, or a product SKU. A surrogate key is an artificial identifier created specifically for the database, like an auto-incrementing integer that has no meaning outside the system. Both can serve as primary keys, but surrogate keys are preferred in practice for several reasons. Natural keys can change: a customer might update their email, or a government might reissue ID numbers. Natural keys can be long: a twenty-character product code makes indexes larger and slower than a four-byte integer. Natural keys can be missing: not every person has a national ID. Surrogate keys are stable, compact, and always present. The standard pattern is to use an auto-incrementing integer named id or table_name_id as the primary key, and to enforce uniqueness on natural candidate keys like email with a separate UNIQUE constraint.

Declaring a primary key in MySQL. You have already seen this in the CREATE TABLE lesson, but it deserves emphasis:

CREATE TABLE customers (
  customer_id INT PRIMARY KEY AUTO_INCREMENT,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE,
  phone VARCHAR(20)
);

The PRIMARY KEY constraint on customer_id does three things. First, it enforces uniqueness: no two customers can share the same customer_id. Second, it creates an index on that column, which makes lookups by customer_id extremely fast. Third, it prevents NULL values, because a primary key must always have a value. AUTO_INCREMENT means MySQL generates the value automatically, so you never have to assign it manually when inserting a row.

Composite primary keys. Sometimes a single column is not enough to guarantee uniqueness. In a junction table that links students to courses, the combination of student_id and course_id is unique, even though each individual value appears many times. You declare a composite primary key like this:

CREATE TABLE enrollments (
  student_id INT NOT NULL,
  course_id INT NOT NULL,
  enrollment_date DATE DEFAULT (CURRENT_DATE),
  PRIMARY KEY (student_id, course_id)
);

This ensures a student cannot be enrolled in the same course twice. The order of columns in the composite key matters for index performance, but not for uniqueness. Put the more selective column first, the one with more distinct values, because MySQL uses the leftmost prefix of a composite index for lookups.

Verifying primary key behavior. Try inserting two rows with the same primary key value, or inserting a row without specifying the primary key on a non-auto-increment column. MySQL rejects both with an error. This is not a suggestion. It is a hard rule enforced by the database engine. That enforcement is what makes relational databases trustworthy for critical data.

2. Foreign Keys: Linking Tables Together

A foreign key is a column in one table that references the primary key of another table. It is the mechanism that creates relationships. Without foreign keys, tables are isolated islands. With foreign keys, they become a network where data in one table has meaning relative to data in another.

The foreign key concept. Imagine a customers table with customer_id as the primary key, and an orders table where every order belongs to one customer. The orders table needs a column, typically named customer_id, that stores the identifier of the customer who placed the order. This column is the foreign key. It does not contain customer names or emails. It contains only the integer that points to the correct row in the customers table. When you need the customer's name, you join the tables using this foreign key, which you will learn in the next lesson.

Declaring a foreign key in MySQL. Here is how to create the orders table with a foreign key referencing customers:

CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
  total_amount DECIMAL(12, 2) NOT NULL,
  status VARCHAR(20) DEFAULT 'pending',
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

The FOREIGN KEY line tells MySQL that the customer_id column in orders must contain a value that already exists in the customer_id column of the customers table. If you try to insert an order with customer_id 999 and no customer with that ID exists, MySQL rejects the insert. This prevents orphan records, orders that point to non-existent customers.

ON DELETE and ON UPDATE actions. By default, if you try to delete a customer who has orders, MySQL blocks the deletion because it would leave orphan orders. You can change this behavior by adding ON DELETE or ON UPDATE clauses:

FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
  ON DELETE CASCADE
  ON UPDATE CASCADE

ON DELETE CASCADE means if a customer is deleted, all their orders are automatically deleted too. This keeps the database consistent but is dangerous if you need to preserve order history for auditing. ON DELETE SET NULL means if a customer is deleted, the customer_id in their orders becomes NULL instead of pointing to a deleted customer. This preserves the orders but breaks the link. ON DELETE RESTRICT, the default, simply prevents deletion of a customer who has orders. Choose the behavior that matches your business rules. For financial data, RESTRICT or SET NULL is usually safer than CASCADE.

Foreign key requirements. For a foreign key to work, the referenced column must be indexed. In MySQL, primary keys are automatically indexed, so referencing a primary key always works. The referencing column, the foreign key itself, must have the same data type as the referenced column. An INT foreign key cannot reference a BIGINT primary key. The tables must use the InnoDB storage engine, which is the default in modern MySQL. If you are using an older MySQL version or a non-standard engine, foreign keys may be silently ignored.

3. One-to-One, One-to-Many, and Many-to-Many Relationships

These three relationship types describe how many rows in one table can relate to how many rows in another. Choosing the correct type for each pair of entities is the core of database design. Get it wrong and you will duplicate data, create update anomalies, or make queries unnecessarily complex.

One-to-one: one row relates to exactly one row. This is the least common relationship type. It occurs when you split a single entity across two tables for organizational or security reasons. For example, an employees table might store public information like name and department, while a separate employee_details table stores sensitive information like salary and home address. Each employee has exactly one details record, and each details record belongs to exactly one employee. The foreign key is placed on either side, but it must be unique to enforce the one-to-one constraint:

CREATE TABLE employee_details (
  employee_id INT PRIMARY KEY,
  salary DECIMAL(12, 2),
  home_address VARCHAR(200),
  FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

Notice that employee_details uses employee_id as both its primary key and its foreign key. This guarantees one details record per employee and prevents an employee from having multiple details records.

One-to-many: one row relates to many rows. This is the most common relationship. One customer has many orders. One category contains many products. One department employs many staff. The foreign key always goes on the many side. In a one-to-many between customers and orders, the orders table gets the customer_id foreign key because there are many orders per customer. You would never put an order_id column in the customers table, because a customer might have dozens of orders and storing them in a single row would require a list or array, which violates first normal form.

Many-to-many: many rows relate to many rows. A student enrolls in many courses. A course has many students. A product belongs to many categories. A category contains many products. You cannot implement a many-to-many relationship directly with a single foreign key in either table, because that would only allow one value per row. Instead, you create a junction table, also called an associative table or linking table, that sits between the two main tables and contains foreign keys to both:

CREATE TABLE students (
  student_id INT PRIMARY KEY AUTO_INCREMENT,
  first_name VARCHAR(50),
  last_name VARCHAR(50)
);

CREATE TABLE courses (
  course_id INT PRIMARY KEY AUTO_INCREMENT,
  course_name VARCHAR(100),
  credits INT
);

CREATE TABLE enrollments (
  student_id INT NOT NULL,
  course_id INT NOT NULL,
  enrollment_date DATE DEFAULT (CURRENT_DATE),
  grade VARCHAR(2),
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

The enrollments table has no meaning on its own. It exists only to connect students to courses. Each row represents one enrollment. A student appears in enrollments once for every course they take. A course appears once for every student enrolled. The composite primary key prevents duplicate enrollments. Additional columns like enrollment_date and grade store information about the relationship itself, information that belongs neither to the student nor to the course alone.

When designing a database, ask these questions for every pair of entities. Can one A have many B? Can one B have many A? If the answer to both is no, it is one-to-one. If the answer to the first is yes and the second is no, it is one-to-many. If the answer to both is yes, it is many-to-many and requires a junction table. This simple framework prevents most design errors before you write a single CREATE TABLE statement.

4. Entity Relationship Diagram Basics

An Entity Relationship Diagram, or ERD, is a visual map of your database. It shows every table as a box, every column as a line inside that box, and every relationship as a line connecting two boxes. ERDs are the universal language of database design. You draw them before you write SQL so you can spot design flaws while they are still easy to fix. You share them with teammates so everyone understands the data model. You reference them six months later when you have forgotten why a table was structured a certain way.

ERD notation: crow's foot. The most common notation system uses crow's foot symbols to show cardinality. A straight line with a single perpendicular bar at one end means one. A line with a crow's foot, three branching lines, at one end means many. The combination tells you the relationship type. A line with a bar on one end and a crow's foot on the other means one-to-many. A line with bars on both ends means one-to-one. A line with crow's feet on both ends means many-to-many, though in a properly normalized ERD, many-to-many is always resolved through a junction table drawn between the two main entities.

Drawing an ERD in MySQL Workbench. MySQL Workbench has a built-in ERD tool called the Model Editor. Go to File → New Model to start a new diagram. Double-click Add Diagram to open the canvas. In the left panel, click the table icon, then click on the canvas to place a new table. Double-click the table to edit its name and columns. Set data types, primary keys, and NOT NULL constraints in the column editor. To create a relationship, click the relationship icon in the toolbar, then click the parent table, the one with the primary key, and then the child table, the one that will receive the foreign key. MySQL Workbench automatically draws the connecting line and adds the foreign key column to the child table. This is faster and less error-prone than writing ALTER TABLE statements by hand.

Forward engineering from ERD to SQL. Once your ERD is complete, MySQL Workbench can generate the entire CREATE TABLE script automatically. Go to Database → Forward Engineer and follow the wizard. It produces a script with all tables, columns, data types, primary keys, foreign keys, and indexes in the correct dependency order. Tables are created in an order that respects foreign key references, so parent tables are created before child tables. This eliminates the manual work of deciding which table to create first.

Reverse engineering from existing database to ERD. If you inherit an existing database and need to understand its structure, go to Database → Reverse Engineer. Connect to your database, select the schemas you want to diagram, and MySQL Workbench generates a visual ERD from the live schema. This is invaluable when joining a project with an existing database. Instead of reading dozens of DESCRIBE outputs, you see the entire structure at a glance.

ERD best practices. Keep your diagram focused. An ERD with fifty tables is unreadable. Group related tables into subject areas and create separate diagrams for each. Use consistent naming conventions: singular nouns for tables, snake_case for column names, id for the primary key, and parent_table_id for foreign keys. Add notes to explain business rules that are not obvious from the column names. A good ERD is a communication tool, not just a technical artifact. It should be understandable by a non-technical stakeholder who needs to validate that the data model matches their understanding of the business.

5. Referential Integrity

Referential integrity is the guarantee that every foreign key value in your database points to a valid primary key value in the referenced table. It is the contract that makes relationships trustworthy. Without it, your orders table might contain customer_id values that do not exist in the customers table. Your enrollments table might reference courses that were deleted. Your data becomes inconsistent, reports become unreliable, and applications crash when they try to look up related information that is not there.

How foreign keys enforce referential integrity. When you declare a FOREIGN KEY constraint, MySQL becomes the gatekeeper. It checks every INSERT into the child table to ensure the foreign key value exists in the parent table. It checks every UPDATE to the foreign key column to ensure the new value is valid. It checks every DELETE from the parent table to ensure no child rows would be orphaned, unless you have specified ON DELETE CASCADE or ON DELETE SET NULL. These checks happen automatically, at the database level, regardless of which application or user is sending the query. This is why foreign keys are superior to application-level validation. An application bug might forget to check a reference. The database never forgets.

Cascading updates and deletes. ON UPDATE CASCADE ensures that if a primary key value changes, all referencing foreign keys are updated automatically to match. This is rare with surrogate keys because they never change, but useful if you are using natural keys that might be corrected. ON DELETE CASCADE removes child rows when the parent is removed. Use this with caution. In an e-commerce system, deleting a customer should probably not delete their order history, because that history is needed for tax and accounting. ON DELETE SET NULL is safer for such cases: the foreign key becomes NULL, the child row remains, and you can still query it, though the link to the parent is broken.

Checking referential integrity status. To see all foreign keys in your database and their rules, query the information_schema:

SELECT
  table_name,
  column_name,
  constraint_name,
  referenced_table_name,
  referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema = 'webbo3_retail'
  AND referenced_table_name IS NOT NULL;

This returns every foreign key in the webbo3_retail database, showing which table and column reference which parent table and column. Run this query on any unfamiliar database to map its relationships quickly.

Referential integrity in data analysis. As an analyst, you do not always have permission to create or modify foreign keys. You often work with databases designed by others. But understanding referential integrity helps you write better queries. If you know that orders.customer_id is a foreign key to customers.customer_id, you can safely join the two tables without worrying about orphan records. If you discover that no foreign key exists, you must be more careful. A LEFT JOIN might reveal orders with NULL customer information, which indicates either a data quality problem or a legitimate business case, like guest checkout. Knowing the intended relationships lets you distinguish between bugs and features in the data.

Using AI to Move Faster in Database Design

Database design is a creative and analytical skill that requires human judgment. But the mechanical work, writing CREATE TABLE statements, choosing data types, and generating ERDs, can be accelerated dramatically with AI assistance.

1. Generate complete schemas from business descriptions.
Instead of starting with a blank ERD canvas, describe your application to AI in plain language: "I am building a library management system with books, authors, borrowers, loans, and fines. Authors can write many books. A book can have multiple authors. Borrowers can loan many books. Each loan tracks the borrow date, due date, and return date. Fines are calculated based on overdue days. Generate the complete MySQL schema with primary keys, foreign keys, and appropriate data types." AI will produce a full set of CREATE TABLE statements, usually with correct relationship types and junction tables where needed. Your job is to review every data type, verify the cardinality assumptions match your business rules, and adjust ON DELETE behaviors to match your retention policies.

2. Use AI to explain ERD notation and validate your diagrams.
If you are unsure whether a relationship should be one-to-many or many-to-many, describe the business scenario to AI: "A product can be in multiple categories, and a category can contain multiple products. Is this many-to-many, and do I need a junction table?" AI will confirm it is many-to-many, explain why a junction table is required, and suggest column names like product_category with product_id and category_id as a composite primary key. You can also upload a screenshot of your ERD and ask AI to critique it for normalization issues, missing relationships, or redundant columns.

3. Generate sample data that respects referential integrity.
Creating realistic test data manually is tedious, and it is easy to accidentally insert a foreign key value that does not exist. Ask AI: "Generate twenty INSERT statements for a customers table and fifty INSERT statements for an orders table, where every order references a valid customer_id from the customers table. Include realistic Nigerian names, Lagos and Abuja addresses, and order amounts between 5,000 and 500,000 naira." AI will generate coherent data where every foreign key is valid, saving you from referential integrity errors during testing.

4. Debug foreign key errors with AI.
MySQL foreign key error messages can be cryptic. If you get ERROR 1452: Cannot add or update a child row, paste the error, your CREATE TABLE statements, and your INSERT statement into an AI assistant: "Why is this INSERT failing with a foreign key constraint error?" AI will identify that you are inserting customer_id 15 into orders when the customers table only has IDs 1 through 10, or that your foreign key and primary key data types do not match, or that the parent table uses MyISAM instead of InnoDB. This turns a frustrating error into a quick fix.

5. Verify AI-generated schemas against normalization principles.
AI can generate schemas that look correct but violate normalization rules. A common issue is storing calculated values, like total_order_value, in a table when they can be derived from other columns. Another is storing multi-valued attributes in a single column, like a comma-separated list of tags. After receiving an AI-generated schema, ask yourself: does every table have a primary key? Are repeating groups eliminated? Does every non-key column depend on the whole primary key, not just part of it? Does every column depend only on the primary key, not on other non-key columns? AI accelerates drafting, but the final design judgment is always yours.

A habit worth building from this lesson onward: before creating any new table, sketch the ERD on paper or in a digital whiteboard, then ask AI to generate the SQL, then audit every relationship, data type, and constraint against your understanding of the business. The time you spend validating the design upfront is repaid tenfold by the time you save not fixing data inconsistencies later.

Next lesson: JOIN operations, combining data from multiple tables with INNER JOIN, LEFT JOIN, and more.