MySQL外键语法咨询：创建dispatch表时外键添加失败求助

Hey there, let's work through your MySQL foreign key issue step by step.

First, let's clarify the correct syntax for foreign keys in MySQL:

Single-column foreign key #

This is the most common use case, referencing a primary key in the parent table:

CREATE TABLE child_table (
    column1 INT,
    -- Other column definitions
    PRIMARY KEY (column1),
    FOREIGN KEY (child_foreign_key_col) REFERENCES parent_table(parent_primary_key_col)
    -- Optional: Add ON DELETE/ON UPDATE rules (e.g., ON DELETE CASCADE)
) ENGINE=InnoDB; -- Critical: Only InnoDB supports foreign key constraints

Composite foreign key (for multi-column references) #

Use this only if the parent table relies on multiple columns to uniquely identify rows (a composite primary/unique key):

CREATE TABLE child_table (
    col1 INT,
    col2 DATE,
    -- Other columns
    PRIMARY KEY (col1),
    FOREIGN KEY (col1, col2) REFERENCES parent_table(p_col1, p_col2)
) ENGINE=InnoDB;

Why You're Getting ERROR 1215 #

That error almost always stems from one of these issues, and your query has a few clear red flags:

1. Missing composite key in the parent table: You're trying to reference 8 columns in the booking table, but booking almost certainly doesn't have a composite primary key or unique constraint covering all 8 of those columns. MySQL needs a unique identifier in the parent table to link to.
2. Redundant foreign key columns: Notice that booking_no is already the primary key of your dispatch table. If booking_no is the primary key of the booking table (which it should be), you don't need to include all those extra columns in the foreign key. The primary key alone uniquely identifies a row in booking—adding more columns is unnecessary and triggers this error.
3. Mismatched data types: Even if you did need the composite key, every column in dispatch must have exactly the same data type, length, and NULLability as the corresponding column in booking (e.g., VARCHAR(20) in dispatch must match VARCHAR(20) in booking, not VARCHAR(25)).
4. Wrong storage engine: If either dispatch or booking uses MyISAM instead of InnoDB, foreign keys won't work—MyISAM doesn't support them.

Fixes to Try #

Option 1: Simplify the Foreign Key (Recommended) #

Since booking_no is the primary key of booking, you only need to reference that single column. This is the standard approach and will fix your error immediately:

CREATE TABLE dispatch(
    booking_no INT,
    bdate DATE,
    src_stn VARCHAR(20),
    dest_stn VARCHAR(20),
    consignee VARCHAR(30),
    desc_goods VARCHAR(40),
    no_of_art SMALLINT,
    total FLOAT,
    driver_name VARCHAR(30),
    lorry_no VARCHAR(10),
    status VARCHAR(5),
    PRIMARY KEY (booking_no),
    FOREIGN KEY (booking_no) REFERENCES booking(booking_no)
    -- Optional: Add ON DELETE CASCADE if you want to delete dispatch rows when the booking is deleted
) ENGINE=InnoDB;

Option 2: Keep the Composite Foreign Key (If Required by Your Logic) #

If you really need to use all 8 columns as a foreign key (unlikely, but possible), first make sure booking has a composite unique constraint covering those columns:

-- First, add the unique constraint to the booking table
ALTER TABLE booking ADD UNIQUE KEY booking_composite_key (booking_no, bdate, src_stn, dest_stn, consignee, desc_goods, no_of_art, total);

-- Then re-create your dispatch table, ensuring all column types match exactly
CREATE TABLE dispatch(
    booking_no INT,
    bdate DATE,
    src_stn VARCHAR(20),
    dest_stn VARCHAR(20),
    consignee VARCHAR(30),
    desc_goods VARCHAR(40),
    no_of_art SMALLINT,
    total FLOAT,
    driver_name VARCHAR(30),
    lorry_no VARCHAR(10),
    status VARCHAR(5),
    PRIMARY KEY (booking_no),
    FOREIGN KEY (booking_no,bdate,src_stn,dest_stn,consignee,desc_goods,no_of_art,total) 
        REFERENCES booking(booking_no,bdate,src_stn,dest_stn,consignee,desc_goods,no_of_art,total)
) ENGINE=InnoDB;

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