Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. Odd behaviors when submitting rows with NOT NULL constraints
Forum Updated to NodeBB v4.3 + New Features

Odd behaviors when submitting rows with NOT NULL constraints

Scheduled Pinned Locked Moved Unsolved General and Desktop
5 Posts 3 Posters 177 Views 3 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • M Offline
    M Offline
    Mark81
    wrote on 10 Mar 2025, 17:43 last edited by Mark81 3 Oct 2025, 17:44
    #1

    I have the following SQlite3 table schema:

    CREATE TABLE bouquets         
    (
        variant TEXT NOT NULL,          
        name TEXT NOT NULL,          
        capacity INT NOT NULL,          
        PRIMARY KEY (name)
    );
    

    here a minimal reproducible example, cutting down the obvious:

    mainwindow.h

    private slots:
        void on_pushAdd_clicked();
    
    private:
        QSqlTableModel *model;
    

    mainwindow.cpp

    MainWindow::MainWindow(QWidget *parent) : QMainWindow(parent), ui(new Ui::MainWindow)
    {
        ui->setupUi(this);
    
        QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", DB_NAME);
        db.setDatabaseName(DB_FILENAME);
        db.open();
    
        model = new QSqlTableModel(this, db);
        model->setTable(TABLE_NAME);
        model->setEditStrategy(QSqlTableModel::OnRowChange);
        model->select();
    
        ui->tableView->setModel(model);
        ui->tableView->setItemDelegate(new QStyledItemDelegate(ui->tableView));
    
        connect(model, &QSqlTableModel::beforeInsert, this, [=](QSqlRecord &record)
        {
            switch (model->lastError().type())
            {
            case QSqlError::ConnectionError: qWarning() << "Insert connection error" << model->lastError().text(); break;
            case QSqlError::StatementError:  qWarning() << "Insert statement error" << model->lastError().text(); break;
            case QSqlError::TransactionError: qWarning() << "Insert transaction error" << model->lastError().text(); break;
            default: break;
            }
        });
        
        connect(model, &QSqlTableModel::beforeUpdate, this, [=](int row, QSqlRecord &record)
        {
            switch (model->lastError().type())
            {
            case QSqlError::ConnectionError: qWarning() << "Update connection error" << model->lastError().text(); break;
            case QSqlError::StatementError: qWarning() << "Update statement error" << model->lastError().text(); break;
            case QSqlError::TransactionError: qWarning() << "Update transaction error" << model->lastError().text(); break;
            default: break;
            }
       });
    }
    
    void MainWindow::on_btnAddRecord_clicked()
    {
        model->insertRow(model->rowCount());
        QModelIndex index = model->index(model->rowCount() - 1, 0);
        if (!index.isValid()) return;
        ui->tableView->setCurrentIndex(index);
        ui->tableView->edit(index);
    }
    

    It just populates a QTableView using a QSqlTableModel from a SQlite database.
    When the user presses the QPushButton a new record is appended to the table.

    Here the steps to reproduce the issue:

    1. click the button to add a new record
    2. fill only the first column ("variant"), press enter to close the editor and click on another row to submit
    3. as expected the row is not submitted and this error if fired:

    "Insert connection error NOT NULL constraint failed: bouquets.name Unable to fetch row"

    1. now go back to the row, fill the "name" column and press enter. The error is emitted for the third time (the second one was when you went back to the row, but this is ok). No matter what, you cannot submit this row even if you populate the "name" column!

    The expected behavior should be: once fulfilled the NOT NULL constraint on the "name" column it should scream about the "capacity" column instead.

    Now that we have the first two columns filled:

    1. enter something in the last column ("capacity") and press enter

    It now fires this other error:

    Insert connection error NOT NULL constraint failed: bouquets.capacity Unable to fetch row"

    It complains about a missing value (while it is there) and it throws an error about inserting the row to the database, but the row is actually stored into the db!

    Surely I'm missing something in the error handling. In my real application I connected these errors to a message box to inform the user and it's a pain, because they don't seem to reflect the actual situation.

    Can you please help me to understand how to handle this simple scenario?

    1 Reply Last reply
    0
    • S Offline
      S Offline
      SGaist
      Lifetime Qt Champion
      wrote on 10 Mar 2025, 19:02 last edited by
      #2

      Hi,

      Which version of Qt is it ?
      Can you please provide a complete minimal example that shows this ?

      Interested in AI ? www.idiap.ch
      Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

      M 1 Reply Last reply 10 Mar 2025, 19:06
      0
      • S SGaist
        10 Mar 2025, 19:02

        Hi,

        Which version of Qt is it ?
        Can you please provide a complete minimal example that shows this ?

        M Offline
        M Offline
        Mark81
        wrote on 10 Mar 2025, 19:06 last edited by Mark81 3 Oct 2025, 19:07
        #3

        @SGaist Qt 6.8.2
        The example above is complete.
        I just left out the #include directives and the usual definitions. Often I read in other threads you guys suggest to avoid the obvious code. Anyway, no problem, I post here all the stuff.

        mainwindow.h

        #ifndef MAINWINDOW_H
        #define MAINWINDOW_H
        
        #include <QMainWindow>
        #include <QSqlTableModel>
        #include <QSqlDatabase>
        #include <QSqlQuery>
        #include <QSqlError>
        
        QT_BEGIN_NAMESPACE
        namespace Ui {
        class MainWindow;
        }
        QT_END_NAMESPACE
        
        class MainWindow : public QMainWindow
        {
            Q_OBJECT
        
        public:
            MainWindow(QWidget *parent = nullptr);
            ~MainWindow();
        
        private slots:
            void on_pushAdd_clicked();
        
        private:
            Ui::MainWindow *ui;
            QSqlTableModel *model;
        };
        #endif // MAINWINDOW_H
        

        mainwindow.cpp

        #include "mainwindow.h"
        #include "ui_mainwindow.h"
        #include <QStyledItemDelegate>
        
        MainWindow::MainWindow(QWidget *parent) : QMainWindow(parent), ui(new Ui::MainWindow)
        {
            ui->setupUi(this);
        
            QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", DB_NAME);
            db.setDatabaseName(DB_FILE);
            db.open();
        
            model = new QSqlTableModel(this, db);
            model->setTable(TABLE_NAME);
            model->setEditStrategy(QSqlTableModel::OnRowChange);
            model->select();
        
            ui->tableView->setModel(model);
            ui->tableView->setItemDelegate(new QStyledItemDelegate(ui->tableView));
        
            connect(model, &QSqlTableModel::beforeInsert, this, [=](QSqlRecord &record)
            {
                switch (model->lastError().type())
                {
                case QSqlError::ConnectionError: qWarning() << "Insert connection error" << model->lastError().text(); break;
                case QSqlError::StatementError: qWarning() << "Insert statement error" << model->lastError().text(); break;
                case QSqlError::TransactionError: qWarning() << "Insert transaction error" << model->lastError().text(); break;
                default: break;
                }
            });
        }
        
        MainWindow::~MainWindow()
        {
            delete ui;
        }
        
        void MainWindow::on_pushAdd_clicked()
        {
            model->insertRow(model->rowCount());
            QModelIndex index = model->index(model->rowCount() - 1, 0);
            if (!index.isValid()) return;
            ui->tableView->setCurrentIndex(index);
            ui->tableView->edit(index);
        }
        

        mainwindow.ui

        <?xml version="1.0" encoding="UTF-8"?>
        <ui version="4.0">
         <class>MainWindow</class>
         <widget class="QMainWindow" name="MainWindow">
          <property name="geometry">
           <rect>
            <x>0</x>
            <y>0</y>
            <width>800</width>
            <height>600</height>
           </rect>
          </property>
          <property name="windowTitle">
           <string>MainWindow</string>
          </property>
          <widget class="QWidget" name="centralwidget">
           <widget class="QTableView" name="tableView">
            <property name="geometry">
             <rect>
              <x>40</x>
              <y>40</y>
              <width>321</width>
              <height>381</height>
             </rect>
            </property>
           </widget>
           <widget class="QPushButton" name="btnAdd">
            <property name="geometry">
             <rect>
              <x>400</x>
              <y>40</y>
              <width>88</width>
              <height>25</height>
             </rect>
            </property>
            <property name="text">
             <string>PushButton</string>
            </property>
           </widget>
          </widget>
         <resources/>
         <connections/>
        </ui>
        
        1 Reply Last reply
        0
        • Christian EhrlicherC Online
          Christian EhrlicherC Online
          Christian Ehrlicher
          Lifetime Qt Champion
          wrote on 10 Mar 2025, 19:09 last edited by
          #4

          Please minimze the code - no need for an ui (file) at all, no pushbutton. Simply all in main.cpp with a QSqlTableModel and a sqlite in-memory db should do the job.

          Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
          Visit the Qt Academy at https://academy.qt.io/catalog

          1 Reply Last reply
          0
          • M Offline
            M Offline
            Mark81
            wrote on 10 Mar 2025, 19:30 last edited by Mark81 3 Oct 2025, 19:31
            #5

            Well, I hope this time it is ok for you:

            #include <QApplication>
            #include <QSqlTableModel>
            #include <QSqlQuery>
            #include <QSqlDatabase>
            #include <QSqlError>
            #include <QMainWindow>
            #include <QTableView>
            #include <QStyledItemDelegate>
            
            int main(int argc, char *argv[])
            {
                QApplication a(argc, argv);
                QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
                db.open();
                QSqlQuery query(db);
                query.exec("CREATE TABLE bouquets (variant TEXT NOT NULL, name TEXT NOT NULL, capacity INT NOT NULL, PRIMARY KEY (name));");
                query.exec("INSERT INTO bouquets (variant, name, capacity) VALUES ('A', 'foo', 10);");
            
                QMainWindow window;
                QSqlTableModel *model;
                model = new QSqlTableModel(&window, db);
                model->setTable("bouquets");
                model->setEditStrategy(QSqlTableModel::OnRowChange);
                model->select();
            
                QTableView tableView;
                window.setCentralWidget(&tableView);
                tableView.setModel(model);
                tableView.setItemDelegate(new QStyledItemDelegate(&tableView));
            
                QObject::connect(model, &QSqlTableModel::beforeInsert, [=](QSqlRecord &record)
                {
                    switch (model->lastError().type())
                    {
                    case QSqlError::ConnectionError: qWarning() << "Insert connection error" << model->lastError().text(); break;
                    case QSqlError::StatementError: qWarning() << "Insert statement error" << model->lastError().text(); break;
                    case QSqlError::TransactionError: qWarning() << "Insert transaction error" << model->lastError().text(); break;
                    default: break;
                    }
                });
            
                model->insertRow(model->rowCount());
                QModelIndex index = model->index(model->rowCount() - 1, 0);
                tableView.setCurrentIndex(index);
                tableView.edit(index);
            
                window.show();
                return a.exec();
            }
            

            I write again the steps:

            1. fill only the "variant" column, press enter and change row
            2. check the (correct) error about the missing "bouquets.name" field is fired
            3. come back to the row, check again the same error is firing (just because we changed the row, ok)
            4. fill the "name" column and press enter

            The error is fired again! But we set the column to a value different than NULL. No error should be fired.
            Now if you still edit the same column and press again enter it will change (correctly) to the next missing column.

            1. fill the "capacity" column and press enter. The row is submitted but:

            a) it should not since we didn't change the row
            b) a wrong error ("NOT NULL constraint failed: bouquets.capacity Unable to fetch row") is fired: it makes no sense since the field was set and the row correctly submitted

            1 Reply Last reply
            0

            1/5

            10 Mar 2025, 17:43

            • Login

            • Login or register to search.
            1 out of 5
            • First post
              1/5
              Last post
            0
            • Categories
            • Recent
            • Tags
            • Popular
            • Users
            • Groups
            • Search
            • Get Qt Extensions
            • Unsolved