MySQL在外键插入后添加主键

CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

INSERT INTO customers (name) VALUES ('Alice');
INSERT INTO orders (customer_id, order_date) VALUES (LAST_INSERT_ID(), '2023-10-01');

DELIMITER //

CREATE TRIGGER insert_customer_before_order
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    IF NEW.customer_id NOT IN (SELECT id FROM customers) THEN
        INSERT INTO customers (name) VALUES ('New Customer');
        SET NEW.customer_id = LAST_INSERT_ID();
    END IF;
END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE insert_order_with_customer(IN customer_name VARCHAR(255), IN order_date DATE)
BEGIN
    DECLARE customer_id INT;
    INSERT INTO customers (name) VALUES (customer_name);
    SET customer_id = LAST_INSERT_ID();
    INSERT INTO orders (customer_id, order_date) VALUES (customer_id, order_date);
END //

DELIMITER ;