如何避免子表UserType插入冲突时插入父表User记录？

你的核心需求是保证User和UserType的插入原子性——要么两个表都插入成功，要么都不插入，避免出现User表有记录但UserType插入失败的情况。下面给你两种实用的解决思路：

方法一：先检查再插入，提前拦截重复email #

在开启事务前，先查询UserType表中是否已存在目标email，如果存在直接返回错误，不执行后续插入操作：

async fn create_user(&self, user: &User) -> Result<(), CreateError> {
    let query_user = "insert into User (userid) values (?1)";
    let query_user_type = "insert into UserType (email, userId) values (?1, ?2)";
    
    let mut conn = self.pool.acquire().map_err("获取数据库连接失败").await?;

    // 先检查目标email是否已存在
    let email_exists = sqlx::query_scalar(
        "SELECT EXISTS(SELECT 1 FROM UserType WHERE email = ?1)"
    )
    .bind(user.email.as_str())
    .fetch_one(&mut conn)
    .map_err("检查email存在性失败").await?;

    if email_exists {
        return Err(CreateError::DuplicateEmail); // 自定义重复错误类型
    }

    // 检查通过，开启事务执行双插入
    let mut tx = conn.begin().map_err("开启事务失败").await?;

    sqlx::query(query_user)
        .bind(user.userid.as_str())
        .execute(&mut *tx)
        .map_err("插入User表失败").await?;

    sqlx::query(query_user_type)
        .bind(user.email.as_str())
        .bind(user.userid.as_str())
        .execute(&mut *tx)
        .map_err("插入UserType表失败").await?;

    tx.commit().map_err("提交事务失败").await?;
    Ok(())
}

方法二：调整插入顺序+利用事务回滚（更高效） #

把UserType的插入放在前面，并且去掉insert or ignore——让重复email直接触发数据库错误，此时事务会自动回滚，之前插入的User记录也会被撤销：

async fn create_user(&self, user: &User) -> Result<(), CreateError> {
    let query_user = "insert into User (userid) values (?1)";
    // 去掉or ignore，让唯一键冲突直接报错
    let query_user_type = "insert into UserType (email, userId) values (?1, ?2)";
    
    let mut conn = self.pool.acquire().map_err("获取数据库连接失败").await?;
    let mut tx = conn.begin().map_err("开启事务失败").await?;

    // 先插UserType，冲突则直接返回错误，事务自动回滚
    sqlx::query(query_user_type)
        .bind(user.email.as_str())
        .bind(user.userid.as_str())
        .execute(&mut *tx)
        .map_err(|e| {
            // 捕获SQLite唯一键冲突错误（错误码19）
            if let sqlx::Error::Database(db_err) = &e {
                if db_err.code() == Some("19") {
                    CreateError::DuplicateEmail
                } else {
                    CreateError::DbOperationFailed(e.into())
                }
            } else {
                CreateError::DbOperationFailed(e.into())
            }
        })
        .await?;

    // UserType插入成功后，再插User表
    sqlx::query(query_user)
        .bind(user.userid.as_str())
        .execute(&mut *tx)
        .map_err("插入User表失败").await?;

    tx.commit().map_err("提交事务失败").await?;
    Ok(())
}

关键注意点 #

1. 你原代码里的User插入SQL存在字段名错误：User表的主键是userid，但你写的是insert into User (id) values (?1)，必须改成(userid)，否则会触发字段不存在的错误。
2. 两种方法都依赖事务的原子性，确保任何一步失败都不会留下无效数据。方法二更高效，省去了一次额外查询。

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