解决SQLite主键约束异常及数据库ID必要性的疑问

问题描述 #

使用sqflite和provider开发购物车功能，拥有室内植物、室外植物、花瓶三类商品列表。将第一个列表的首个商品加入购物车后，无法添加另外两个列表的首个商品，触发SQLite主键约束异常：DatabaseException(UNIQUE constraint failed: cart.id (code 1555 SQLITE_CONSTRAINT_PRIMARYKEY))。

尝试设置INTEGER PRIMARY KEY AUTOINCREMENT无效，调整变量也未解决。移除主键自增仅保留id INTEGER时，触发productId的UNIQUE约束报错；移除UNIQUE后可添加商品，但删除时会批量移除同名称商品且价格不更新。最终移除id和productId字段，改用商品名进行删除和更新操作解决问题，但不清楚教程中id和productId的作用，以及数据库表是否必须设置id字段。

核心原因 #

1. 主键冲突：从报错参数可见，插入的商品id值均为0，而id是表的主键（唯一约束），第二次插入id=0时直接触发冲突。即使设置了AUTOINCREMENT，因为手动传入了id值，SQLite不会自动生成主键，导致自增失效。
2. productId重复：三类商品列表的首个商品productId都为0，而表中productId设置了UNIQUE约束，同样会触发冲突。

正确解决方案 #

1. 修正Cart模型 #

将id改为可选参数，默认值设为null，插入时不传入id，让SQLite自动生成主键：

class Cart {
  final int? id;
  final String? productId;
  final String? productName;
  final int? initialPrice;
  final int? productPrice;
  final int? quantity;
  final String? productDesc;
  final String image;

  Cart({
    this.id,
    required this.productId,
    required this.productName,
    required this.initialPrice,
    required this.productPrice,
    required this.quantity,
    required this.productDesc,
    required this.image,
  });

  Cart.fromMap(Map<dynamic, dynamic> res)
      : id = res['id'],
        productId = res['productId'],
        productName = res['productName'],
        initialPrice = res['initialPrice'],
        productPrice = res['productPrice'],
        quantity = res['quantity'],
        productDesc = res['productDesc'],
        image = res['image'];

  Map<String, Object?> toMap() {
    return {
      'id': id,
      'productId': productId,
      'productName': productName,
      'initialPrice': initialPrice,
      'productPrice': productPrice,
      'quantity': quantity,
      'productDesc': productDesc,
      'image': image,
    };
  }
}

2. 修正DBHelper的表结构和插入逻辑 #

• 表结构中id保留INTEGER PRIMARY KEY即可，SQLite会自动为其生成自增的唯一值（无需AUTOINCREMENT，该关键字仅用于特殊场景）。
• 新增insertOrUpdate方法，实现"存在则更新数量，不存在则插入"的逻辑：

class DBHelper {
  static Database? _db;
  Future<Database?> get db async {
    if (_db != null) {
      return _db!;
    }
    _db = await initDatabase();
    return _db;
  }

  initDatabase() async {
    io.Directory documentDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentDirectory.path, 'cart.db');
    var db = await openDatabase(path, version: 1, onCreate: _onCreate);
    return db;
  }

  _onCreate(Database db, int version) async {
    await db.execute(
        'CREATE TABLE cart(id INTEGER PRIMARY KEY,productId VARCHAR UNIQUE,productName TEXT,initialPrice INTEGER,productPrice INTEGER,quantity INTEGER,productDesc TEXT,image TEXT)');
  }

  Future<Cart> insertOrUpdate(Cart cart) async {
    var dbClient = await db;
    // 查询是否存在该productId的记录
    var existing = await dbClient!.query(
      'cart',
      where: 'productId = ?',
      whereArgs: [cart.productId],
    );

    if (existing.isNotEmpty) {
      // 存在则更新数量和总价
      int currentQuantity = existing.first['quantity'] as int;
      int newQuantity = currentQuantity + cart.quantity!;
      int newPrice = cart.productPrice! * newQuantity;
      await dbClient.update(
        'cart',
        {
          'quantity': newQuantity,
          'productPrice': newPrice,
        },
        where: 'productId = ?',
        whereArgs: [cart.productId],
      );
      return Cart(
        id: existing.first['id'] as int,
        productId: cart.productId,
        productName: cart.productName,
        initialPrice: cart.initialPrice,
        productPrice: newPrice,
        quantity: newQuantity,
        productDesc: cart.productDesc,
        image: cart.image,
      );
    } else {
      // 不存在则插入，id自动生成
      await dbClient.insert(
        'cart',
        cart.toMap(),
        conflictAlgorithm: ConflictAlgorithm.abort,
      );
      return cart;
    }
  }

  // 其他原有方法（getCartList、delete、updateQuantity）保持不变
}

3. 更新CartProvider中的添加逻辑 #

调用insertOrUpdate方法替代原有的insert，确保重复添加商品时更新数量而非触发冲突。

关于id和productId的作用 #

1. id（数据库主键）：

   • 不是所有表都必须设置，但属于通用最佳实践。它是数据库层面唯一标识每条记录的字段，用于精准执行更新、删除操作，避免使用商品名等可能重复的字段导致误操作（比如多个商品同名的场景）。
   • SQLite中INTEGER PRIMARY KEY会自动自增，无需额外设置AUTOINCREMENT。

2. productId（业务唯一标识）：

   • 代表商品在业务系统中的唯一ID，用于区分不同商品。设置UNIQUE约束后，可确保同一个商品在购物车中只有一条记录，重复添加时只需更新数量，符合购物车的常规逻辑。

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