UNIQUE constraint failed: products.id (code 1555 SQLITE_CONSTRAINT_PRIMARYKEY)) sql 'INSERT INTO products (id, name, price, quantity) VALUES (?, ?, ?,

Question

I get eroor

SqfliteDatabaseException (DatabaseException(UNIQUE constraint failed: products.id (code 1555 SQLITE_CONSTRAINT_PRIMARYKEY)) sql 'INSERT INTO products (id, name, price, quantity) VALUES (?, ?, ?, ?)' args [0, shoes, 150000.0, 1])

can you help me

my dtabasehelper

import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
import 'package:sqflite_crud/models/product.dart';

class DatabaseHelper {
  DatabaseHelper._privateConstructor();
  static final DatabaseHelper instance = DatabaseHelper._privateConstructor();

  static Database? _database;

  Future<Database> get database async {
    _database ??= await _initDatabase();
    return _database!;
  }

  Future<Database> _initDatabase() async {
    String path = join(await getDatabasesPath(), 'kasir.db');
    return await openDatabase(
      path,
      version: 1,
      onCreate: _onCreate,
    );
  }

  Future<void> _onCreate(Database db, int version) async {
    await db.execute('''
      CREATE TABLE products (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT,
        price REAL,
        quantity INTEGER
      )
    ''');
  }

  Future<int> insertProduct(Product product) async {
    Database db = await instance.database;
    return await db.insert('products', product.toMap());
  }

  Future<List<Product>> getAllProducts() async {
    Database db = await instance.database;
    List<Map<String, dynamic>> maps = await db.query('products');
    return List.generate(maps.length, (index) {
      return Product.fromMap(maps[index]);
    });
  }

  Future<int> updateProduct(Product product) async {
    Database db = await instance.database;
    return await db.update(
      'products',
      product.toMap(),
      where: 'id = ?',
      whereArgs: [product.id],
    );
  }

  Future<int> deleteProduct(int id) async {
    Database db = await instance.database;
    return await db.delete(
      'products',
      where: 'id = ?',
      whereArgs: [id],
    );
  }
}

My class

class Product {
  int id;
  String name;
  double price;
  int quantity;

  Product({
    required this.id,
    required this.name,
    required this.price,
    required this.quantity,
  });

  Map<String, dynamic> toMap() {
    return {
      'id': id,
      'name': name,
      'price': price,
      'quantity': quantity,
    };
  }

  static Product fromMap(Map<String, dynamic> map) {
    return Product(
      id: map['id'],
      name: map['name'],
      price: map['price'],
      quantity: map['quantity'],
    );
  }
}

my screen dart

import 'package:flutter/material.dart';

import 'package:sqflite_crud/helpers/databasehelper.dart';
import 'package:sqflite_crud/models/product.dart';
import 'package:sqflite_crud/screens/user.dart';
import 'package:google_fonts/google_fonts.dart';

class CashierPage extends StatefulWidget {
  @override
  _CashierPageState createState() => _CashierPageState();
}

class _CashierPageState extends State<CashierPage> {
  final _nameController = TextEditingController();
  final _priceController = TextEditingController();
  final _quantityController = TextEditingController();
  final _formKey = GlobalKey<FormState>();

  Future<List<Product>> _loadProducts() async {
    return await DatabaseHelper.instance.getAllProducts();
  }

  void _logout() {
    Navigator.pushAndRemoveUntil(
      context,
      MaterialPageRoute(builder: (context) => LoginPage()),
      (Route<dynamic> route) => false,
    );
  }

  void _addProduct() async {
    if (_formKey.currentState!.validate()) {
      Product product = Product(
        id: 0, // ID 0 untuk penambahan baru
        name: _nameController.text,
        price: double.parse(_priceController.text),
        quantity: int.parse(_quantityController.text),
      );
      await DatabaseHelper.instance.insertProduct(product);
      _clearFields();
      setState(() {});
    }
  }

  void _deleteProduct(int id) async {
    await DatabaseHelper.instance.deleteProduct(id);
    setState(() {});
  }

