mysql父子关系表

-- 确保插入的外键值在父表中存在
INSERT INTO child_table (parent_id, column2) VALUES (1, 'value');

-- 设置外键约束时使用ON DELETE CASCADE
ALTER TABLE child_table
ADD CONSTRAINT fk_child_parent
FOREIGN KEY (parent_id) REFERENCES parent_table(id)
ON DELETE CASCADE;

-- 使用索引优化查询
CREATE INDEX idx_parent_id ON child_table(parent_id);

-- 创建父表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100)
);

-- 创建子表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    amount DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 插入数据
INSERT INTO users (name) VALUES ('Alice');
INSERT INTO orders (user_id, amount) VALUES (1, 100.00);

-- 查询数据
SELECT * FROM users JOIN orders ON users.id = orders.user_id;