调整MySQL表主键/外键，使grades表与users表保持id-username关联

Hey there! Let's sort out the primary and foreign key setup for your MySQL tables to ensure the id and username pairs in grades always match what's in users. Right now, the foreign key on grades is pointing to the wrong place, and we need to lock down that consistency.

First, Let's Diagnose the Current Issue #

Looking at your table structures:

• The grades table doesn't have a primary key defined (your CREATE TABLE only marks id as auto-increment but not a primary key)
• The foreign key on grades.id referencing users.id is problematic—it forces grades.id to exist in users.id, but since grades.id is auto-incrementing, this will break as soon as you add more grades than users. Worse, there's nothing stopping someone from adding a grades row where id=1 and username='b', which conflicts with users where id=1 maps to username='a'.

Step-by-Step Fixes #

Option 1: Keep username in grades and Lock Down the Id-Username Pair #

If you need to keep the username field in grades, we'll add a composite foreign key to ensure the (id, username) combination in grades exactly matches a row in users.

1. Add a primary key to grades first (every table should have one):

   ALTER TABLE grades ADD PRIMARY KEY (id);
   

2. Remove the broken foreign key
   First, find the name of the existing foreign key (since MySQL assigns a default name if you don't specify one):

   SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
   WHERE TABLE_NAME = 'grades' AND REFERENCED_TABLE_NAME = 'users';
   

   Then drop it (replace grades_ibfk_1 with the name you found):

   ALTER TABLE grades DROP FOREIGN KEY grades_ibfk_1;
   

3. Add the composite foreign key
   This ensures any (id, username) in grades must exist in users:

   ALTER TABLE grades ADD CONSTRAINT fk_grades_user_pair 
   FOREIGN KEY (id, username) REFERENCES users(id, username);
   

Option 2: Optimize the Schema (Remove Redundant username Field) #

A cleaner approach is to remove the username from grades entirely—you can always fetch it by joining with users using a user_id foreign key. This eliminates redundant data and reduces the chance of mismatches.

1. Check for invalid data first
   Make sure every username in grades exists in users:

   SELECT g.id, g.username FROM grades g
   LEFT JOIN users u ON g.username = u.username
   WHERE u.id IS NULL;
   

   Fix any mismatched rows before proceeding.

2. Add a user_id column to grades

   ALTER TABLE grades ADD COLUMN user_id INT;
   

3. Populate user_id with matching values from users

   UPDATE grades g
   JOIN users u ON g.username = u.username
   SET g.user_id = u.id;
   

4. Lock down the user_id column and add the foreign key

   ALTER TABLE grades MODIFY COLUMN user_id INT NOT NULL;
   ALTER TABLE grades ADD CONSTRAINT fk_grades_user 
   FOREIGN KEY (user_id) REFERENCES users(id);
   

5. Remove the redundant username column

   ALTER TABLE grades DROP COLUMN username;
   

Verify Everything Works #

After making changes, run these queries to confirm no mismatched data exists:

• For Option 1:

  SELECT g.* FROM grades g
  LEFT JOIN users u ON g.id = u.id AND g.username = u.username
  WHERE u.id IS NULL;
  

• For Option 2:

  SELECT g.* FROM grades g
  LEFT JOIN users u ON g.user_id = u.id
  WHERE u.id IS NULL;
  

If you get no results, your data is consistent!

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