编写MySQL代码的最佳实践：主键与外键定义方式疑问

Great question—this is such a common sticking point when you’re first learning MySQL schema design, so let’s unpack this clearly.

First, let’s clarify your instructor’s approach #

Your teacher mentioned creating all tables first then using ALTER TABLE to add keys for better naming—but their exercise answer uses CREATE TABLE for primary keys and ALTER for foreign keys. There’s a practical reason for this split:

Primary Keys: Why defining in CREATE TABLE is usually better

Primary keys are the core unique identifier for your table—they’re fundamental to the table’s purpose, so you’ll almost always know what your primary key is when you first design the table.

The auto-increment issue you ran into happens because MySQL requires AUTO_INCREMENT to be attached to a column that’s either a primary key or a unique non-null key. If you try to add a primary key via ALTER after creating the table without setting those properties first, MySQL will throw an error.

If you do need to add a primary key via ALTER (for example, refactoring an existing table), here’s the correct sequence to fix the auto-increment problem:

-- Step 1: Create the table without primary key
CREATE TABLE users (id INT, username VARCHAR(50) NOT NULL);

-- Step 2: Modify the column to be non-null, auto-increment, and set as primary key
ALTER TABLE users MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

Or you can split it into two steps if you prefer clarity:

ALTER TABLE users MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT;
ALTER TABLE users ADD PRIMARY KEY (id);

Either way, the key is ensuring the column is NOT NULL and tied to a unique key (primary or unique) before enabling AUTO_INCREMENT.

Foreign Keys: Why ALTER TABLE is the smarter choice

Foreign keys depend on other tables existing first (they reference a primary/unique key in another table). If you try to define a foreign key in CREATE TABLE before the referenced table exists, MySQL will throw an error.

By creating all tables first, then adding foreign keys via ALTER, you avoid circular dependency issues and get full control over naming (e.g., fk_order_customer instead of MySQL’s default generic names like orders_ibfk_1). This matches what your instructor recommended for key naming.

So what’s the best practice? #

• Primary Keys: Define them directly in CREATE TABLE whenever possible. It’s more intuitive, avoids auto-increment headaches, and makes your schema easier to read at a glance.
• Foreign Keys: Add them using ALTER TABLE after all tables are created. This is more flexible, prevents dependency errors, and lets you use meaningful names for your foreign keys.

Your instructor’s exercise answer is following this practical middle ground—prioritizing clarity and functionality for both key types.

内容的提问来源于stack exchange，提问作者Learning Java