MySQL Tables
LESSON 2
RENEE CLINT P. GORTIFACION, MSIT
Topic 1: MySQL Keys and
Relationships
In a relational database like MySQL, keys and relationships are fundamental
concepts used to organize data, enforce data integrity, and create connections
between tables. Keys uniquely identify rows in a table, while relationships define
how tables are linked together through the use of keys. Understanding and
implementing keys and relationships properly ensures that the database can
efficiently store and retrieve data while maintaining consistency across related
entities.
This topic will explore the different types of keys in MySQL, the various types of
relationships, and how they work together to create a well-structured relational
database.
What are MySQL Keys?
Keys in MySQL are columns or sets of columns used to uniquely
identify rows in a table. They play a crucial role in maintaining data
integrity, establishing relationships between tables, and optimizing
database performance.
Comprehensive Guide to MySQL Keys and
Relationships
1. Primary Keys
A Primary Key is a column (or a set of columns) that uniquely identifies each record in a table. A primary
key ensures that no two rows in the table can have the same value for the primary key column(s), and it
cannot contain null values.
2. Foreign Keys
A Foreign Key is a column (or set of columns) in one table that refers to the Primary Key of
another table. It is used to establish and enforce a link between the data in two tables. Foreign
keys ensure referential integrity by preventing invalid data entry that would break the
relationship between tables.
3. Unique Keys
A unique key ensures that all values in a column are different. Unlike primary keys, a table
can have multiple unique keys.
Characteristics of Unique Key:
•Uniqueness: Guarantees that no two rows
have the same value in the constrained
column(s).
•Allows Null: Can allow null values (if
defined), but only one null is allowed.
4. Composite Keys
Composite keys use a combination of two or more columns to create a unique identifier
for each record. These are useful when no single column uniquely identifies a record.
5. Relationships in MySQL
1. One-to-One (1:1) Relationship
A One-to-One relationship exists when one record in a table is associated with exactly one
record in another table. In this case, the primary key in one table corresponds to the primary
key in another table.
Example:
In an employee management system, each employee may have one unique company-
issued laptop. A One-to-One relationship links the Employee and Laptop tables.
1 CREATE TABLE employees (
2 employee_id INT PRIMARY KEY,
3 name VARCHAR(100)
4 );
5
6 CREATE TABLE laptops (
7 laptop_id INT PRIMARY KEY,
8 employee_id INT UNIQUE,
9 FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
10 );
One-to-One: Each row in a table is linked to one, and only one, row in another table.
2. One-to-Many (1:N) Relationship
A One-to-Many relationship exists when one record in a table is associated with multiple
records in another table. This is one of the most common types of relationships in databases.
The primary key from the "one" side is linked to the foreign key in the "many" side.
Example:
A customer can place multiple orders. The Customers and Orders tables are connected in a
One-to-Many relationship.
1 CREATE TABLE orders (
2 order_id INT PRIMARY KEY,
3 customer_id INT,
4 order_date DATE,
5 FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
6 );
In this example, each customer can have multiple
orders, but each order belongs to only one
customer.
One-to-Many: A record in one table can be associated with one or more records in another
table.
3. Many-to-Many (M:N) Relationship
A Many-to-Many relationship exists when multiple records in one table are related to
multiple records in another table. This type of relationship is represented by creating a
junction table (or bridge table) that contains foreign keys referencing both tables.
Example:
A student can enroll in many courses, and each course can have many students. The
Students and Courses tables are connected through a course_enrollment junction table.
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(100)
);
CREATE TABLE course_enrollment (
student_id INT,
course_id INT,
enrollment_date DATE,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
Many-to-Many: Records in one table can relate to multiple records in another, and vice versa,
typically managed using a junction table.
Referential Integrity
Referential Integrity is a key concept in relational databases that ensures relationships between
tables remain consistent. A foreign key constraint ensures that every value in a foreign key column
must match an existing value in the referenced primary key column, preventing orphan records
and maintaining database consistency.
Referential Actions:
• ON DELETE CASCADE: Deletes records in the child table when the referenced record in the parent table is deleted.
• ON UPDATE CASCADE: Updates foreign key values in the child table when the primary key in the parent table is updated.
• SET NULL: Sets the foreign key to null when the referenced record is deleted or updated.
Example:
If you delete a customer in the Customers table, all corresponding records in the Orders table are deleted if ON DELETE
CASCADE is applied.
Conclusion
In MySQL, keys and relationships are essential for structuring
relational databases and ensuring data integrity. Keys, such as
primary keys, foreign keys, and unique keys, help uniquely identify
records and enforce data validation. Relationships, such as one-to-
one, one-to-many, and many-to-many, define how tables interact
with each other and establish referential integrity. By implementing
these concepts correctly, database designers can create scalable,
efficient, and consistent databases.
Topic 2: MySQL Constraints
MySQL constraints are rules applied to table columns that enforce the integrity
and accuracy of the data in a database. These constraints are crucial for
ensuring that the data stored in a database is reliable and follows specific
standards. Understanding and implementing constraints is essential for database
management, data integrity, and effective data manipulation in MySQL.
This topic will explore the different types of constraints in MySQL, their use cases, and how
to apply them when creating or altering tables.
Comprehensive Guide to MySQL Constraints
1. NOT NULL Constraint
The NOT NULL constraint ensures that a column cannot have a NULL value. It is used to
enforce a field to always contain a value, which means you cannot insert a new record, or
update a record without adding a value to this field.
In this example, first_name and last_name are
defined as NOT NULL, meaning you must provide
values for these fields when inserting new records.
2. UNIQUE Constraint
• The UNIQUE constraint ensures that all values in a column are different. This provides a way
to enforce the uniqueness of a column or a combination of columns.
Here, the email column must contain unique values.
No two employees can have the same email
address.
Adding a Unique Constraint to
Multiple Columns:
You can apply a composite UNIQUE constraint to more than one
column.
This ensures that the combination of product_id and
customer_id is unique, meaning a customer cannot
place the same order for the same product more
than once.
3. PRIMARY KEY Constraint
• A PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must
contain unique values and cannot contain NULL values. A table can have only one primary
key, which may consist of single or multiple columns.
In this example, customer_id is the primary key,
meaning it will uniquely identify each customer and
cannot be null or duplicate.
Composite Primary Key:
You can define a primary key on multiple columns:
This ensures that the combination of order_id and
product_id is unique within the order_details table.
4. FOREIGN KEY Constraint
The FOREIGN KEY constraint enforces a link between two tables. A foreign key in one
table points to a primary key or a unique key in another table, ensuring referential
integrity between the tables.
In this example, customer_id in the orders
table is a foreign key that references
customer_id in the customers table. This
ensures that every order is associated
with a valid customer.
On Delete and Update Cascade:
You can define actions for foreign keys to automatically update or
delete related rows in the child table when a record in the parent
table is updated or deleted.
Here, if a customer is deleted from the customers table, their related orders in the orders
table will also be deleted.
5. CHECK Constraint
• The CHECK constraint ensures that all values in a column satisfy a specific condition. This
constraint is used to limit the value range that can be placed in a column.
This ensures that the age column
only allows values of 18 or greater,
enforcing a rule that employees must
be at least 18 years old.
6. DEFAULT Constraint
• The DEFAULT constraint is used to provide a default value for a column. The default value will
be added to all new records, if no other value is specified.
In this example, if a price is not
specified when inserting a product,
the default value of 0.00 will be used.
Scenario: Creating and Using Constraints in a
Database
Imagine you're developing a Sales Management System for a company. You need to create a
database that stores information about customers, orders, and products, ensuring data integrity
with constraints.
CREATE TABLE customers (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
join_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2) CHECK (price >= 0) DEFAULT 0.00
);
CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT,
product_id INT,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
Conclusion
MySQL constraints play a crucial role in maintaining the accuracy,
integrity, and consistency of your database. By applying constraints
such as NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and
DEFAULT, you can prevent invalid data from being entered and ensure
relationships between tables are preserved. Understanding and using
constraints effectively helps create a well-structured, reliable database
that supports your application’s needs.
Topic 3: MySQL Table Manipulation
MySQL table manipulation involves performing various operations to manage the
structure of tables in a database. These operations include creating tables,
modifying existing tables, and deleting tables. Table manipulation is essential for
maintaining the structure and integrity of the database as the data requirements
evolve.
This topic will explore how to manipulate tables in MySQL, including how to
create, modify, rename, and delete tables. It will also cover how to add and
remove columns and constraints.
Modifying an Existing Table
There are times when you need to change the structure of an existing table, such as
adding new columns, modifying existing columns, or adding constraints. The ALTER TABLE
statement allows you to perform these operations.
CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
quantity INT DEFAULT 0,
date_added TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
ALTER TABLE products
ADD COLUMN supplier_id INT;
ALTER TABLE products rename to inventory;
Conclusion
MySQL table manipulation involves various operations, such as creating, modifying,
renaming, and deleting tables, as well as adding or removing columns and
constraints. These operations allow you to maintain and adjust the database
structure as requirements change. Understanding how to manipulate tables
efficiently is essential for managing the evolving needs of your database.
Topic 4: MySQL ON DELETE Clause
The ON DELETE clause in MySQL is used in conjunction with foreign keys to define
what happens when a referenced record in a parent table is deleted. It is an
essential part of maintaining referential integrity in a relational database,
ensuring that relationships between tables are preserved or managed correctly
when deletions occur.
This topic will explore the ON DELETE clause, the various actions you can define
for it, and how to apply it when setting up foreign keys between related tables.
Example:
Conclusion
By defining appropriate ON DELETE behaviors during table creation, you ensure
the integrity and consistency of data within your relational databases. Each of
these behaviors serves different use cases depending on how you wish to
manage the dependencies between your data entities.
The MySQL ON DELETE clause is a powerful tool for maintaining referential integrity
between related tables. By specifying actions like CASCADE, SET NULL, RESTRICT,
or NO ACTION, you can control how child records are handled when a parent
record is deleted. Understanding when and how to use these actions ensures
that your database maintains consistency and that relationships between tables
are managed correctly.