This article focuses on four core MySQL table constraints: PRIMARY KEY, AUTO_INCREMENT, UNIQUE, and FOREIGN KEY. Together, they solve common problems such as duplicate data, broken parent-child relationships, and inconsistent ID management. Keywords: MySQL constraints, primary key, foreign key.
Technical Specifications Snapshot
| Parameter | Description |
|---|---|
| Language | SQL / MySQL |
| Database Protocol | MySQL client-server protocol |
| Stars | Not provided in the source content |
| Core Dependencies | MySQL 5.7+ / 8.0+, InnoDB engine |
| Use Cases | Table design, data integrity enforcement, relational modeling |
MySQL table constraints move business rules into the database layer
Constraints are not just syntax decorations. They are hard validation rules enforced by the database to guarantee data validity. Even when the application layer misses a check, constraints still provide a safety net and prevent dirty data from entering your schema.
This article covers four of the most common advanced constraints: primary keys, auto-increment columns, unique keys, and foreign keys. They address four distinct problems: unique row identification, automatic ID generation, deduplication of business fields, and consistency across related tables.
Primary keys identify each row in a stable and unique way
A primary key follows three simple rules: its value must be unique, it cannot be NULL, and each table can define only one primary key. That “one” refers to a single primary key definition, which may consist of multiple columns as a composite primary key.
Primary keys typically use integer types and often work together with AUTO_INCREMENT. This combination improves query performance, keeps indexing stable, and makes the key easy for other tables to reference through foreign keys.
CREATE TABLE student (
id INT PRIMARY KEY, -- Primary key: uniquely identifies a student
name VARCHAR(50) NOT NULL
);
INSERT INTO student VALUES (1, 'Alice');
INSERT INTO student VALUES (1, 'Bob'); -- Duplicate primary key, this will failThis SQL example demonstrates the uniqueness guarantee of a primary key: MySQL will reject rows with duplicate primary key values.
AI Visual Insight: The image shows that when you insert a duplicate primary key value, MySQL returns a constraint violation error. This confirms that PRIMARY KEY rejects duplicate id values at write time and relies on the underlying unique index to enforce row-level uniqueness.
Existing tables can also have primary keys added or removed later
If you forget to declare a primary key during table creation, you can add one later with ALTER TABLE. However, the target column must not contain duplicate values or NULL values, or the change will fail.
Dropping a primary key usually happens during schema refactoring. In production, you should handle this carefully because indexes, foreign keys, and application code may already depend on it.
ALTER TABLE student ADD PRIMARY KEY (id); -- Add a primary key to an existing column
ALTER TABLE student DROP PRIMARY KEY; -- Drop the primary keyThis SQL example shows that you can add or remove a primary key after table creation, but you should always evaluate existing data and dependencies first.
AI Visual Insight: The image shows the console result of dropping a primary key. The key point is that DROP PRIMARY KEY removes both the primary key constraint and its associated primary index definition, so future inserts are no longer protected by primary key uniqueness.
AI Visual Insight: The image shows the process of adding a primary key to an existing table with data. It highlights that MySQL supports online schema changes for constraints, but checks whether the target column satisfies the required “non-null and unique” conditions before applying the change.
Composite primary keys fit business models with natural multi-column uniqueness
When a single column cannot uniquely identify a record, you should use a composite primary key. For example, in an enrollment table, the combination of student_id and course_id is naturally unique, while either column alone is insufficient.
CREATE TABLE score (
student_id INT,
course_id INT,
grade INT,
PRIMARY KEY (student_id, course_id) -- Unique as a combined key
);This SQL pattern works well for junction tables and prevents duplicate enrollment records.
AI Visual Insight: The image shows the syntax placement and execution result of a composite primary key. It emphasizes that PRIMARY KEY (column1, column2) enforces uniqueness on the combination of columns, not on each column independently.
AUTO_INCREMENT gives integer primary keys automatic allocation
AUTO_INCREMENT automatically generates increasing integer values and is commonly used for surrogate primary keys. When you omit the column during insertion, MySQL generates a new value based on the current maximum plus one.
It has three prerequisites: the column must use an integer type, it must be indexed, and each table can have only one auto-increment column. In practice, you usually attach it directly to the primary key column.
CREATE TABLE user_info (
id INT PRIMARY KEY AUTO_INCREMENT, -- Auto-increment primary key
name VARCHAR(50) NOT NULL
);
INSERT INTO user_info(name) VALUES ('Tom');
INSERT INTO user_info(name) VALUES ('Jerry');This SQL example removes the cost of manually assigning IDs and avoids duplicate primary key conflicts under concurrent writes.
AI Visual Insight: The image shows the result after creating a table with an auto-increment column and inserting rows. It demonstrates that when id is not explicitly provided, MySQL automatically generates sequential integer values for the primary key column.
Manually inserting a larger ID changes the future auto-increment baseline
If you manually insert a value larger than the current sequence, subsequent auto-increment values typically continue from that new maximum. This is one reason why manually writing primary key values in production is usually a bad idea.
To get the most recent auto-increment value generated by the current connection, use LAST_INSERT_ID(). Note that for a multi-row insert, it returns the first generated auto-increment value for that batch.
INSERT INTO user_info(id, name) VALUES (100, 'Admin'); -- Manually specify a larger ID
INSERT INTO user_info(name) VALUES ('NextUser');
SELECT LAST_INSERT_ID(); -- Query the starting auto-increment value of the most recent insertThis SQL example shows that the auto-increment sequence does not simply increase mechanically by one. It also depends on the current maximum primary key value.
AI Visual Insight: The image shows the result of inserting a manually specified id and then inserting a new row. It demonstrates that MySQL continues assigning the next auto-increment value from the current maximum primary key rather than returning to the old sequence.
AI Visual Insight: The image shows the query result of LAST_INSERT_ID(). It reflects that this function returns the auto-increment value generated by the most recent successful insert on the current connection and is often used when inserting child rows after a parent row.
UNIQUE prevents duplicate values in business fields
Both unique keys and primary keys prevent duplicates, but they serve different purposes. A primary key emphasizes row identity, while a unique key enforces business-level uniqueness for fields such as phone numbers, email addresses, or national ID numbers.
A unique key can allow NULL, and a table can define multiple unique keys. That makes it ideal for enforcing additional business rules rather than replacing the primary key.
| Feature | Primary Key | Unique Key |
|---|---|---|
| Count | One primary key definition per table | Multiple allowed per table |
| Allows NULL | No | Usually yes |
| Main Purpose | Uniquely identifies a row | Prevents duplicate business values |
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(100) UNIQUE, -- Email must be unique
phone VARCHAR(20) UNIQUE -- Phone number must be unique
);This SQL example shows that unique keys are more flexible and can support multiple independent business constraints.
AI Visual Insight: The image shows insert behavior under a unique key constraint. The key takeaway is that the database rejects duplicate business field values, preventing repeated records for critical fields such as email addresses and phone numbers.
FOREIGN KEY enforces consistency between parent and child tables
A foreign key defines a reference relationship between tables. You usually declare it on the child table, and the referenced column in the parent table must be a primary key or a unique key. The core rule is simple: a foreign key value in the child table must either exist in the parent table or be NULL.
This kind of constraint does not solve duplicate-value problems within a single table. Instead, it solves cross-table consistency problems. For example, a class_id in the student table must point to a real class record, or the business relationship becomes invalid.
CREATE TABLE class_info (
class_id INT PRIMARY KEY, -- Parent table primary key
class_name VARCHAR(50) NOT NULL
);
CREATE TABLE student_info (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
class_id INT,
FOREIGN KEY (class_id) REFERENCES class_info(class_id) -- Foreign key constraint
);This SQL example creates the reference relationship between the class table and the student table, with MySQL validating referential integrity for you.
AI Visual Insight: The image shows the creation of the parent table. The key point is that you define the primary key on the referenced column first, because a foreign key can reference only a parent column that already has uniqueness through a primary key or unique key.
AI Visual Insight: The image shows the syntax used to declare a foreign key when creating the child table. It demonstrates how FOREIGN KEY (class_id) REFERENCES class_info(class_id) binds a student’s class assignment to the valid record set in the parent table.
Foreign keys stop invalid business data before it is written
If you insert a student record with class_id = 104, but no class 104 exists in the parent table, MySQL rejects the insert immediately. This prevents logical corruption such as “the student has been assigned to a class that does not exist.”
However, if class_id is nullable, inserting NULL remains valid and simply indicates that the student has not yet been assigned to a class. This is a common “unrelated state” in real-world systems.
INSERT INTO class_info VALUES (101, 'Class 1');
INSERT INTO student_info(name, class_id) VALUES ('Zhang San', 101); -- Valid
INSERT INTO student_info(name, class_id) VALUES ('Li Si', 104); -- Invalid, parent row does not exist
INSERT INTO student_info(name, class_id) VALUES ('Wang Wu', NULL); -- Valid, not assigned to a class yetThis SQL example directly shows the value of foreign keys: MySQL validates relational consistency instead of leaving all checks to the application layer.
AI Visual Insight: The image shows a successful insert when the parent-child reference is valid. It demonstrates that foreign keys do not interfere with valid writes and only block operations that violate referential integrity.
AI Visual Insight: The image shows the error message raised when inserting a non-existent class ID. It demonstrates that a foreign key checks at execution time whether the child value exists in the parent candidate key set, and if not, the statement fails and rolls back.
Constraint design should prioritize maintainability
Start by giving every table a stable primary key. Then use unique keys to enforce business-level deduplication, use foreign keys to maintain relationship consistency, and reserve auto-increment for integer identifier columns. This is the safest path for relational modeling.
If you worry that foreign keys may complicate migrations or legacy-system compatibility, you can first establish discipline at the application and indexing layers, then introduce database constraints gradually. Over the long term, however, pushing rules down into the database is usually more reliable.
FAQ
1. How should I choose between a primary key and a unique key?
Use a primary key to uniquely identify each row. It must be non-null, and a table can have only one primary key definition. Use a unique key to prevent duplicates in business fields such as email addresses and phone numbers. A table can define multiple unique keys, but they usually should not replace the primary key.
2. Why is AUTO_INCREMENT usually paired with a primary key?
Because an auto-increment column must be an integer and should ideally be indexed. A primary key naturally satisfies both high-frequency lookup and uniqueness requirements. Together, they reduce the complexity of manual ID assignment.
3. Is a foreign key always required?
No, but it is strongly recommended for core business tables. It delegates cross-table relationship validation to MySQL and significantly reduces orphaned records, invalid references, and other forms of dirty business data.
[AI Readability Summary]
This article provides a systematic explanation of MySQL table constraints, including primary keys, AUTO_INCREMENT, unique keys, and foreign keys. It clarifies their rules, differences, and common use cases, and uses executable SQL examples to show how to enforce data integrity effectively.