How to solve DatabaseException - SQLITE constraint primary key?

Question

I followed a tutorial for creating a shopping cart using sqflite and 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.

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 '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]);
  }
}

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.

Indoor plants list:

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

outdoor plants list:

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

vases list:

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

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 ?

Answer 1

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<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) {
  // database table has the data with given id 
  // so the details will be. updated
  dbClient?.update(tableName, cart.toMap());
} else {
  // database table don'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<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;
}

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.