How can you use Qt to create a SQL many to many relationship?

Jonathan

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 both elements of the primary key are 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). 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 have no problems modifying or deleting rows in the other tables.

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.

ie it's not possible to create a bridging table. Any ideas on how I can get around this?

wrosecrans

Could you create a bridging table with a separate id column that isn't referenced to another table which serves as the "membership reference" id? This also accounts for the case of an absent minded person who has joined a single club twice. I think the fact that “delete from Clubs_People where peopleId = 1 AND clubId = 4” is potentially ambiguous in such a case is part of the reason why Qt isn't designed to work that way. (Even if you and I think it isn't very sensible to join one club twice, having a unique id column for the table makes such ambiguity impossible.)

Jonathan

The purpose of having a compound primary key which is a composite of the two foreign keys is to prevent the same relationship being entered twice. If there is a separate primary key then someone can indeed join the same club multiple times, which is what I'm trying to prevent.

Why is "delete from Clubs_People where peopleId = 1 AND clubId = 4” potentially ambiguous?

Oh well, I shall see if it's possible to solve the problem by using constraints of some sort.

vcsala

I beleive what wrosecrans wrote (creating a separate id) makes sense as it solves the limitation. Uniqueness of the pairs can be ensure by using unique composite index on the peopleId and clubId fields together.

Jonathan

Thanks guys: always a good feeling to learn new stuff from experts!

I presume QSqlIndex is the class to use, however I can't find any sensible documentation. In particular
(i) how to enforce a UNIQUE constraint
(ii) how to find out what indexing is currently in place

I could do both using explicit SQL statementsw, but this seems to defeat the purpose of the high level SQL classes.

vcsala

What database engine do you use? If the tables are already created the easiest is to check it on the database level (eg. if you have mysql phpMyAdmin or similar tool can help you to check your indexes and create the new one). If you create everything from your app (eg. the CREATE TABLE command are executed from there) then you have to add a UNIQUE index (similar to the primary key contraint) to your CREATE TABLE command.

andre

Qt's SQL support is unfortunately limited. There simply is no support for manipulating data structures, only for manipulating data itself. As far as I know, i) is not possible with Qt at the moment, other than using QSqlQuery with hand-crafted SQL. That, unfortunately, is not portable across databases. For point ii), you are right that the documentation is unclear. I can not find a well described way to find other indices other than the primairy index per table. You would expect something like QSqlDatabase::indices or even a QSql::TableType value (since a QSqlIndex is a QSqlRecord subclass), but I can't find them. Also, even though QSqlIndex hints at manipulating indices, it does not tell you how to actually do that. I would have expected something like a sqlStatement() method for that. To me, it looks like it is a piece of Qt that was planned to be expanded further, but never was.

Jonathan

My application uses Qt to create, review, and edit a random SQLite database.

So it looks as though I have no choice but to use SQL's CREATE INDEX command to create the indexes, and then use the SQLite PRAGMAs INDEX INFO and INDEX LIST to get details of existing indexes ;o(

Thanks for all your help.

Jonathan

Finally developed the code and UI to manage and create arbitrary indexes.

Using an index on the foreign keys with a separate primary key does indeed do the job, though it still seems slightly unnatural.