deepsidhu1313 · Oct 26, 2026 · Oct 30, 2026
diff --git a/src/sqlitedb.cpp b/src/sqlitedb.cpp
@@ -1461,6 +1461,15 @@ std::string DBBrowserDB::emptyInsertStmt(const std::string& schemaName, const sq
                     vals.push_back("NULL");
                 }
             }
+        } else if(pk.size() > 1 && !pk_value.isNull() && contains(pk, f.name())) {
+            // Composite primary key of a table without rowid: the given value goes into the last key column and all other key columns get a
+            // neutral value. Because the last column is set to a value which does not exist yet, the combination is always unique.
+            fields.push_back(f.name());
+
+            if(pk.back().name() == f.name())
+                vals.push_back(f.isText()? "'" + pk_value.toStdString() + "'" : pk_value.toStdString());
+            else
+                vals.push_back(f.isInteger()? "0" : "''");
         } else if(f.notnull() && f.defaultValue().length() == 0) {
             fields.push_back(f.name());
 
@@ -1522,10 +1531,18 @@ QString DBBrowserDB::addRecord(const sqlb::ObjectIdentifier& tablename)
         qWarning() << "addRecord: " << lastErrorMessage;
         return QString();
     } else {
-        if(table->withoutRowidTable())
-            return pk_value;
-        else
+        if(!table->withoutRowidTable())
             return QString::number(sqlite3_last_insert_rowid(_db));
+
+        const sqlb::StringVector rowid_columns = table->rowidColumns();
+        if(rowid_columns.size() == 1)
+            return pk_value;
+
+        // For multiple rowid columns the identifier of a row is the composed value built by sqlb_make_single_value (see getRow). Only the
+        // last key column is known to be unique, so we look the new row up by that one.
+        const auto last = sqlb::findField(table, rowid_columns.back());
+        const std::string where = sqlb::escapeIdentifier(rowid_columns.back()) + "=" + (last->isText()? "'" + pk_value.toStdString() + "'" : pk_value.toStdString());
+        return QString::fromUtf8(querySingleValueFromDb("SELECT sqlb_make_single_value(" + sqlb::joinStringVector(sqlb::escapeIdentifier(rowid_columns), ",") + ") FROM " + tablename.toString() + " WHERE " + where));
     }
 }
 

diff --git a/src/sqlitetablemodel.cpp b/src/sqlitetablemodel.cpp
@@ -750,8 +750,26 @@ QModelIndex SqliteTableModel::dittoRecord(int old_row)
     int new_row = rowCount() - 1;
 
     const auto pk = m_table_of_query->primaryKeyColumns();
