MySQL中如何为不同数据类型的字段创建复合键

Hey there! Let's troubleshoot why your composite key creation is failing in MySQL. The good news is that mixed data types for Id and Name aren't the problem—MySQL fully supports composite keys with different column types. The issue almost always comes down to syntax oversights or missing constraints.

First, Here's the Correct Syntax for Creating Your Table with a Composite Key #

Let's start with working examples. I'll assume common data types for Id (INT) and Name (VARCHAR), but you can swap these out for your actual types (like UUID, BIGINT, TEXT, etc.) without issues.

Option 1: Define the Composite Primary Key Directly

CREATE TABLE your_table_name (
    Id INT NOT NULL, -- Critical: Primary key columns can't be nullable
    Name VARCHAR(255) NOT NULL, -- Same here—must be NOT NULL
    Price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (Id, Name) -- This is your composite key
);

Option 2: Use a Named Constraint (Better for Maintainability)

If you want to give your composite key a clear name (helpful for future schema changes), use this syntax:

CREATE TABLE your_table_name (
    Id INT NOT NULL,
    Name VARCHAR(255) NOT NULL,
    Price DECIMAL(10,2) NOT NULL,
    CONSTRAINT pk_product_id_name PRIMARY KEY (Id, Name)
);

Common Mistakes That Cause Errors #

Let's go over the most frequent issues that lead to failures:

• Missing NOT NULL on key columns: MySQL requires all columns in a primary key to be non-nullable. If either Id or Name doesn't have NOT NULL, you'll get an error.
• Syntax typos: Check for missing commas, mismatched parentheses, or misspelled keywords (like PRIMAY instead of PRIMARY).
• Duplicate constraint names: If you already have a constraint named pk_product_id_name in your database, you'll get a conflict—use a unique name for each constraint.
• Confusing primary key vs. unique key: If you meant to create a composite unique key (not a primary key), use UNIQUE KEY instead of PRIMARY KEY:

  CREATE TABLE your_table_name (
      Id INT NOT NULL,
      Name VARCHAR(255) NOT NULL,
      Price DECIMAL(10,2) NOT NULL,
      UNIQUE KEY uk_id_name (Id, Name)
  );
  

Example of a Broken Statement (and Fix) #

Here's a common error scenario—notice the missing NOT NULL constraints:

Broken code (will throw an error):

CREATE TABLE your_table_name (
    Id INT, -- Oops, no NOT NULL
    Name VARCHAR(255), -- Oops, no NOT NULL
    Price DECIMAL(10,2),
    PRIMARY KEY (Id, Name)
);

Fix: Add NOT NULL to both Id and Name columns as shown in the working examples above.

If you share the exact error message you're getting, I can narrow it down even further, but these fixes should resolve most common issues with composite key creation.

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