mysql外键失效

-- 错误示例
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id VARCHAR(50), -- 数据类型不匹配
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE customers (
    customer_id INT PRIMARY KEY
);

-- 正确示例
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT, -- 数据类型匹配
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    INDEX idx_customer_id (customer_id)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

SET FOREIGN_KEY_CHECKS = 1; -- 启用外键约束

-- 检查外键列中的数据是否在参照表中存在
SELECT * FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM customers);

-- 修正数据不一致的问题
DELETE FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM customers);

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    ENGINE=InnoDB
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    ENGINE=InnoDB
);