Uploaded image for project: 'Qt'
  1. Qt
  2. QTBUG-16179

Qt should allow SQL bridging tables for a many to many relationship

    XMLWordPrintable

Details

    • Suggestion
    • Resolution: Out of scope
    • P3: Somewhat important
    • Some future release
    • 4.7.1
    • SQL Support
    • None
    • Windows 7

    Description

      A common SQL paradigm is represented by People and Clubs, where a person can belong to many clubs, and clubs have many members. Other examples are singers and songs, or authors and publishers.

      The required database tables can be created using the following:
      CREATE TABLE People (id INTEGER, name TEXT NOT NULL, PRIMARY KEY (id))
      CREATE TABLE Clubs (id INTEGER, sport TEXT NOT NULL, PRIMARY KEY (id))
      CREATE TABLE Clubs_People (peopleId INT REFERENCES People, clubId INT REFERENCES Clubs, PRIMARY KEY (peopleId, clubId))"

      The last of these creates the "bridging" table with a compound primary key, where each element is a foreign key.

      I'm using QSqlRelationalTable, QSqlRelationalDelegate, QSqlRelation, and QTableView. In general this combination works well. I can create table views with foreign keys, and select which column from the foreign (parent) table to display in the original table.

      However, when I construct the above schema, I cannot use the QTableView to delete or modify rows in the bridging table (although I can add new rows). I have no problems modifying or deleting rows in the other tables.

      The following application output is generated when I attempt to delete a row in this bridging table:
      "QSqlQuery::value: not positioned on a valid record"

      I CAN delete rows from the bridging table if I use eg
      QSqlQueryModel *model = new QSqlQueryModel;
      model->setQuery(queryString);
      where queryString is something like "delete from Clubs_People where peopleId = 1 AND clubId = 4"

      The clue to the limitation is given in http://doc.qt.nokia.com/4.7/qsqlrelationaltablemodel.html#setData:

      1. The table must have a primary key declared.
      2. The table's primary key may not contain a relation to another table.

      *This limitation is surely unacceptable*

      (I also think the following points, 4th and 5th in the same list, are impossible to understand)

      Attachments

        No reviews matched the request. Check your Options in the drop-down menu of this sections header.

        Activity

          People

            Unassigned Unassigned
            jallin JAllin
            Votes:
            1 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Gerrit Reviews

                There are no open Gerrit changes