Can't edit QTableView with column that is primary and foreign key at the same time

[Auryn]

I have one QTableView for edit one QSqlRelationalTableModel.
The model takes its data from one table in PostgreSQL.
The table (myTable) has one foreign key to another table (myOtherTable).

The problem is that I can't edit fields in the table view when the foreign key is a primary key at the same time. I get this error when I submit the changes manually:

QSqlQuery::value: not positioned on a valid record

Here is the main file:

#include <QApplication>
#include "myClass.hpp"
 
int main(int argc, char *argv[]) {
	QApplication aplication(argc, argv);
 
	// Open conection to database
	QSqlDatabase bd(QSqlDatabase::addDatabase("QPSQL"));
	bd.setHostName("localhost");
	bd.setDatabaseName("myDB");
	bd.open("postgres", "postgres");
 
	// Load data in the table
	QTableView myTV;
	QSqlRelationalTableModel myModel(&myTV, bd);
	myModel.setEditStrategy(QSqlTableModel::OnManualSubmit);
	myModel.setTable("myTable");
 
	// Activate one foreign key
	QSqlRelation myRel("myOtherTable", "ID_other", "name_other");
	myModel.setRelation(1, myRel);
	myTV.setItemDelegateForColumn(1, new QSqlRelationalDelegate);
 
	// Show table with the data from database
	myModel.select();
	myTV.setModel(&myModel);
	myTV.show();
 
	// Create the object with the slot to save changes to database
	TmyClass myObject(0, &myModel);
 
	return aplication.exec();
}

#include <QApplication>
#include "myClass.hpp"

int main(int argc, char *argv[]) {
	QApplication aplication(argc, argv);

	// Open conection to database
	QSqlDatabase bd(QSqlDatabase::addDatabase("QPSQL"));
	bd.setHostName("localhost");
	bd.setDatabaseName("myDB");
	bd.open("postgres", "postgres");

	// Load data in the table
	QTableView myTV;
	QSqlRelationalTableModel myModel(&myTV, bd);
	myModel.setEditStrategy(QSqlTableModel::OnManualSubmit);
	myModel.setTable("myTable");

	// Activate one foreign key
	QSqlRelation myRel("myOtherTable", "ID_other", "name_other");
	myModel.setRelation(1, myRel);
	myTV.setItemDelegateForColumn(1, new QSqlRelationalDelegate);

	// Show table with the data from database
	myModel.select();
	myTV.setModel(&myModel);
	myTV.show();

	// Create the object with the slot to save changes to database
	TmyClass myObject(0, &myModel);

	return aplication.exec();
}

To copy to clipboard, switch view to plain text mode 

This is the header file of myClass:

#ifndef TMYCLASS
#define TMYCLASS
 
#include <QtCore/QDebug>
#include <QtGui/QTableView>
#include <QtSql/QSqlError>
#include <QtSql/QSqlRelationalDelegate>
 
class TmyClass : public QObject
{
Q_OBJECT
 
public:
	TmyClass(QObject *parent, QSqlRelationalTableModel *oneModel);
 
public slots:
	void mySave(const QModelIndex &a, const QModelIndex &b);
 
private:
	QSqlRelationalTableModel *m_model;
};
 
#endif

#ifndef TMYCLASS
#define TMYCLASS

#include <QtCore/QDebug>
#include <QtGui/QTableView>
#include <QtSql/QSqlError>
#include <QtSql/QSqlRelationalDelegate>

class TmyClass : public QObject
{
Q_OBJECT

public:
	TmyClass(QObject *parent, QSqlRelationalTableModel *oneModel);

public slots:
	void mySave(const QModelIndex &a, const QModelIndex &b);

private:
	QSqlRelationalTableModel *m_model;
};

#endif

To copy to clipboard, switch view to plain text mode 

And this is the code to save manually in myClass:

#include "myClass.hpp"
 
 
TmyClass::TmyClass(QObject *parent, QSqlRelationalTableModel *oneModel): QObject(parent), m_model(oneModel)
{
	// Cuando algo cambie, llamar al slot mySave
	connect(m_model, SIGNAL(dataChanged(const QModelIndex&, const QModelIndex&)), this, SLOT(mySave(const QModelIndex&, const QModelIndex&)));
}
 
 
void TmyClass::mySave(const QModelIndex &a, const QModelIndex &b)
{
	if(!m_model->submitAll())
	{
		qDebug()<<"Error when submitting:"<<m_model->lastError().text();
	}
}

#include "myClass.hpp"


TmyClass::TmyClass(QObject *parent, QSqlRelationalTableModel *oneModel): QObject(parent), m_model(oneModel)
{
	// Cuando algo cambie, llamar al slot mySave
	connect(m_model, SIGNAL(dataChanged(const QModelIndex&, const QModelIndex&)), this, SLOT(mySave(const QModelIndex&, const QModelIndex&)));
}


void TmyClass::mySave(const QModelIndex &a, const QModelIndex &b)
{
	if(!m_model->submitAll())
	{
		qDebug()<<"Error when submitting:"<<m_model->lastError().text();
	}
}

To copy to clipboard, switch view to plain text mode 

Finally, this is the myDB database dump:

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
 
