Posting data to multiple tables with joins is not working

REST API
1

I am trying to post a new record, along with a new child record neither have a primary since this is generated by my database, and is explained on this page that it is not necessary: https://blog.dreamfactory.com/sql-and-rest-working-with-related-data. I have a user table which has a 1 to 1 mapping to a customer table (by a user id in the customer table).

I am trying to POST the following data to the user table:

{“resource”: [{“email”:“b@b.com”, “customer_by_UserId”: [{“StripeData”: “test”}]}]}

but I am being given the error:

{
“error”: {
“context”: null,
“message”: “The user id can not be empty.”,
“code”: 400
}
}

I’ve been fighting with this for hours now… What am I doing wrong? I can’t possibly know the user id beforehand, but even if I try and put a user id into the customer relation, I still get the same error.

2.PNG1082×394 16.8 KB
3.PNG585×570 25.8 KB

1 Like
2

Can anyone help with this?

3

Can you provide your schema (from DreamFactory?)
GET {service name}/_schema?refresh=true
THEN
GET {service name}/_schema/{table name}
for both tables in question.

4

Hi @formerstaff,

I got into a similar issue, where the post to two related tables works fine for the parent table and not for the child table. The post request, the error and schema for both tables are given below.

Kindly help with this issue.
Thank you
-Prabhakar

curl -X POST --header 'Content-Type: application/json' --header 'Accept: application/json' --header 'X-DreamFactory-Api-Key: 36fda24fe5588fa4285ac6c6c2fdfbdb6b6bc9834699774c9bf777f706d05a88' --header 'X-DreamFactory-Session-Token: eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJzdWIiOjEsInVzZXJfaWQiOjEsImVtYWlsIjoiZXByYWJoYWthckBnbWFpbC5jb20iLCJmb3JldmVyIjpmYWxzZSwiaXNzIjoiaHR0cDpcL1wvbG9jYWxob3N0OjgwODBcL2FwaVwvdjJcL3N5c3RlbVwvYWRtaW5cL3Nlc3Npb24iLCJpYXQiOjE0NzI0NTMxNTcsImV4cCI6MTQ3MjQ1Njc1NywibmJmIjoxNDcyNDUzMTU3LCJqdGkiOiI0NzQxMTg1MWEyYTM0ZTI2MzFjMjgxZTdhY2UwNTBhYyJ9.xZPm6BQDmfjjF016Ms1H47nYmN_VamsT22cK1ABalxo' -d '{ 
  "resource": [
  {
  "UHID": "UID07",
  "NAME": "Prabhakar",
  "DOB": "2016-08-13",
  "GENDER":"m",
  "PHONE": 9999900000,
  "ADDRESS":"First Lane, Rajendra Nagar",
  "STATEID":"200",
  "DISTRICTID":"D4",
  "CITYID":"C0025",
  "DATE":"2016-08-13",
  "USERID":11,
  "DOCID": "D01",
  "REFDOCID":"ABB",
  "REGAMT": 50,
  
   "endolife.tblbilling_by_UHID":[
      {
        "DAYID":1608195,
        "TOTALAMOUNT":200,
        "AMOUNTPAID": 100,
        "AMOUNTDUE":100,
        "USERID":11,
        "DATE": "2016-08-10",
        "TYPE":"S02"
       }

   ]
  }

   ]
}' 'http://localhost:8080/api/v2/mysql/_table/endolife.tblpatinfo'

Response Error message

{
  "error": {
    "context": null,
    "message": "The endolife.tblpatinfo id can not be empty.",
    "code": 400
  }
}

The schema for the parent endolife.tblpatinfo table is given below