+
+    // Columns which are part of a UNIQUE constraint can't hold the same value twice, so just like the primary key they are not copied
+    sqlb::StringVector unique_columns;
+    for(const auto& it : m_table_of_query->indexConstraints())
+    {
+        if(!it.second->isPrimaryKey())
+        {
+            for(const auto& c : it.first)
+                unique_columns.push_back(c.name());
+        }
+    }
+    for(const auto& f : m_table_of_query->fields)
+    {
+        if(f.unique())
+            unique_columns.push_back(f.name());
+    }
+
     for (size_t col = 0; col < m_table_of_query->fields.size(); ++col) {
-        if(pk.empty() || !contains(pk, m_table_of_query->fields.at(col).name())) {
+        const std::string& name = m_table_of_query->fields.at(col).name();
+        if((pk.empty() || !contains(pk, name)) && !contains(unique_columns, name)) {
             if (!firstEditedColumn)
                 firstEditedColumn = col + 1;
 

diff --git a/src/tests/CMakeLists.txt b/src/tests/CMakeLists.txt
@@ -123,3 +123,68 @@ set(TESTCACHE_HDR
 add_executable(test-cache ${TESTCACHE_HDR} ${TESTCACHE_SRC})
 target_link_libraries(test-cache ${QT_MAJOR}::Test)
 add_test(NAME test-cache COMMAND $<TARGET_FILE:test-cache>)
+
+# test duplicate record
+
+set(TESTDUPLICATERECORD_SRC
+    TestDuplicateRecord.cpp
+    ../sqlitedb.cpp
+    ../sqlitetablemodel.cpp
+    ../RowLoader.cpp
+    ../CipherDialog.cpp
+    ../CipherSettings.cpp
+    ../CondFormat.cpp
+    ../Data.cpp
+    ../Settings.cpp
+    ../sql/sqlitetypes.cpp
+    ../sql/Query.cpp
+    ../sql/ObjectIdentifier.cpp
+    ../sql/parser/ParserDriver.cpp
+    ../sql/parser/sqlite3_lexer.cpp
+    ../sql/parser/sqlite3_parser.cpp
+)
+
+set(TESTDUPLICATERECORD_HDR
+    TestDuplicateRecord.h
+    ../sqlitedb.h
+    ../sqlitetablemodel.h
+    ../RowLoader.h
+    ../RowCache.h
+    ../CipherDialog.h
+    ../CipherSettings.h
+    ../CondFormat.h
+    ../Data.h
+    ../Settings.h
+    ../sql/sqlitetypes.h
+    ../sql/Query.h
+    ../sql/ObjectIdentifier.h
+    ../sql/parser/ParserDriver.h
+    ../sql/parser/sqlite3_lexer.h
+    ../sql/parser/sqlite3_location.h
+    ../sql/parser/sqlite3_parser.hpp
+)
+
+add_executable(test-duplicate-record ${TESTDUPLICATERECORD_HDR} ${TESTDUPLICATERECORD_SRC} ../CipherDialog.ui)
+target_include_directories(test-duplicate-record SYSTEM PRIVATE ${CMAKE_SOURCE_DIR}/libs/json)
+if(QT_MAJOR STREQUAL "Qt6")
+    set_target_properties(test-duplicate-record PROPERTIES AUTOUIC_OPTIONS "--connections=string")
+endif()
+target_link_libraries(test-duplicate-record ${QT_MAJOR}::Test ${QT_MAJOR}::Widgets ${QT_MAJOR}::Concurrent ${LIBSQLITE_NAME} ${QT5_COMPAT})
+add_test(NAME test-duplicate-record COMMAND $<TARGET_FILE:test-duplicate-record>)
+if(WIN32)
+    set(_dr_extra_paths "")
+    foreach(_p IN LISTS CMAKE_PREFIX_PATH)
+        list(APPEND _dr_extra_paths "${_p}" "${_p}/bin")
+        # OpenSSL 3 Universal installer layout: bin/x86, bin/x64
+        if(CMAKE_SIZEOF_VOID_P EQUAL 8)
+            list(APPEND _dr_extra_paths "${_p}/bin/x64")
+        else()
+            list(APPEND _dr_extra_paths "${_p}/bin/x86")
+        endif()
+    endforeach()
+    # SQLite and SQLCipher are built as DLLs in the CMake prefix by the Windows CI jobs.
+    string(REPLACE ";" "\\;" TESTDUPLICATERECORD_PATH "${_dr_extra_paths};$ENV{PATH}")
+    set_tests_properties(test-duplicate-record PROPERTIES ENVIRONMENT "QT_QPA_PLATFORM=offscreen;PATH=${TESTDUPLICATERECORD_PATH}")
+else()
+    set_tests_properties(test-duplicate-record PROPERTIES ENVIRONMENT "QT_QPA_PLATFORM=offscreen")
+endif()
diff --git a/src/tests/TestDuplicateRecord.cpp b/src/tests/TestDuplicateRecord.cpp
@@ -0,0 +1,236 @@
+#include "TestDuplicateRecord.h"
+
+#include "../sqlitedb.h"
+#include "../sqlitetablemodel.h"
+#include "../sql/Query.h"
+
+#include <QApplication>
+#include <QSignalSpy>
+#include <QTemporaryDir>
+#include <QTimer>
+#include <QtTest/QtTest>
+
+QTEST_MAIN(TestDuplicateRecord)
+
+namespace
+{
+
+// All rows of the test table 't', each one rendered as a single string, ordered by the given expression
+QStringList tableRows(DBBrowserDB& db, const QString& columns, const QString& order)
+{
+    const std::string sql = "SELECT group_concat(r, char(10)) FROM (SELECT " + columns.toStdString() + " AS r FROM t ORDER BY " + order.toStdString() + ")";
+    const QString rows = QString::fromUtf8(db.querySingleValueFromDb(sql, false, DBBrowserDB::Wait));
+    return rows.isEmpty() ? QStringList() : rows.split('\n');
+}
+
+}
+
+void TestDuplicateRecord::duplicateKeepsExistingRecords_data()
+{
+    QTest::addColumn<QString>("create");
+    QTest::addColumn<QString>("seed");
+    QTest::addColumn<QString>("columns");
+    QTest::addColumn<QString>("order");
+    QTest::addColumn<int>("rowToDuplicate");
+    QTest::addColumn<int>("sortColumn");
+    QTest::addColumn<bool>("loadAll");
+
+    const QString columns = "quote(a)||','||quote(b)||','||quote(c)";
+
+    QTest::newRow("no primary key")
+        << "CREATE TABLE t(a INTEGER, b TEXT, c TEXT)"
+        << "INSERT INTO t VALUES(1,'one','x'),(2,'two','y'),(3,'three','z')"
+        << columns << "rowid" << 1 << -1 << true;
+    QTest::newRow("integer primary key")
+        << "CREATE TABLE t(a INTEGER PRIMARY KEY, b TEXT, c TEXT)"
+        << "INSERT INTO t VALUES(1,'one','x'),(2,'two','y'),(3,'three','z')"
+        << columns << "a" << 1 << -1 << true;
+    QTest::newRow("integer primary key autoincrement")
+        << "CREATE TABLE t(a INTEGER PRIMARY KEY AUTOINCREMENT, b TEXT, c TEXT)"
+        << "INSERT INTO t VALUES(1,'one','x'),(2,'two','y'),(3,'three','z')"
+        << columns << "a" << 1 << -1 << true;
+    QTest::newRow("integer primary key not null")
+        << "CREATE TABLE t(a INTEGER NOT NULL PRIMARY KEY, b TEXT, c TEXT)"
+        << "INSERT INTO t VALUES(1,'one','x'),(2,'two','y'),(3,'three','z')"
+        << columns << "a" << 1 << -1 << true;
+    QTest::newRow("text primary key")
+        << "CREATE TABLE t(a TEXT PRIMARY KEY, b TEXT, c TEXT)"
+        << "INSERT INTO t VALUES('1','one','x'),('2','two','y'),('3','three','z')"
+        << columns << "a" << 1 << -1 << true;
+    QTest::newRow("composite primary key")
+        << "CREATE TABLE t(a INTEGER, b INTEGER, c TEXT, PRIMARY KEY(a,b))"
+        << "INSERT INTO t VALUES(1,1,'x'),(1,2,'y'),(2,1,'z')"
+        << columns << "a,b" << 1 << -1 << true;
+    QTest::newRow("without rowid")
+        << "CREATE TABLE t(a INTEGER PRIMARY KEY, b TEXT, c TEXT) WITHOUT ROWID"
+        << "INSERT INTO t VALUES(1,'one','x'),(2,'two','y'),(3,'three','z')"
+        << columns << "a" << 1 << -1 << true;
+    QTest::newRow("without rowid composite primary key")
+        << "CREATE TABLE t(a INTEGER, b INTEGER, c TEXT, PRIMARY KEY(a,b)) WITHOUT ROWID"
+        << "INSERT INTO t VALUES(1,1,'x'),(1,2,'y'),(2,1,'z')"
+        << columns << "a,b" << 1 << -1 << true;
+    QTest::newRow("unique column")
+        << "CREATE TABLE t(a INTEGER PRIMARY KEY, b TEXT UNIQUE, c TEXT)"
+        << "INSERT INTO t VALUES(1,'one','x'),(2,'two','y'),(3,'three','z')"
+        << columns << "a" << 1 << -1 << true;
+    QTest::newRow("sorted descending")
+        << "CREATE TABLE t(a INTEGER PRIMARY KEY, b TEXT, c TEXT)"
+        << "INSERT INTO t VALUES(1,'one','x'),(2,'two','y'),(3,'three','z')"
+        << columns << "a" << 0 << 1 << true;
+    QTest::newRow("sorted descending text")
+        << "CREATE TABLE t(a INTEGER PRIMARY KEY, b TEXT, c TEXT)"
+        << "INSERT INTO t VALUES(1,'one','x'),(2,'two','y'),(3,'three','z')"
+        << columns << "a" << 0 << 2 << true;
+    QTest::newRow("large partially loaded")
+        << "CREATE TABLE t(a INTEGER PRIMARY KEY, b TEXT, c TEXT)"
+        << "WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<120000) INSERT INTO t SELECT n, 'v'||n, 'x' FROM s"
+        << columns << "a" << 5 << -1 << false;
+    QTest::newRow("large partially loaded no pk")
+        << "CREATE TABLE t(a INTEGER, b TEXT, c TEXT)"
+        << "WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<120000) INSERT INTO t SELECT n, 'v'||n, 'x' FROM s"
+        << columns << "rowid" << 5 << -1 << false;
+}
+
+void TestDuplicateRecord::duplicateKeepsExistingRecords()
+{
+    QFETCH(QString, create);
+    QFETCH(QString, seed);
+    QFETCH(QString, columns);
+    QFETCH(QString, order);
+    QFETCH(int, rowToDuplicate);
+    QFETCH(int, sortColumn);
+    QFETCH(bool, loadAll);
+
+    QTemporaryDir dir;
+    QVERIFY(dir.isValid());
+
+    DBBrowserDB db;
+    QVERIFY(db.create(dir.filePath("test.db")));
+    QVERIFY(db.executeSQL(create.toStdString()));
+    QVERIFY(db.executeSQL(seed.toStdString()));
+    db.updateSchema();
+
+    const QStringList before = tableRows(db, columns, order);
+
+    SqliteTableModel model(db, nullptr, QString(), true);
+    QSignalSpy rowCountSpy(&model, &SqliteTableModel::finishedRowCount);
+    model.setQuery(sqlb::Query(sqlb::ObjectIdentifier("main", "t")));
+    QVERIFY(rowCountSpy.wait(5000) || rowCountSpy.count() > 0);
+    QCOMPARE(model.rowCount(), before.size());
+    if(sortColumn >= 0)
+        model.sort(sortColumn, Qt::DescendingOrder);
+    if(loadAll)
+        QVERIFY(model.completeCache());
+    else
+        model.waitUntilIdle();
+
+    // Any message box popping up (e.g. for a failed update) would block the test, so close it and remember it
+    bool dialogShown = false;
+    QTimer closer;
+    connect(&closer, &QTimer::timeout, this, [&]() {
+        if(QWidget* w = QApplication::activeModalWidget())
+        {
+            dialogShown = true;
+            w->close();
+        }
+    });
+    closer.start(50);
+
+    model.dittoRecord(rowToDuplicate);
+    closer.stop();
+
+    // The original records must all still be there, unmodified, and the table must have received one more record.
+    const QStringList after = tableRows(db, columns, order);
+    QVERIFY2(!dialogShown, "an error dialog was shown while duplicating");
+    QCOMPARE(after.size(), before.size() + 1);
+    const QSet<QString> afterSet(after.begin(), after.end());
+    for(const QString& row : before)
+        QVERIFY2(afterSet.contains(row), qPrintable("record was lost or overwritten: " + row));
+}
+
+void TestDuplicateRecord::duplicateTwiceWithoutRowidCompositeKey()
+{
+    QTemporaryDir dir;
+    QVERIFY(dir.isValid());
+
+    DBBrowserDB db;
+    QVERIFY(db.create(dir.filePath("test.db")));
+    QVERIFY(db.executeSQL("CREATE TABLE t(a INTEGER, b INTEGER, c TEXT, PRIMARY KEY(a,b)) WITHOUT ROWID"));
+    QVERIFY(db.executeSQL("INSERT INTO t VALUES(1,1,'x'),(1,2,'y'),(2,1,'z')"));
+    db.updateSchema();
+
+    SqliteTableModel model(db, nullptr, QString(), true);
+    QSignalSpy rowCountSpy(&model, &SqliteTableModel::finishedRowCount);
+    model.setQuery(sqlb::Query(sqlb::ObjectIdentifier("main", "t")));
+    QVERIFY(rowCountSpy.wait(5000) || rowCountSpy.count() > 0);
+    QVERIFY(model.completeCache());
+
+    // Any message box popping up would block the test, so close it and remember it
+    bool dialogShown = false;
+    QTimer closer;
+    connect(&closer, &QTimer::timeout, this, [&]() {
+        if(QWidget* w = QApplication::activeModalWidget())
+        {
+            dialogShown = true;
+            w->close();
+        }
+    });
+    closer.start(50);
+
+    // The identifier of the new row must match the inserted row, otherwise the second duplicate runs into the first one.
+    // Row 1 has the key (1,2)
+    QVERIFY(model.dittoRecord(1).isValid());
+    QVERIFY(model.dittoRecord(1).isValid());
+    closer.stop();
+    QVERIFY2(!dialogShown, "an error dialog was shown while duplicating");
+
+    QCOMPARE(tableRows(db, "quote(a)||','||quote(b)||','||quote(c)", "a,b"),
+             QStringList() << "0,3,'y'" << "0,4,'y'" << "1,1,'x'" << "1,2,'y'" << "2,1,'z'");
+
+    // The rows in the model have to be the ones in the database
+    QCOMPARE(model.rowCount(), 5);
+    QCOMPARE(model.data(model.index(3, 1), Qt::EditRole).toString(), QString("0"));
+    QCOMPARE(model.data(model.index(3, 2), Qt::EditRole).toString(), QString("3"));
+    QCOMPARE(model.data(model.index(3, 3), Qt::EditRole).toString(), QString("y"));
+    QCOMPARE(model.data(model.index(4, 2), Qt::EditRole).toString(), QString("4"));
+    QCOMPARE(model.data(model.index(4, 3), Qt::EditRole).toString(), QString("y"));
+}
+
+void TestDuplicateRecord::duplicateSkipsUniqueColumns()
+{
+    QTemporaryDir dir;
+    QVERIFY(dir.isValid());
+
+    DBBrowserDB db;
+    QVERIFY(db.create(dir.filePath("test.db")));
+    QVERIFY(db.executeSQL("CREATE TABLE t(a INTEGER PRIMARY KEY, b TEXT UNIQUE, c TEXT, d TEXT, e TEXT, UNIQUE(c,d))"));
+    QVERIFY(db.executeSQL("INSERT INTO t VALUES(1,'one','x','p','q'),(2,'two','y','r','s')"));
+    db.updateSchema();
+
+    SqliteTableModel model(db, nullptr, QString(), true);
+    QSignalSpy rowCountSpy(&model, &SqliteTableModel::finishedRowCount);
+    model.setQuery(sqlb::Query(sqlb::ObjectIdentifier("main", "t")));
+    QVERIFY(rowCountSpy.wait(5000) || rowCountSpy.count() > 0);
+    QVERIFY(model.completeCache());
+
+    bool dialogShown = false;
+    QTimer closer;
+    connect(&closer, &QTimer::timeout, this, [&]() {
+        if(QWidget* w = QApplication::activeModalWidget())
+        {
+            dialogShown = true;
+            w->close();
+        }
+    });
+    closer.start(50);
+
+    // Columns covered by a UNIQUE constraint (b and c,d) are not copied, the others are
+    const QModelIndex edit = model.dittoRecord(0);
+    closer.stop();
+    QVERIFY2(!dialogShown, "an error dialog was shown while duplicating");
+    QVERIFY(edit.isValid());
+    QCOMPARE(edit.row(), 2);
+
+    QCOMPARE(tableRows(db, "quote(a)||','||quote(b)||','||quote(c)||','||quote(d)||','||quote(e)", "a"),
+             QStringList() << "1,'one','x','p','q'" << "2,'two','y','r','s'" << "3,NULL,NULL,NULL,'q'");
+}
diff --git a/src/tests/TestDuplicateRecord.h b/src/tests/TestDuplicateRecord.h
@@ -0,0 +1,17 @@
+#ifndef TESTDUPLICATERECORD_H
+#define TESTDUPLICATERECORD_H
+
+#include <QObject>
+
+class TestDuplicateRecord : public QObject
+{
+    Q_OBJECT
+
+private slots:
+    void duplicateKeepsExistingRecords_data();
+    void duplicateKeepsExistingRecords();
+    void duplicateTwiceWithoutRowidCompositeKey();
+    void duplicateSkipsUniqueColumns();
+};
+
+#endif // TESTDUPLICATERECORD_H