CREATE TABLE "myOtherTable" (
    "ID_other" integer NOT NULL,
    name_other text
);
ALTER TABLE public."myOtherTable" OWNER TO postgres;
 
CREATE TABLE "myTable" (
    "ID" integer NOT NULL,
    "FK" integer NOT NULL,
    name text
);
 
ALTER TABLE public."myTable" OWNER TO postgres;
 
CREATE SEQUENCE "myOtherTable_ID_other_seq"
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;
 
ALTER TABLE public."myOtherTable_ID_other_seq" OWNER TO postgres;
 
ALTER SEQUENCE "myOtherTable_ID_other_seq" OWNED BY "myOtherTable"."ID_other";
 
SELECT pg_catalog.setval('"myOtherTable_ID_other_seq"', 2, true);
 
CREATE SEQUENCE "myTable_ID_seq"
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;
 
ALTER TABLE public."myTable_ID_seq" OWNER TO postgres;
 
ALTER SEQUENCE "myTable_ID_seq" OWNED BY "myTable"."ID";
 
SELECT pg_catalog.setval('"myTable_ID_seq"', 3, true);
 
ALTER TABLE "myOtherTable" ALTER COLUMN "ID_other" SET DEFAULT nextval('"myOtherTable_ID_other_seq"'::regclass);
 
ALTER TABLE "myTable" ALTER COLUMN "ID" SET DEFAULT nextval('"myTable_ID_seq"'::regclass);
 
COPY "myOtherTable" ("ID_other", name_other) FROM stdin;
1	One other
2	Two other
\.
 
COPY "myTable" ("ID", "FK", name) FROM stdin;
1	1	One
2	2	Two
3	2	ttt
\.
 
ALTER TABLE ONLY "myTable"
    ADD CONSTRAINT pk PRIMARY KEY ("ID", "FK");
 
ALTER TABLE ONLY "myOtherTable"
    ADD CONSTRAINT pk_other PRIMARY KEY ("ID_other");
 
ALTER TABLE ONLY "myTable"
    ADD CONSTRAINT fk FOREIGN KEY ("FK") REFERENCES "myOtherTable"("ID_other");

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;

CREATE TABLE "myOtherTable" (
    "ID_other" integer NOT NULL,
    name_other text
);
ALTER TABLE public."myOtherTable" OWNER TO postgres;

CREATE TABLE "myTable" (
    "ID" integer NOT NULL,
    "FK" integer NOT NULL,
    name text
);

ALTER TABLE public."myTable" OWNER TO postgres;

CREATE SEQUENCE "myOtherTable_ID_other_seq"
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

ALTER TABLE public."myOtherTable_ID_other_seq" OWNER TO postgres;

ALTER SEQUENCE "myOtherTable_ID_other_seq" OWNED BY "myOtherTable"."ID_other";

SELECT pg_catalog.setval('"myOtherTable_ID_other_seq"', 2, true);

CREATE SEQUENCE "myTable_ID_seq"
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

ALTER TABLE public."myTable_ID_seq" OWNER TO postgres;

ALTER SEQUENCE "myTable_ID_seq" OWNED BY "myTable"."ID";

SELECT pg_catalog.setval('"myTable_ID_seq"', 3, true);

ALTER TABLE "myOtherTable" ALTER COLUMN "ID_other" SET DEFAULT nextval('"myOtherTable_ID_other_seq"'::regclass);

ALTER TABLE "myTable" ALTER COLUMN "ID" SET DEFAULT nextval('"myTable_ID_seq"'::regclass);

COPY "myOtherTable" ("ID_other", name_other) FROM stdin;
1	One other
2	Two other
\.

COPY "myTable" ("ID", "FK", name) FROM stdin;
1	1	One
2	2	Two
3	2	ttt
\.

ALTER TABLE ONLY "myTable"
    ADD CONSTRAINT pk PRIMARY KEY ("ID", "FK");

ALTER TABLE ONLY "myOtherTable"
    ADD CONSTRAINT pk_other PRIMARY KEY ("ID_other");

ALTER TABLE ONLY "myTable"
    ADD CONSTRAINT fk FOREIGN KEY ("FK") REFERENCES "myOtherTable"("ID_other");

To copy to clipboard, switch view to plain text mode 

Perhaps this bug has some relation with my problem:
http://trolltech.com/developer/task-...ntry&id=176248

Thank you very much.

[Auryn]

Here is the complete example. It works with Qt 4.3 and PostgreSQL 8.3.

[zeb]

It is an old thread that I am bumping because nobody answered, and I have just had this issue. Indeed, you cannot use a column which is a PK or part of a PK as a FK relation. This is not caught with an exception, unfortunately.
This also means that you cannot display relations with an identifying relationship, but only with a non-identifying relationship (column is a non-null FK, but not a FK and PK at the same time).

See the Qt documentation: http://doc.trolltech.com/latest/qsql...ablemodel.html
The Notes section lists these restrictions:

• The table must have a primary key declared.
• The table's primary key may not contain a relation to another table.
• If a relational table contains keys that refer to non-existent rows in the referenced table, the rows containing the invalid keys will not be exposed through the model. The user or the database is responsible for keeping referential integrity.