{
  "alias": null,
  "name": "endolife.tblpatinfo",
  "is_view": false,
  "label": "Endolife.tblpatinfo",
  "plural": "Endolife.tblpatinfos",
  "description": null,
  "primary_key": "UHID",
  "name_field": null,
  "field": [
    {
      "alias": null,
      "name": "UHID",
      "label": "UHID",
      "description": null,
      "type": "string",
      "db_type": "varchar(50)",
      "length": 50,
      "precision": null,
      "scale": null,
      "default": null,
      "required": true,
      "allow_null": false,
      "fixed_length": false,
      "supports_multibyte": true,
      "auto_increment": false,
      "is_primary_key": true,
      "is_unique": false,
      "is_index": false,
      "is_foreign_key": false,
      "is_virtual_foreign_key": false,
      "is_foreign_ref_service": false,
      "ref_service": null,
      "ref_service_id": null,
      "ref_table": null,
      "ref_fields": null,
      "ref_on_update": null,
      "ref_on_delete": null,
      "picklist": null,
      "validation": null,
      "db_function": null
    },
    {
      "alias": null,
      "name": "NAME",
      "label": "NAME",
      "description": null,
      "type": "string",
      "db_type": "varchar(50)",
      "length": 50,
      "precision": null,
      "scale": null,
      "default": null,
      "required": false,
      "allow_null": true,
      "fixed_length": false,
      "supports_multibyte": true,
      "auto_increment": false,
      "is_primary_key": false,
      "is_unique": false,
      "is_index": false,
      "is_foreign_key": false,
      "is_virtual_foreign_key": false,
      "is_foreign_ref_service": false,
      "ref_service": null,
      "ref_service_id": null,
      "ref_table": null,
      "ref_fields": null,
      "ref_on_update": null,
      "ref_on_delete": null,
      "picklist": null,
      "validation": null,
      "db_function": null
    },
    {
      "alias": null,
      "name": "DOB",
      "label": "DOB",
      "description": null,
      "type": "datetime",
      "db_type": "datetime(6)",
      "length": 6,
      "precision": null,
      "scale": null,
      "default": null,
      "required": false,
      "allow_null": true,
      "fixed_length": false,
      "supports_multibyte": false,
      "auto_increment": false,
      "is_primary_key": false,
      "is_unique": false,
      "is_index": false,
      "is_foreign_key": false,
      "is_virtual_foreign_key": false,
      "is_foreign_ref_service": false,
      "ref_service": null,
      "ref_service_id": null,
      "ref_table": null,
      "ref_fields": null,
      "ref_on_update": null,
      "ref_on_delete": null,
      "picklist": null,
      "validation": null,
      "db_function": null
    },
    {
      "alias": null,
      "name": "GENDER",
      "label": "GENDER",
      "description": null,
      "type": "string",
      "db_type": "varchar(2)",
      "length": 2,
      "precision": null,
      "scale": null,
      "default": null,
      "required": false,
      "allow_null": true,
      "fixed_length": false,
      "supports_multibyte": true,
      "auto_increment": false,
      "is_primary_key": false,
      "is_unique": false,
      "is_index": false,
      "is_foreign_key": false,
      "is_virtual_foreign_key": false,
      "is_foreign_ref_service": false,
      "ref_service": null,
      "ref_service_id": null,
      "ref_table": null,
      "ref_fields": null,
      "ref_on_update": null,
      "ref_on_delete": null,
      "picklist": null,
      "validation": null,
      "db_function": null
    },
    {
      "alias": null,
      "name": "PHONE",
      "label": "PHONE",
      "description": null,
      "type": "integer",
      "db_type": "int(11)",
      "length": 11,
      "precision": null,
      "scale": null,
      "default": null,
      "required": false,
      "allow_null": true,
      "fixed_length": false,
      "supports_multibyte": false,
      "auto_increment": false,
      "is_primary_key": false,
      "is_unique": false,
      "is_index": false,
      "is_foreign_key": false,
      "is_virtual_foreign_key": false,
      "is_foreign_ref_service": false,
      "ref_service": null,
      "ref_service_id": null,
      "ref_table": null,
      "ref_fields": null,
      "ref_on_update": null,
      "ref_on_delete": null,
      "picklist": null,
      "validation": null,
      "db_function": null
    },
    {
      "alias": null,
      "name": "ADDRESS",
      "label": "ADDRESS",
      "description": null,
      "type": "string",
      "db_type": "varchar(300)",
      "length": 300,
      "precision": null,
      "scale": null,
      "default": null,
      "required": false,
      "allow_null": true,
      "fixed_length": false,
      "supports_multibyte": true,
      "auto_increment": false,
      "is_primary_key": false,
      "is_unique": false,
      "is_index": false,
      "is_foreign_key": false,
      "is_virtual_foreign_key": false,
      "is_foreign_ref_service": false,
      "ref_service": null,
      "ref_service_id": null,
      "ref_table": null,
      "ref_fields": null,
      "ref_on_update": null,
      "ref_on_delete": null,
      "picklist": null,
      "validation": null,
      "db_function": null
    },
    {
      "alias": null,
      "name": "STATEID",
      "label": "STATEID",
      "description": null,
      "type": "string",
      "db_type": "varchar(10)",
      "length": 10,
      "precision": null,
      "scale": null,
      "default": null,
      "required": false,
      "allow_null": true,
      "fixed_length": false,
      "supports_multibyte": true,
      "auto_increment": false,
      "is_primary_key": false,
      "is_unique": false,
      "is_index": false,
      "is_foreign_key": false,
      "is_virtual_foreign_key": false,
      "is_foreign_ref_service": false,
      "ref_service": null,
      "ref_service_id": null,
      "ref_table": null,
      "ref_fields": null,
      "ref_on_update": null,
      "ref_on_delete": null,
      "picklist": null,
      "validation": null,
      "db_function": null
    },
    {
      "alias": null,
      "name": "DISTRICTID",
      "label": "DISTRICTID",
      "description": null,
      "type": "string",
      "db_type": "varchar(10)",
      "length": 10,
      "precision": null,
      "scale": null,
      "default": null,
      "required": false,
      "allow_null": true,
      "fixed_length": false,
      "supports_multibyte": true,
      "auto_increment": false,
      "is_primary_key": false,
      "is_unique": false,
      "is_index": false,
      "is_foreign_key": false,
      "is_virtual_foreign_key": false,
      "is_foreign_ref_service": false,
      "ref_service": null,
      "ref_service_id": null,
      "ref_table": null,
      "ref_fields": null,
      "ref_on_update": null,
      "ref_on_delete": null,
      "picklist": null,
      "validation": null,
      "db_function": null
    },
    {
      "alias": null,
      "name": "CITYID",
      "label": "CITYID",
      "description": null,
      "type": "string",
      "db_type": "varchar(10)",
      "length": 10,
      "precision": null,
      "scale": null,
      "default": null,
      "required": false,
      "allow_null": true,
      "fixed_length": false,
      "supports_multibyte": true,
      "auto_increment": false,
      "is_primary_key": false,
      "is_unique": false,
      "is_index": false,
      "is_foreign_key": false,
      "is_virtual_foreign_key": false,
      "is_foreign_ref_service": false,
      "ref_service": null,
      "ref_service_id": null,
      "ref_table": null,
      "ref_fields": null,
      "ref_on_update": null,
      "ref_on_delete": null,
      "picklist": null,
      "validation": null,
      "db_function": null
    },
    {
      "alias": null,
      "name": "DATE",
      "label": "DATE",
      "description": null,
      "type": "datetime",
      "db_type": "datetime(6)",
      "length": 6,
      "precision": null,
      "scale": null,
      "default": null,
      "required": false,
      "allow_null": true,
      "fixed_length": false,
      "supports_multibyte": false,
      "auto_increment": false,
      "is_primary_key": false,
      "is_unique": false,
      "is_index": false,
      "is_foreign_key": false,
      "is_virtual_foreign_key": false,
      "is_foreign_ref_service": false,
      "ref_service": null,
      "ref_service_id": null,
      "ref_table": null,
      "ref_fields": null,
      "ref_on_update": null,
      "ref_on_delete": null,
      "picklist": null,
      "validation": null,
      "db_function": null
    },
    {
      "alias": null,
      "name": "USERID",
      "label": "USERID",
      "description": null,
      "type": "integer",
      "db_type": "int(11)",
      "length": 11,
      "precision": null,
      "scale": null,
      "default": null,
      "required": false,
      "allow_null": true,
      "fixed_length": false,
      "supports_multibyte": false,
      "auto_increment": false,
      "is_primary_key": false,
      "is_unique": false,
      "is_index": false,
      "is_foreign_key": false,
      "is_virtual_foreign_key": false,
      "is_foreign_ref_service": false,
      "ref_service": null,
      "ref_service_id": null,
      "ref_table": null,
      "ref_fields": null,
      "ref_on_update": null,
      "ref_on_delete": null,
      "picklist": null,
      "validation": null,
      "db_function": null
    },
    {
      "alias": null,
      "name": "DOCID",
      "label": "DOCID",
      "description": null,
      "type": "string",
      "db_type": "varchar(10)",
      "length": 10,
      "precision": null,
      "scale": null,
      "default": null,
      "required": false,
      "allow_null": true,
      "fixed_length": false,
      "supports_multibyte": true,
      "auto_increment": false,
      "is_primary_key": false,
      "is_unique": false,
      "is_index": false,
      "is_foreign_key": false,
      "is_virtual_foreign_key": false,
      "is_foreign_ref_service": false,
      "ref_service": null,
      "ref_service_id": null,
      "ref_table": null,
      "ref_fields": null,
      "ref_on_update": null,
      "ref_on_delete": null,
      "picklist": null,
      "validation": null,
      "db_function": null
    },
    {
      "alias": null,
      "name": "REFDOCID",
      "label": "REFDOCID",
      "description": null,
      "type": "string",
      "db_type": "varchar(10)",
      "length": 10,
      "precision": null,
      "scale": null,
      "default": null,
      "required": false,
      "allow_null": true,
      "fixed_length": false,
      "supports_multibyte": true,
      "auto_increment": false,
      "is_primary_key": false,
      "is_unique": false,
      "is_index": false,
      "is_foreign_key": false,
      "is_virtual_foreign_key": false,
      "is_foreign_ref_service": false,
      "ref_service": null,
      "ref_service_id": null,
      "ref_table": null,
      "ref_fields": null,
      "ref_on_update": null,
      "ref_on_delete": null,
      "picklist": null,
      "validation": null,
      "db_function": null
    },
    {
      "alias": null,
      "name": "REGAMT",
      "label": "REGAMT",
      "description": null,
      "type": "decimal",
      "db_type": "decimal(9,2)",
      "length": 9,
      "precision": 9,
      "scale": 2,
      "default": null,
      "required": false,
      "allow_null": true,
      "fixed_length": false,
      "supports_multibyte": false,
      "auto_increment": false,
      "is_primary_key": false,
      "is_unique": false,
      "is_index": false,
      "is_foreign_key": false,
      "is_virtual_foreign_key": false,
      "is_foreign_ref_service": false,
      "ref_service": null,
      "ref_service_id": null,
      "ref_table": null,
      "ref_fields": null,
      "ref_on_update": null,
      "ref_on_delete": null,
      "picklist": null,
      "validation": null,
      "db_function": null
    }
  ],
  "related": [
    {
      "alias": null,
      "name": "endolife.tblbilling_by_UHID",
      "label": "Endolife Tblbilling By UHID",
      "description": null,
      "always_fetch": false,
      "flatten": false,
      "flatten_drop_prefix": false,
      "type": "has_many",
      "field": "UHID",
      "is_virtual": false,
      "is_foreign_service": false,
      "ref_service": null,
      "ref_service_id": null,
      "ref_table": "endolife.tblbilling",
      "ref_fields": "UHID",
      "ref_on_update": null,
      "ref_on_delete": null,
      "is_foreign_junction_service": false,
      "junction_service": null,
      "junction_service_id": null,
      "junction_table": null,
      "junction_field": null,
      "junction_ref_field": null
    }
  ],
  "access": 31
}

