如何解决DatabaseException – SQLITE主键约束?

huangapple go评论66阅读模式
英文:

How to solve DatabaseException - SQLITE constraint primary key?

问题

I followed a tutorial for creating a shopping cart using sqflite and provider: https://www.youtube.com/watch?v=_JTbQmGocqw&ab_channel=TheTechBrothers

我按照教程创建了一个使用sqflite和provider的购物车:https://www.youtube.com/watch?v=_JTbQmGocqw&ab_channel=TheTechBrothers

I have 3 types of plants with each their own list, the issue is when I add the first item from the first list to cart, I cant add the first item from the second list and third list, I get a SQLITE constraint primary key error.

我有3种不同的植物,每种都有自己的列表,问题是当我将第一个列表中的第一项添加到购物车时,我无法添加第二个列表和第三个列表中的第一项,我会收到一个SQLITE约束主键错误。

I tried adding "INTEGER PRIMARY KEY AUTOINCREMENT" but its not working, I also tried creating a string variable called id with "INTEGER PRIMARY KEY AUTOINCREMENT" but it did not work, my question is, how do I solve this ? from the questions that have been asked here most of them been solved with auto increment but it did not work for me, or did I not do it correctly ?

我尝试添加了"INTEGER PRIMARY KEY AUTOINCREMENT"但它不起作用,我还尝试创建了一个名为id的字符串变量并添加了"INTEGER PRIMARY KEY AUTOINCREMENT"但它不起作用,我的问题是,我该如何解决这个问题?大部分问题都是通过自动增量解决的,但对我来说并没有起作用,或者我没有正确操作吗?

here is my dbhelper class:

这是我的dbhelper类:

import 'package:path_provider/path_provider.dart';
import 'package:sqflite/sqflite.dart';
import 'dart:io' as io;
import 'cart_model.dart';
import 'package:path/path.dart';

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

  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> insert(Cart cart) async {
    print(cart.toMap());
    var dbClient = await db;
    await dbClient!.insert('cart', cart.toMap());
    return cart;
  }

  Future<List<Cart>> getCartList() async {
    var dbClient = await db;
    final List<Map<String, Object?>> queryResult =
        await dbClient!.query('cart');
    return queryResult.map((e) => Cart.fromMap(e)).toList();
  }

  Future<int> delete(int id) async {
    var dbClient = await db;
    return await dbClient!.delete('cart', where: 'id = ?', whereArgs: [id]);
  }

  Future<int> updateQuantity(Cart cart) async {
    var dbClient = await db;
    return await dbClient!
        .update('cart', cart.toMap(), where: 'id = ?', whereArgs: [cart.id]);
  }
}

这是我的dbhelper类:

here is the cart provider class:

这是购物车提供程序类:

import 'package:flutter/material.dart';
import 'package:plantel/screens/widgets/db_helper.dart';
import 'package:shared_preferences/shared_preferences.dart';

import '../screens/widgets/cart_model.dart';

class CartProvider with ChangeNotifier {
  DBHelper db = DBHelper();
  int _counter = 0;
  int get counter => _counter;
  double _totalPrice = 0.0;
  double get totalPrice => _totalPrice;
  late Future<List<Cart>> _cart;
  Future<List<Cart>> get cart => _cart;
  Future<List<Cart>> getData() async {
    _cart = db.getCartList();
    return _cart;
  }

  void _setPrefItems() async {
    SharedPreferences prefs = await SharedPreferences.getInstance();
    prefs.setInt('cart_items', _counter);
    prefs.setDouble('total_price', _totalPrice);
    notifyListeners();
  }

  void _getPrefItems() async {
    SharedPreferences prefs = await SharedPreferences.getInstance();
    _counter = prefs.getInt('cart_item') ?? 0;
    _totalPrice = prefs.getDouble('total_price') ?? 0.0;

    notifyListeners();
  }

  void addTotalPrice(double productPrice) {
    _totalPrice = _totalPrice + productPrice;
    _setPrefItems();
    notifyListeners();
  }

