Details
-
Suggestion
-
Resolution: Out of scope
-
P3: Somewhat important
-
4.7.1
-
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:
- The table must have a primary key declared.
- 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)