QSqlRelationalTableModel issue updating a row

I'm using QSqlRelationalTableModel with QTableView to display and edit an SQLite table. Inserting new rows works fine, and the comboboxes from QSqlRelationalDelegate display correctly. But I can't edit existing rows. I suspect it's related to column aliasing that happens when setRelation() is used and display column names collide. How do I fix this?

This is a common stumbling block when working with QSqlRelationalTableModel. The model automatically aliases column names when it detects naming conflicts between your main table and your related tables. This aliasing can cause updates to silently fail because the generated SQL no longer matches the actual column names in your database.

Let's walk through why this happens and how to solve it.

Understanding the aliasing problem

When you call setRelation() on a column, QSqlRelationalTableModel joins your main table with the related table. If the display column in the related table has the same name as a column in your main table (or as a display column in another relation), Qt aliases it automatically. The alias follows the pattern tablename_columnname_id.

For example, imagine you have a books table with a column called name, and you set up a relation to an authors table that also has a column called name. Qt will alias one of these to something like authors_name_1. When the model then tries to write back an edit, it can generate SQL that references the aliased name instead of the real column name — and the update fails.

The simplest fix: use unique column names

The most reliable way to avoid this problem is to make sure your display column names in related tables don't collide with column names in your main table or with each other.

For instance, instead of having name in both your main table and your related table, rename the related table's column to something distinct like author_name. This prevents Qt from generating aliases in the first place.

If you control the database schema, this is the cleanest solution.

A practical example

Let's build a complete working example that demonstrates the problem and the fix. We'll create a small database with a books table that has foreign key relationships to authors and genres tables.

First, here's a version with conflicting column names — the kind that causes trouble:

# Schema that causes aliasing issues
# books: id, title, name, author_id, genre_id
# authors: id, name        <-- "name" conflicts with books.name
# genres: id, name         <-- "name" also conflicts

Now here's the fixed schema, where each table uses a unique column name:

# Schema with unique display column names
# books: id, title, author_id, genre_id
# authors: id, author_name
# genres: id, genre_name

Here's a complete working example using PySide6:

import sys

from PySide6.QtSql import (
    QSqlDatabase,
    QSqlQuery,
    QSqlRelation,
    QSqlRelationalDelegate,
    QSqlRelationalTableModel,
)
from PySide6.QtWidgets import QApplication, QMainWindow, QTableView


def create_database():
    """Create an in-memory SQLite database with sample data."""
    db = QSqlDatabase.addDatabase("QSQLITE")
    db.setDatabaseName(":memory:")

    if not db.open():
        print("Could not open database")
        sys.exit(1)

    query = QSqlQuery()

    # Create the authors table with a uniquely named column.
    query.exec(
        """
        CREATE TABLE authors (
            id INTEGER PRIMARY KEY,
            author_name TEXT NOT NULL
        )
    """
    )
    query.exec("INSERT INTO authors (author_name) VALUES ('Jane Austen')")
    query.exec("INSERT INTO authors (author_name) VALUES ('George Orwell')")
    query.exec("INSERT INTO authors (author_name) VALUES ('Toni Morrison')")

    # Create the genres table with a uniquely named column.
    query.exec(
        """
        CREATE TABLE genres (
            id INTEGER PRIMARY KEY,
            genre_name TEXT NOT NULL
        )
    """
    )
    query.exec("INSERT INTO genres (genre_name) VALUES ('Fiction')")
    query.exec("INSERT INTO genres (genre_name) VALUES ('Dystopian')")
    query.exec("INSERT INTO genres (genre_name) VALUES ('Romance')")

    # Create the main books table with foreign keys.
    query.exec(
        """
        CREATE TABLE books (
            id INTEGER PRIMARY KEY,
            title TEXT NOT NULL,
            author_id INTEGER NOT NULL,
            genre_id INTEGER NOT NULL,
            FOREIGN KEY (author_id) REFERENCES authors(id),
            FOREIGN KEY (genre_id) REFERENCES genres(id)
        )
    """
    )
    query.exec("INSERT INTO books (title, author_id, genre_id) VALUES ('Pride and Prejudice', 1, 3)")
    query.exec("INSERT INTO books (title, author_id, genre_id) VALUES ('1984', 2, 2)")
    query.exec("INSERT INTO books (title, author_id, genre_id) VALUES ('Beloved', 3, 1)")

    return db


class MainWindow(QMainWindow):
    def __init__(self):
        super().__init__()
        self.setWindowTitle("QSqlRelationalTableModel Example")
        self.resize(600, 300)

        # Set up the model.
        self.model = QSqlRelationalTableModel()
        self.model.setTable("books")

        # Set up relations. The display columns ("author_name", "genre_name")
        # are unique, so no aliasing will occur.
        self.model.setRelation(
            2, QSqlRelation("authors", "id", "author_name")
        )
        self.model.setRelation(
            3, QSqlRelation("genres", "id", "genre_name")
        )

        # Use OnFieldChange so edits are submitted immediately.
        self.model.setEditStrategy(
            QSqlRelationalTableModel.EditStrategy.OnFieldChange
        )

        self.model.select()

        # Set up the view.
        self.table_view = QTableView()
        self.table_view.setModel(self.model)

        # The relational delegate provides comboboxes for related columns.
        self.table_view.setItemDelegate(
            QSqlRelationalDelegate(self.table_view)
        )

        self.setCentralWidget(self.table_view)


app = QApplication(sys.argv)
create_database()
window = MainWindow()
window.show()
sys.exit(app.exec())

Run this and try editing any cell — including the combobox columns for author and genre. Changes should save correctly.

Choosing an edit strategy

In the example above, we used OnFieldChange as the edit strategy. This means every individual edit is submitted to the database immediately. This is helpful for debugging because you'll see errors right away.

You have three options for edit strategy:

If you're using OnManualSubmit, make sure to check the return value of submitAll() and call model.lastError().text() if it returns False. This will show you the SQL error, which often reveals the aliasing problem directly:

if not self.model.submitAll():
    print("Submit failed:", self.model.lastError().text())

What if you can't change the schema?

If you're working with an existing database and can't rename columns, you have a couple of options.

Use a proxy or manual model

Instead of relying on QSqlRelationalTableModel, you can use a plain QSqlTableModel (or even QSqlQueryModel) and handle the foreign key lookups yourself. You'd populate comboboxes manually using a custom delegate. This gives you full control over the SQL that gets generated. For more on working with table models and views, see the PySide6 QTableView with numpy and pandas tutorial, which covers model/view fundamentals in depth.

Summary

The core issue with QSqlRelationalTableModel failing to update rows comes down to column name conflicts between your main table and your related tables. When Qt detects duplicate column names, it aliases them — and those aliases can break the UPDATE SQL.

The most straightforward fix is to give your display columns unique names across all related tables. If that's not possible, consider handling the relations manually with a custom delegate or by subclassing the model. To learn more about the model/view architecture that underpins these table models, see the PySide6 ModelView Architecture tutorial. You may also find it helpful to review how sorting works in QTableView and how to edit data in a PySide6 table view.