Essential MySQL Database Terms
Learn essential MySQL and relational database terms, including databases, tables, rows, columns, primary keys, foreign keys, indexes, and relationships.
Understanding a few core terms makes MySQL much easier to learn. These terms describe how data is organized, identified, connected, and retrieved in a relational database.
MySQL is a relational database management system. It stores structured data, organizes that data into related tables, and provides SQL commands for creating, querying, and managing it.
Database
A database is an organized collection of related data. In MySQL, a database is a named collection of tables and other database objects used by an application or business function.
A single MySQL server can contain multiple databases. For example, one server might contain a shop database for an online store, a support database for customer service, and a reporting database for analytics. Separating data into databases can help organize applications and control access.
The database is the top-level organizational unit for an application's data. A database contains tables, and tables contain the actual structured records.
CREATE DATABASE shop;
USE shop;CREATE DATABASE creates a database, while USE selects the database for subsequent SQL statements. You can learn more in Create a Database.
Table
A table is a structured collection of related records inside a database. Tables usually represent an entity, category, or type of event, such as customers, products, or orders.
For example, a shop database might contain customers, products, and orders tables. The database provides the overall container; each table organizes one particular kind of information.
| Term | What it represents | Example |
|---|---|---|
| Database | A named collection of related tables and objects | shop |
| Table | A set of related data arranged in columns and rows | customers |
| Column | A named property stored for each row | email |
| Row | One complete record | One customer |
| Primary key | A key that uniquely identifies a row | customer_id |
| Foreign key | A reference to a key in another table | orders.customer_id |
| Index | A structure that helps MySQL locate rows efficiently | An index on email |
Columns and fields
A column is a named attribute or property stored for every record in a table. Columns define what kind of information the table can store. A customer table might have columns for an identifier, name, email address, and registration date.
Every column has a data type, which defines the kind of value it can store. Common examples include integers, text, dates, and decimal numbers. A column can also have constraints, which are rules applied to its values. Examples include NOT NULL, UNIQUE, and foreign-key rules.
The word field is used informally and can be ambiguous. In some discussions, a field means a column; in others, it means one data item at the intersection of a row and a column, sometimes called a cell. In formal MySQL table descriptions, prefer column for the table-wide attribute.
Rows and records
A row is one complete item in a table. A row is also called a record. The values in a row correspond to the table's columns in the same order.
| customer_id | customer_name | |
|---|---|---|
| 101 | Rina Patel | rina@example.com |
| 102 | Marco Lee | marco@example.com |
This table has three columns: customer_id, customer_name, and email. It has two rows. The first row is one complete customer record, and the second row is another. A column describes the same kind of property for every row; a row combines all the properties for one item.
Creating a table
The following statement creates the example customer table:
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE
);Here, INT stores integer identifiers, VARCHAR stores variable-length text, NOT NULL requires a customer name, and UNIQUE prevents duplicate non-null email values. See Data Types and Create a Table for related SQL concepts.
Primary keys
A primary key is one column or a combination of columns whose value uniquely identifies each row in a table. A table has one primary-key definition, but that definition can contain multiple columns; such a key is called a composite primary key.
Primary-key values must be unique, and primary-key columns cannot contain NULL. These rules ensure that MySQL can distinguish one record from every other record.
A primary key is useful when you need to:
- Identify one exact row.
- Update or delete the intended record.
- Reference the record from another table.
- Prevent duplicate identities.
A common pattern is an integer identifier such as customer_id, often generated automatically. Another option is a natural identifier: a value that already has meaning in the real world, such as an official product code. Natural identifiers can change or have unexpected duplicates, so an artificial integer identifier is often simpler for application relationships.
Every primary key is indexed by MySQL. However, a primary key is more than an index: it defines the table's main identity and enforces uniqueness and non-nullability. Read more in Primary Keys.
Foreign keys
A foreign key is a column or group of columns that references a key in another table. It models a relationship between entities.
The table containing the referenced key is the parent table. The table containing the foreign key is the child table. In a shop application, customers is the parent table and orders is the child table:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
total_amount DECIMAL(10,2) NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);Here, orders.customer_id references customers.customer_id. One customer can have many orders, so this is a one-to-many relationship: one row in customers can correspond to many rows in orders.
Referential integrity means that references between related tables remain valid. For example, MySQL normally will not allow an order to reference a customer ID that does not exist. It may also prevent deleting a customer while orders still reference that customer, depending on the configured constraint actions.
| Table | Key column | Key type | References or purpose |
|---|---|---|---|
customers | customer_id | Primary key | Uniquely identifies each customer |
orders | order_id | Primary key | Uniquely identifies each order |
orders | customer_id | Foreign key | References customers.customer_id |
Indexes
An index is an auxiliary data structure that helps MySQL locate matching rows more efficiently. It is similar to an index in a book: instead of examining every page, the database can use the index to narrow the search.
For example, if an application frequently searches for customers by name, an index may help:
CREATE INDEX idx_customers_name
ON customers (customer_name);If email addresses must be unique, a unique index is appropriate. The table definition above creates one through UNIQUE:
CREATE UNIQUE INDEX ux_customers_email
ON customers (email);Indexes can improve reads and lookups, but they have costs:
- They consume additional storage.
- MySQL must maintain them during inserts, updates, and deletes.
- Too many indexes can make write operations slower.
- An index is useful only when it supports real query patterns.
A primary key is automatically indexed. An ordinary index does not make a column a primary key and does not, by itself, create a relationship. A foreign-key constraint defines and enforces a relationship; an index improves access speed. MySQL may use indexes when checking or following relationships, but these concepts serve different purposes.
Primary key, foreign key, and index compared
| Feature | Main purpose | Uniqueness requirement | Relationship role | Performance role |
|---|---|---|---|---|
| Primary key | Identify each row | Required; never NULL | Can be referenced by other tables | Automatically indexed |
| Foreign key | Reference a row in another table | Usually not unique; many child rows may share a value | Defines and helps enforce a relationship | May benefit from an index |
| Ordinary index | Find rows faster | Not required | Does not define a relationship | Improves selected lookups |
| Unique index | Find rows and prevent duplicate values | Required for indexed non-null values | Does not necessarily define a table relationship | Improves lookups |
How the terms fit together
Think of the organization as a hierarchy:
- A MySQL server can contain multiple databases.
- A database contains tables.
- A table defines named columns.
- Each table contains rows, or records.
- A primary key identifies each row.
- A foreign key connects rows in related tables.
- An index helps MySQL find relevant rows efficiently.
For the shop example, the shop database contains customers and orders. The customers table has columns such as customer_id, customer_name, and email. Each customer is one row. The orders table has an order_id primary key and a customer_id foreign key. Several order rows may contain the same customer ID because one customer can place many orders.
Inspecting keys and indexes
Use these commands to examine a table after creating it:
DESCRIBE customers;
SHOW INDEX FROM customers;DESCRIBE shows columns, data types, and several key and nullability details. SHOW INDEX lists the indexes defined for the table, including the index created for the primary key and any unique or ordinary indexes.
Common problems and troubleshooting
Duplicate primary-key value
If an insert fails because an identifier already exists, the primary-key value is not unique. Use a new identifier or use an appropriate automatically generated identifier strategy.
Foreign-key insert fails
If an order uses a customer_id that does not match an existing customer, MySQL rejects the insert to protect referential integrity. Insert the parent customer first or use a valid existing ID.
Deleting a referenced customer fails
A customer may not be removable while orders still reference that customer. Decide whether dependent orders should also be deleted, whether the customer should be retained, or whether an intentional foreign-key delete action is appropriate. Do not remove related data accidentally.
Too many indexes slow writes
Indexes are not automatically beneficial on every column. Review the queries the application actually runs and add indexes for useful lookup, filtering, sorting, or join patterns.
Confusing a foreign key with an index
An indexed column is not automatically a foreign key. An index is a performance structure; a foreign-key constraint declares and enforces a valid reference between tables.
Exam-relevant notes
- A database contains tables; a table contains columns and rows.
- A column describes one attribute across records; a row represents one complete record.
- A primary key uniquely identifies every row and cannot be
NULL. - A table has one primary-key definition, which may contain multiple columns.
- A foreign key references a key in another table and supports referential integrity.
- One-to-many relationships commonly place the foreign key in the many-side, or child, table.
- An index can speed up reads but requires storage and maintenance during writes.
- A unique index prevents duplicate indexed values where the data model requires uniqueness.
For the next steps, review Insert New Records, Query a Database, and What Is MySQL.