  void removeTotalPrice(double productPrice) {
    _totalPrice = _totalPrice - productPrice;
    _setPrefItems();
    notifyListeners();
  }

  double getTotalPrice() {
    _getPrefItems();
    return _totalPrice;
  }

  void addCounter() {
    _counter++;
    _setPrefItems();
    notifyListeners();
  }

  void removeCounter() {
    _counter--;
    _setPrefItems();
    notifyListeners();
  }

  int getCounter() {
    _getPrefItems();
    return _counter;
  }
}

这是购物车提供程序类:

here is the cart model:

这是购物车模型:

class Cart {
  late 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(
      {required 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,
    };
  }
}

这是购物车模型:

and here are the lists. Each of the lists are in different classes.

这些是列表。每个列表位于

英文:

I followed a tutorial for creating a shopping cart using sqflite and provider : https://www.youtube.com/watch?v=_JTbQmGocqw&amp;ab_channel=TheTechBrothers

I have 3 types of plants with each their own list, the issue is when I add the first item from the first list to cart, I cant add the first item from the second list and third list, I get a SQLITE constraint primary key error.

I tried adding "INTEGER PRIMARY KEY AUTOINCREMENT" but its not working, I also tried creating a string variable called id with "INTEGER PRIMARY KEY AUTOINCREMENT" but it did not work, my question is, how do I solve this ? from the questions that have been asked here most of them been solved with auto increment but it did not work for me, or did I not do it correctly ?

here is my dbhelper class:

