Unable to insert a row to QSqlRelationalTable model



  • 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"
    @



  • 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);@



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

    All are same.



  • 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...



  • 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();
    @


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.