The schema for the child endolife.tblbiliing table is given below.


{
  "alias": null,
  "name": "endolife.tblbilling",
  "is_view": false,
  "label": "Endolife.tblbilling",
  "plural": "Endolife.tblbillings",
  "description": null,
  "primary_key": null,
  "name_field": null,
  "field": [
    {
      "alias": null,
      "name": "DAYID",
      "label": "DAYID",
      "description": null,
      "type": "bigint",
      "db_type": "bigint(20)",
      "length": 20,
      "precision": null,
      "scale": null,
      "default": null,
      "required": false,
      "allow_null": true,
      "fixed_length": false,
      "supports_multibyte": false,
      "auto_increment": false,
      "is_primary_key": false,
      "is_unique": false,
      "is_index": false,
      "is_foreign_key": false,
      "is_virtual_foreign_key": false,
      "is_foreign_ref_service": false,
      "ref_service": null,
      "ref_service_id": null,
      "ref_table": null,
      "ref_fields": null,
      "ref_on_update": null,
      "ref_on_delete": null,
      "picklist": null,
      "validation": null,
      "db_function": null
    },
    {
      "alias": null,
      "name": "TOTALAMOUNT",
      "label": "TOTALAMOUNT",
      "description": null,
      "type": "decimal",
      "db_type": "decimal(9,2)",
      "length": 9,
      "precision": 9,
      "scale": 2,
      "default": null,
      "required": false,
      "allow_null": true,
      "fixed_length": false,
      "supports_multibyte": false,
      "auto_increment": false,
      "is_primary_key": false,
      "is_unique": false,
      "is_index": false,
      "is_foreign_key": false,
      "is_virtual_foreign_key": false,
      "is_foreign_ref_service": false,
      "ref_service": null,
      "ref_service_id": null,
      "ref_table": null,
      "ref_fields": null,
      "ref_on_update": null,
      "ref_on_delete": null,
      "picklist": null,
      "validation": null,
      "db_function": null
    },
    {
      "alias": null,
      "name": "AMOUNTPAID",
      "label": "AMOUNTPAID",
      "description": null,
      "type": "decimal",
      "db_type": "decimal(9,2)",
      "length": 9,
      "precision": 9,
      "scale": 2,
      "default": null,
      "required": false,
      "allow_null": true,
      "fixed_length": false,
      "supports_multibyte": false,
      "auto_increment": false,
      "is_primary_key": false,
      "is_unique": false,
      "is_index": false,
      "is_foreign_key": false,
      "is_virtual_foreign_key": false,
      "is_foreign_ref_service": false,
      "ref_service": null,
      "ref_service_id": null,
      "ref_table": null,
      "ref_fields": null,
      "ref_on_update": null,
      "ref_on_delete": null,
      "picklist": null,
      "validation": null,
      "db_function": null
    },
    {
      "alias": null,
      "name": "AMOUNTDUE",
      "label": "AMOUNTDUE",
      "description": null,
      "type": "decimal",
      "db_type": "decimal(9,2)",
      "length": 9,
      "precision": 9,
      "scale": 2,
      "default": null,
      "required": false,
      "allow_null": true,
      "fixed_length": false,
      "supports_multibyte": false,
      "auto_increment": false,
      "is_primary_key": false,
      "is_unique": false,
      "is_index": false,
      "is_foreign_key": false,
      "is_virtual_foreign_key": false,
      "is_foreign_ref_service": false,
      "ref_service": null,
      "ref_service_id": null,
      "ref_table": null,
      "ref_fields": null,
      "ref_on_update": null,
      "ref_on_delete": null,
      "picklist": null,
      "validation": null,
      "db_function": null
    },
    {
      "alias": null,
      "name": "USERID",
      "label": "USERID",
      "description": null,
      "type": "integer",
      "db_type": "int(11)",
      "length": 11,
      "precision": null,
      "scale": null,
      "default": null,
      "required": false,
      "allow_null": true,
      "fixed_length": false,
      "supports_multibyte": false,
      "auto_increment": false,
      "is_primary_key": false,
      "is_unique": false,
      "is_index": false,
      "is_foreign_key": false,
      "is_virtual_foreign_key": false,
      "is_foreign_ref_service": false,
      "ref_service": null,
      "ref_service_id": null,
      "ref_table": null,
      "ref_fields": null,
      "ref_on_update": null,
      "ref_on_delete": null,
      "picklist": null,
      "validation": null,
      "db_function": null
    },
    {
      "alias": null,
      "name": "DATE",
      "label": "DATE",
      "description": null,
      "type": "datetime",
      "db_type": "datetime(6)",
      "length": 6,
      "precision": null,
      "scale": null,
      "default": null,
      "required": false,
      "allow_null": true,
      "fixed_length": false,
      "supports_multibyte": false,
      "auto_increment": false,
      "is_primary_key": false,
      "is_unique": false,
      "is_index": false,
      "is_foreign_key": false,
      "is_virtual_foreign_key": false,
      "is_foreign_ref_service": false,
      "ref_service": null,
      "ref_service_id": null,
      "ref_table": null,
      "ref_fields": null,
      "ref_on_update": null,
      "ref_on_delete": null,
      "picklist": null,
      "validation": null,
      "db_function": null
    },
    {
      "alias": null,
      "name": "TYPE",
      "label": "TYPE",
      "description": null,
      "type": "string",
      "db_type": "varchar(10)",
      "length": 10,
      "precision": null,
      "scale": null,
      "default": null,
      "required": false,
      "allow_null": true,
      "fixed_length": false,
      "supports_multibyte": true,
      "auto_increment": false,
      "is_primary_key": false,
      "is_unique": false,
      "is_index": false,
      "is_foreign_key": false,
      "is_virtual_foreign_key": false,
      "is_foreign_ref_service": false,
      "ref_service": null,
      "ref_service_id": null,
      "ref_table": null,
      "ref_fields": null,
      "ref_on_update": null,
      "ref_on_delete": null,
      "picklist": null,
      "validation": null,
      "db_function": null
    },
    {
      "alias": null,
      "name": "UHID",
      "label": "UHID",
      "description": null,
      "type": "string",
      "db_type": "varchar(50)",
      "length": 50,
      "precision": null,
      "scale": null,
      "default": null,
      "required": true,
      "allow_null": false,
      "fixed_length": false,
      "supports_multibyte": true,
      "auto_increment": false,
      "is_primary_key": false,
      "is_unique": false,
      "is_index": true,
      "is_foreign_key": true,
      "is_virtual_foreign_key": false,
      "is_foreign_ref_service": false,
      "ref_service": null,
      "ref_service_id": null,
      "ref_table": "endolife.tblpatinfo",
      "ref_fields": "UHID",
      "ref_on_update": null,
      "ref_on_delete": null,
      "picklist": null,
      "validation": null,
      "db_function": null
    }
  ],
  "related": [
    {
      "alias": null,
      "name": "endolife.tblpatinfo_by_UHID",
      "label": "Endolife Tblpatinfo By UHID",
      "description": null,
      "always_fetch": false,
      "flatten": false,
      "flatten_drop_prefix": false,
      "type": "belongs_to",
      "field": "UHID",
      "is_virtual": false,
      "is_foreign_service": false,
      "ref_service": null,
      "ref_service_id": null,
      "ref_table": "endolife.tblpatinfo",
      "ref_fields": "UHID",
      "ref_on_update": null,
      "ref_on_delete": null,
      "is_foreign_junction_service": false,
      "junction_service": null,
      "junction_service_id": null,
      "junction_table": null,
      "junction_field": null,
      "junction_ref_field": null
    }
  ],
  "access": 31
}
5

Please help with the above issue.

6

what type of database is this and what version of DreamFactory are you running?

7

I am having the same issue. Database is MySQL.

Details: I have the parent/child tables with foreign key on child table referencing parent tables primary key and foreign key is non nullable. I am able to insert the record in the parent table with out any issues. But fails on Child table telling ‘Foreign_key_column_name’ cannot be null.

Please help.