import &#39;package:path_provider/path_provider.dart&#39;;
import &#39;package:sqflite/sqflite.dart&#39;;
import &#39;dart:io&#39; as io;
import &#39;cart_model.dart&#39;;
import &#39;package:path/path.dart&#39;;

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

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

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

  Future&lt;Cart&gt; insert(Cart cart) async {
    print(cart.toMap());
    var dbClient = await db;
    await dbClient!.insert(&#39;cart&#39;, cart.toMap());
    return cart;
  }

  Future&lt;List&lt;Cart&gt;&gt; getCartList() async {
    var dbClient = await db;
    final List&lt;Map&lt;String, Object?&gt;&gt; queryResult =
        await dbClient!.query(&#39;cart&#39;);
    return queryResult.map((e) =&gt; Cart.fromMap(e)).toList();
  }

  Future&lt;int&gt; delete(int id) async {
    var dbClient = await db;
    return await dbClient!.delete(&#39;cart&#39;, where: &#39;id = ?&#39;, whereArgs: [id]);
  }

  Future&lt;int&gt; updateQuantity(Cart cart) async {
    var dbClient = await db;
    return await dbClient!
        .update(&#39;cart&#39;, cart.toMap(), where: &#39;id = ?&#39;, whereArgs: [cart.id]);
  }
}

here is the cart provider class:

import &#39;package:flutter/material.dart&#39;;
import &#39;package:plantel/screens/widgets/db_helper.dart&#39;;
import &#39;package:shared_preferences/shared_preferences.dart&#39;;

import &#39;../screens/widgets/cart_model.dart&#39;;

class CartProvider with ChangeNotifier {
  DBHelper db = DBHelper();
  int _counter = 0;
  int get counter =&gt; _counter;
  double _totalPrice = 0.0;
  double get totalPrice =&gt; _totalPrice;
  late Future&lt;List&lt;Cart&gt;&gt; _cart;
  Future&lt;List&lt;Cart&gt;&gt; get cart =&gt; _cart;
  Future&lt;List&lt;Cart&gt;&gt; getData() async {
    _cart = db.getCartList();
    return _cart;
  }

  void _setPrefItems() async {
    SharedPreferences prefs = await SharedPreferences.getInstance();
    prefs.setInt(&#39;cart_items&#39;, _counter);
    prefs.setDouble(&#39;total_price&#39;, _totalPrice);
    notifyListeners();
  }

  void _getPrefItems() async {
    SharedPreferences prefs = await SharedPreferences.getInstance();
    _counter = prefs.getInt(&#39;cart_item&#39;) ?? 0;
    _totalPrice = prefs.getDouble(&#39;total_price&#39;) ?? 0.0;

    notifyListeners();
  }

  void addTotalPrice(double productPrice) {
    _totalPrice = _totalPrice + productPrice;
    _setPrefItems();
    notifyListeners();
  }

  void removeTotalPrice(double productPrice) {
    _totalPrice = _totalPrice - productPrice;
    _setPrefItems();
    notifyListeners();
  }

  double getTotalPrice() {
    _getPrefItems();
    return _totalPrice;
  }

  void addCounter() {
    _counter++;
    _setPrefItems();
    notifyListeners();
  }

  void removeCounter() {
    _counter--;
    _setPrefItems();
    notifyListeners();
  }

  int getCounter() {
    _getPrefItems();
    return _counter;
  }
}

here is the cart model:

class Cart {
  late 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(
      {required 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&lt;dynamic, dynamic&gt; res)
      : id = res[&#39;id&#39;],
        productId = res[&#39;productId&#39;],
        productName = res[&#39;productName&#39;],
        initialPrice = res[&#39;initialPrice&#39;],
        productPrice = res[&#39;productPrice&#39;],
        quantity = res[&#39;quantity&#39;],
        productDesc = res[&#39;productDesc&#39;],
        image = res[&#39;image&#39;];
  Map&lt;String, Object?&gt; toMap() {
    return {
      &#39;id&#39;: id,
      &#39;productId&#39;: productId,
      &#39;productName&#39;: productName,
      &#39;initialPrice&#39;: initialPrice,
      &#39;productPrice&#39;: productPrice,
      &#39;quantity&#39;: quantity,
      &#39;productDesc&#39;: productDesc,
      &#39;image&#39;: image,
    };
  }
}

and here are the lists. Each of the lists are in different classes.

Indoor plants list:

 List&lt;String&gt; productName = [
    &#39;Anthurium&#39;,
    &#39;Clivia Miniata&#39;,
    &#39;Aloe Vera&#39;,
    &#39;Nertera Granadensis&#39;,
    &#39;Oxalis Trinagularis&#39;,
    &#39;Opuntia Microdasys&#39;,
    &#39;Kalanchoe Tomentosa&#39;
  ];
  List&lt;String&gt; Productdesc = [
    &#39;Beautiful appearance, difficult to care, and poisonous&#39;,
    &#39;beautiful appearance, trumpet-shaped, easy to maintain&#39;,
    &#39;Very popular, easy to maintain, have health benefits&#39;,
    &#39;Visually unique, picky and light demands, difficult to maintain&#39;,
    &#39;Triangular leaves, photonastic response, toxic for pets&#39;,
    &#39;Desert type,  bunny ears shaped, requires small amount of water&#39;,
    &#39;Easy to maintain, have furry leaves, have thick leaves&#39;
  ];
  List&lt;int&gt; productPrice = [25, 32, 74, 31, 33, 29, 28];
  List&lt;String&gt; productImage = [
    &#39;assets/images/Anthurium.png&#39;,
    &#39;assets/images/clivia-miniata.png&#39;,
    &#39;assets/images/aloe-vera.png&#39;,
    &#39;assets/images/nertera-granadensis.png&#39;,
    &#39;assets/images/oxalis-triangularis.png&#39;,
    &#39;assets/images/opuntia-microdasys.png&#39;,
    &#39;assets/images/kalanchoe-tomentosa.png&#39;
  ];

outdoor plants list:

 List&lt;String&gt; productName = [
    &#39;Lavender&#39;,
    &#39;Jasmine&#39;,
    &#39;Maple Tree&#39;,
    &#39;Cherry Blossom Tree&#39;,
    &#39;Rose&#39;,
    &#39;Morning Glory&#39;,
    &#39;Hibiscus&#39;
  ];
  List&lt;String&gt; Productdesc = [
    &#39;Very popular, fragrant smell, and a beautiful appearance&#39;,
    &#39;Beatiful at night, fragrant smell, and very popular&#39;,
    &#39;Deciduous trees often grown for the shade they produce&#39;,
    &#39;A symbolic flower of the spring&#39;,
    &#39;Very beautiful,fragrant smell, and very sharp thorns&#39;,
    &#39;Fast growing, trumpet-shaped, and mild fragrance&#39;,
    &#39;Colorful, fragrant,very beautiful&#39;
  ];
  List&lt;int&gt; productPrice = [95, 83, 107, 114, 92, 86, 73];
  List&lt;String&gt; productImage = [
    &#39;assets/images/lavender.png&#39;,
    &#39;assets/images/jasmine.png&#39;,
    &#39;assets/images/mapletree.png&#39;,
    &#39;assets/images/cherry-blossom-tree.png&#39;,
    &#39;assets/images/rose.png&#39;,
    &#39;assets/images/morning-glory-flower.png&#39;,
    &#39;assets/images/Hibiscus.png&#39;
  ];

vases list:

 List&lt;String&gt; productName = [
    &#39;Clay&#39;,
    &#39;Plastic&#39;,
    &#39;Glazed Ceramic&#39;,
    &#39;Fiberglass&#39;,
    &#39;Concrete&#39;,
    &#39;Whiskey Barrel&#39;,
    &#39;Metal&#39;
  ];
  List&lt;String&gt; Productdesc = [
    &#39;Good for growing plants, attractive, expensive and heavy in large sizes&#39;,
    &#39;Lightweight, durable, and affordable&#39;,
    &#39;Colorful, varied in style, good for plant growth&#39;,
    &#39;Classically styled, durable, lightweight&#39;,
    &#39;Attractive, durable, and affordable&#39;,
    &#39;Informal in style, verry affordable, becomes more fragile in time&#39;,
    &#39;Very ornamental, lasts very long, very thick&#39;
  ];
  List&lt;int&gt; productPrice = [65, 20, 35, 45, 23, 9, 42];
  List&lt;String&gt; productImage = [
    &#39;assets/images/clay-vase.png&#39;,
    &#39;assets/images/plastic-vase.png&#39;,
    &#39;assets/images/glazed-ceramic.png&#39;,
    &#39;assets/images/fiberglass-vase.png&#39;,
    &#39;assets/images/concrete-vase.png&#39;,
    &#39;assets/images/whiskey-barrel.png&#39;,
    &#39;assets/images/metal-pot.png&#39;
  ];

here is the debug console:
DatabaseException(UNIQUE constraint failed: cart.id (code 1555 SQLITE_CONSTRAINT_PRIMARYKEY)) sql 'INSERT INTO cart (id, productId, productName, initialPrice, productPrice, quantity, productDesc, image) VALUES (?, ?, ?, ?, ?, ?, ?, ?)' args [0, 0, Lavender, 95, 95, 1, Very popular, fragrant smell, and a beautiful appe..., assets/images/lavender.png]

for more understanding of the lists here is a picture of the home page

homepage

EDIT :

I tried removing "PRIMARY KEY AUTOINCREMENT" and only typing "id INTEGER" but I get an error constraint called "SQLITE constraint UNIQUE" because of the unique key word in product id VARCHAR, I tried removing also and leaving create table like this:

await db.execute(
'CREATE TABLE cart(id INTEGER,productId VARCHAR ,productName TEXT,initialPrice INTEGER,productPrice INTEGER,quantity INTEGER,productDesc TEXT,image TEXT)');

after clearing the app data I tired again with the code above, it partially solved it, now I can add items that have the same row, but another problem occurred, now when I remove items from the cart, items that have the same row are removed at once and the price is not updated.

EDIT 2: I SOLVED IT, I can't believe the solution was so simple, after a couple of hours of searching I just randomly wanted to remove both the id and productId just to see what will happen and it worked!!

I removed "id" and "productId" and changed the delete and update function to delete and update an item by name. Although this code was copied from a video I don't completely understand it, can someone please explain why the video creator added id and productId, and is it necessary to add id to a database table even though it worked just now ?

答案1

得分: 2

这个问题出现在两个或更多的数据具有相同的ID(主键)时,因此主键约束失败。

我认为在你的情况下,自动递增ID不起作用,因为你将购物车数据模型传递给了插入函数,而它本身已经有了ID。如果你想尝试,这是创建具有自动递增主键的表的查询:

CREATE TABLE cart
(
    id INTEGER PRIMARY KEY,
    productId VARCHAR UNIQUE,
    productName TEXT,
    initialPrice INTEGER,
    productPrice INTEGER,
    quantity INTEGER,
    productDesc TEXT,
    image TEXT
)

请将此查询替换在onCreate函数中。

你需要清除应用程序数据并重新运行它以生成具有新架构的表格。

如果这不起作用,这是另一种解决方案:

你可以使用数据库插入的upsert功能,从数据库的角度来看,这意味着如果数据库具有具有给定ID的数据,则数据将被更新,否则将被插入。

Sqflite中没有内置的此功能,以下是相同功能的函数:

Future<Cart> upsert(Cart cart) async {
    Database? dbClient = await db;
    String tableName = 'cart';
    bool isExists = await _checkId(tableName, cart.id ?? 0);
    print(cart.toMap());
    if (isExists) {
        // 数据库表中具有给定ID的数据
        // 因此将更新详细信息
        dbClient?.update(tableName, cart.toMap());
    } else {
        // 数据库表中没有具有给定ID的数据
        // 将插入数据库表中
        dbClient?.insert(tableName, cart.toMap());
    }
    return cart;
}

/// [_checkId] 检查给定的ID是否
/// 存在于数据库中。
Future<bool> _checkId(String tableName, int id) async {
    var dbClient = await db;
    dynamic isExsits = await dbClient
        ?.rawQuery('SELECT id FROM $tableName WHERE id==$id;');
    
    return isExsits.isNotEmpty ? true : false;
}

checkId函数检查购物车的给定ID是否存在于数据库表中。如果存在,upsert函数将更新给定的购物车数据,否则将插入数据库表中。

你可以在插入函数的位置使用它。

英文:

This issue came when the two or more data have same id(primary key) so the primary key constraints fails,

I do think in the your case auto incrementing the id doesn't work cause you are giving the cart datamodel to the insert and it have the id itself, if you want to try, here is the query for the create table with auto-increment primary key,

CREATE TABLE cart
(
    id INTEGER PRIMARY KEY,
    productId VARCHAR UNIQUE,
    productName TEXT,
    initialPrice INTEGER,
    productPrice INTEGER,
    quantity INTEGER,
    productDesc TEXT,
    image TEXT
)

Replace the query in the onCreate function.

You need to clear the app data and re-run it to generate the table with the new schema.

And if it doesn't work here is the other solution:

You can use upsert functionality for database insert, in term of database it means that if the database has the data with the given id then the data will be updated otherwise it will be inserted.

There is no built in function for it in Sqflite, here is the function for the same:

Future&lt;Cart&gt; upsert(Cart cart) async {
Database? dbClient = await db;
String tableName = &#39;cart&#39;;
bool isExists = await _checkId(tableName, cart.id ?? 0);
print(cart.toMap());
if (isExists) {
  // database table has the data with given id 
  // so the details will be. updated
  dbClient?.update(tableName, cart.toMap());
} else {
  // database table don&#39;t have the data with given id,
  // it will be inserted in the database table.
  dbClient?.insert(tableName, cart.toMap());
}
return cart;
}

/// [_chcekId] checks that the given id is 
/// present in the database or not.
Future&lt;bool&gt; _checkId(String tableName, int id) async {
var dbClient = await db;
dynamic isExsits = await dbClient
    ?.rawQuery(&#39;&#39;&#39;SELECT id FROM $tableName WHERE id==$id;&#39;&#39;&#39;);

return isExsits.isNotEmpty ? true : false;
}

The checkId function checks that the given id of cart is present in the database table or not. if present upsert function will update the given cart data, otherwise it will be inserted into the database table.

You can use it in place of the insert function.

huangapple
  • 本文由 发表于 2023年6月15日 03:58:09
  • 转载请务必保留本文链接:https://go.coder-hub.com/76477140.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定