Unable to insert a row to QSqlRelationalTable model

soroush

I'm trying to insert a record to QSqlRelationalTable. If setRelation is called before, foreign key constraint fails.

customers table:

@
+-------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(45) | YES | | NULL | |
| last | varchar(45) | YES | | NULL | |
| email | varchar(45) | YES | | NULL | |
| phone | varchar(45) | YES | | NULL | |
| fax | varchar(45) | YES | | NULL | |
| address | text | YES | | NULL | |
| customer_types_id | int(11) | NO | MUL | NULL | |
+-------------------+-------------+------+-----+---------+----------------+
@

customer_types table:

@
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(45) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
@

Code:

@model = new QSqlRelationalTableModel(this);
model->setTable("customers");
model->setRelation(7,QSqlRelation("customer_types", "id", "name"));
// ...
QSqlRecord record = model->record();
record.setValue("name",ui->lineEditName->text());
// Other fields
record.setValue(7,QVariant("1")); // '1' exists in customer_types
qDebug() << model->insertRecord(-1,record);
qDebug() << model->lastError().text();
@

Output:

@
false
"Cannot add or update a child row: a foreign key constraint fails
(doors.customers, CONSTRAINT fk_customers_customer_types FOREIGN KEY
(customer_types_id) REFERENCES customer_types (id)
ON DELETE NO ACTION ON UPDATE NO ACTION) QMYSQL3: Unable to execute statement"
@

francescmm

I think that you have a problem when creating:
@record.setValue(7,QVariant("1")); // '1' exists in customer_types@

Try creating the QVariant with a integer. Perhaps what happens is that the insertRecord() method set the value like you have created it. If you create a QVariant with a QString type, it tries to insert a QString. Try with this code:

@record.setValue(7,1);@

soroush

I tried '1', 'quint32(1)', 'QVariant(1)' and 'QVariant(quint32(1))'

All are same.

soroush

Passing record to qDebug I have:

@
QSqlRecord( 8 )
" 0:" QSqlField("id", int, length: 11, precision: 0, required: yes, generated: no, typeID: 3) "0"
" 1:" QSqlField("name", QString, length: 135, precision: 0, required: no, generated: yes, typeID: 253) ""
" 2:" QSqlField("last", QString, length: 135, precision: 0, required: no, generated: yes, typeID: 253) ""
" 3:" QSqlField("email", QString, length: 135, precision: 0, required: no, generated: yes, typeID: 253) ""
" 4:" QSqlField("phone", QString, length: 135, precision: 0, required: no, generated: yes, typeID: 253) ""
" 5:" QSqlField("fax", QString, length: 135, precision: 0, required: no, generated: yes, typeID: 253) ""
" 6:" QSqlField("address", QString, length: 196605, precision: 0, required: no, generated: yes, typeID: 252) ""
" 7:" QSqlField("customer_types_name_2", QString, length: 135, precision: 0, required: no, generated: yes, typeID: 253) ""
@

Last field is of type QString. I though that it's because of the foreign key relation defined over field. Also, filed name is changed. So I tried passing values instead of keys:

@
record.setValue(7,QVariant("Customer Type 1")); // (1,"Customer Type 1") exists
@

Still same...

soroush

So I solved the issue by a weird solution. Remove the foreign key column, re-add it:

@
QSqlRecord record = model->record();
record.remove(7);
record.insert(7,QSqlField("customer_types_id",QVariant::Int));
// fill data
record.setValue("name",ui->lineEditName->text());
// For foreign key:
record.setValue(7,1);
record.setGenerated("id",false);
model->select();
@