  void _clearFields() {
    _nameController.clear();
    _priceController.clear();
    _quantityController.clear();
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: Text('Cashier App'),
        actions: [
          ElevatedButton(
            onPressed: () {
              _logout();
            },
            child: const Text(
              'Logout',
              style: TextStyle(color: Colors.white),
            ),
            style: ElevatedButton.styleFrom(
              primary: Colors.grey,
              minimumSize: const Size(10, 40),
              shape: const RoundedRectangleBorder(
                  borderRadius: BorderRadius.all(
                Radius.circular(50),
              )),
              textStyle: GoogleFonts.poppins(fontSize: 14),
            ),
          ),
        ],
      ),
      body: FutureBuilder<List<Product>>(
        future: _loadProducts(),
        builder: (context, snapshot) {
          if (snapshot.hasData) {
            List<Product> products = snapshot.data!;
            return ListView.builder(
              itemCount: products.length,
              itemBuilder: (context, index) {
                Product product = products[index];
                return ListTile(
                  title: Text(product.name),
                  subtitle: Text(
                      'Price: ${product.price}\nQuantity: ${product.quantity}'),
                  trailing: IconButton(
                    icon: Icon(Icons.delete),
                    onPressed: () => _deleteProduct(product.id),
                  ),
                );
              },
            );
          } else if (snapshot.hasError) {
            return Text('Error: ${snapshot.error}');
          } else {
            return Center(child: CircularProgressIndicator());
          }
        },
      ),
      floatingActionButton: FloatingActionButton(
        onPressed: () {
          showDialog(
            context: context,
            builder: (context) {
              return AlertDialog(
                title: Text('Add Product'),
                content: Form(
                  key: _formKey,
                  child: Column(
                    mainAxisSize: MainAxisSize.min,
                    children: [
                      TextFormField(
                        controller: _nameController,
                        decoration: InputDecoration(labelText: 'Name'),
                        validator: (value) {
                          if (value == null || value.isEmpty) {
                            return 'Please enter a name';
                          }
                          return null;
                        },
                      ),
                      TextFormField(
                        controller: _priceController,
                        decoration: InputDecoration(labelText: 'Price'),
                        validator: (value) {
                          if (value == null || value.isEmpty) {
                            return 'Please enter a price';
                          }
                          return null;
                        },
                      ),
                      TextFormField(
                        controller: _quantityController,
                        decoration: InputDecoration(labelText: 'Quantity'),
                        validator: (value) {
                          if (value == null || value.isEmpty) {
                            return 'Please enter a quantity';
                          }
                          return null;
                        },
                      ),
                    ],
                  ),
                ),
                actions: [
                  TextButton(
                    onPressed: () {
                      if (_formKey.currentState!.validate()) {
                        _addProduct();
                        Navigator.of(context).pop();
                      }
                    },
                    child: Text('Add'),
                  ),
                  TextButton(
                    onPressed: () {
                      _clearFields();
                      Navigator.of(context).pop();
                    },
                    child: Text('Cancel'),
                  ),
                ],
              );
            },
          );
        },
        child: Icon(Icons.add),
      ),
    );
  }
}

..........................................................................

---

Answer 1

The issue

The id column, due to it being the PRIMARY KEY, is implicitly UNIQUE. You are attempting to insert a row with an id value that already exists (with a value of 0 in the given instance).

Possible Fixes

You could do one of the following (probably the bolded option):-

• INSERT using a unique value (this would require ascertaining the values of the id's that exist).
• INSERT where the value for id is not used along the lines of INSERT INTO products (name,price,quantity) VALUES (?,?,?);, so passing just the name, price and quantity.
• INSERT using null instead of the id, noting that an int cannot be null. However int? is nullable. As such a column defined as INTEGER PRIMARY KEY (with or without AUTOINCREMENT) which is null will generate a value for the column.
  • note that you very likely do not need to use AUTOINCREMENT which is inefficient (see https://www.sqlite.org/autoinc.html)
  • e.g. with id as int? then you could have (I think) id: null, // ID 0 untuk penambahan baru
• INSERT using an expression that will convert the 0 to a unique value, such as INSERT INTO products (id,name,price,quantity) VALUES((?=-98989898)+(SELECT max(id) + 1 FROM products),?, ?,?); - -98989898 is just one of many values that could be used to obtained false which equates to 0.
• Use an INSERT OR IGNORE .... in which case the UNQIUE conflict will be ignored and the row not inserted (see https://pub.dev/documentation/sqflite_common/latest/sql/ConflictAlgorithm.html).
  • any of the previous could use OR IGNORE
• Use an INSERT OR REPLACE where the row with the id be replaced (see https://pub.dev/documentation/sqflite_common/latest/sql/ConflictAlgorithm.html)