I am trying to configure a relationship between a table called ‘items’ and a table called ‘stores’. The items table has a StoreID field that I want for the foreign key. However, I cannot get it to save the relationship from JSON update. Says it saved successfully, but it doesn’t update. Also, I can’t edit through the regular add field editor because the related field drop-down does not show any tables. I am using a fresh install of 1.9.0 on AWS
I have the same problem. But I managed to get relationships by posting JSON schema of my entire database at once (by using createTables() in API Docs)
First, You have to create relationships with foreign keys pointing to primary keys of Your reference table (unless You know how to create unique keys). Use this example as a Reference:
{
"name": "ThornUsers",
"label": "ThornUsers",
"plural": "ThornUsers",
"primary_key": "Id",
"name_field": null,
"field": [
{
"name": "Id",
"label": "Id",
"type": "id",
"db_type": "int(11)",
"length": 11,
"precision": 11,
"scale": 0,
"default": null,
"required": false,
"allow_null": false,
"fixed_length": false,
"supports_multibyte": false,
"auto_increment": true,
"is_primary_key": true,
"is_foreign_key": false,
"ref_table": "",
"ref_fields": "",
"validation": null,
"value": []
},
{
"name": "Username",
"label": "Username",
"type": "string",
"db_type": "varchar(255)",
"length": 255,
"precision": 255,
"scale": 0,
"default": null,
"required": true,
"allow_null": false,
"fixed_length": false,
"supports_multibyte": true,
"auto_increment": false,
"is_primary_key": false,
"is_foreign_key": false,
"ref_table": "",
"ref_fields": "",
"validation": null,
"value": []
}
],
"related": [
{
"name": "Leases_by_ThornUserId",
"type": "has_many",
"ref_table": "Leases",
"ref_field": "ThornUserId",
"field": "Id"
},
{
"name": "Rentables_by_Leases",
"type": "many_many",
"ref_table": "Rentables",
"ref_field": "Id",
"join": "Leases(ThornUserId,RentableId)",
"field": "Id"
},
{
"name": "Rentables_by_OwnerId",
"type": "has_many",
"ref_table": "Rentables",
"ref_field": "OwnerId",
"field": "Id"
}
],
"access": [
"GET",
"POST",
"PUT",
"PATCH",
"MERGE",
"DELETE"
]
},
{
"name": "Leases",
"label": "Leases",
"plural": "Leases",
"primary_key": "RentableId",
"name_field": null,
"field": [
{
"name": "RentableId",
"label": "RentableId",
"type": "reference",
"db_type": "int(11)",
"length": 11,
"precision": 11,
"scale": 0,
"default": null,
"required": true,
"allow_null": false,
"fixed_length": false,
"supports_multibyte": false,
"auto_increment": false,
"is_primary_key": true,
"is_foreign_key": true,
"ref_table": "Rentables",
"ref_fields": "Id",
"validation": null,
"value": []
},
{
"name": "ThornUserId",
"label": "ThornUserId",
"type": "reference",
"db_type": "int(11)",
"length": 11,
"precision": 11,
"scale": 0,
"default": null,
"required": true,
"allow_null": false,
"fixed_length": false,
"supports_multibyte": false,
"auto_increment": false,
"is_primary_key": false,
"is_foreign_key": true,
"ref_table": "ThornUsers",
"ref_fields": "Id",
"validation": null,
"value": []
},
{
"name": "StartTimestamp",
"label": "StartOfLease",
"type": "timestamp_on_create",
"db_type": "timestamp",
"length": 0,
"precision": 0,
"scale": 0,
"default": "0000-00-00 00:00:00",
"required": false,
"allow_null": false,
"fixed_length": false,
"supports_multibyte": false,
"auto_increment": false,
"is_primary_key": false,
"is_foreign_key": false,
"ref_table": "",
"ref_fields": "",
"validation": null,
"value": []
},
{
"name": "Ongoing",
"label": "OngoingLease",
"type": "boolean",
"db_type": "tinyint(1)",
"length": 1,
"precision": 1,
"scale": 0,
"default": 1,
"required": false,
"allow_null": true,
"fixed_length": false,
"supports_multibyte": false,
"auto_increment": false,
"is_primary_key": false,
"is_foreign_key": false,
"ref_table": "",
"ref_fields": "",
"validation": null,
"value": []
},
{
"name": "EndTimestamp",
"label": "EndOfLease",
"type": "timestamp",
"db_type": "timestamp",
"length": 0,
"precision": 0,
"scale": 0,
"default": null,
"required": false,
"allow_null": true,
"fixed_length": false,
"supports_multibyte": false,
"auto_increment": false,
"is_primary_key": false,
"is_foreign_key": false,
"ref_table": "",
"ref_fields": "",
"validation": null,
"value": []
}
],
"related": [
{
"name": "Rentables_by_RentableId",
"type": "belongs_to",
"ref_table": "Rentables",
"ref_field": "Id",
"field": "RentableId"
},
{
"name": "ThornUsers_by_ThornUserId",
"type": "belongs_to",
"ref_table": "ThornUsers",
"ref_field": "Id",
"field": "ThornUserId"
}
],
"access": [
"GET",
"POST",
"PUT",
"PATCH",
"MERGE",
"DELETE"
]
}
You have every possible relationship (belongs_to, has_many, many_many) declared in this example, so just fork it to suit Your needs.
1 Like
I had same problem. And sometimes since some table is already created and work well. Users would rather not change the whole schemes all together to ruin the data.
I did a research and found the cause. My mysql engine is MyISAM which does not support foreign_key constraints. The only engine that supports foreign_key is innoDB.
I guess its the magic underlying dreamfactory relationship. so hopefully everyone knows it before implementing relationship. I convert all my tables to innoDB engine and relationship is good to create.
Here is some references:
8.1.4 Creating Foreign Key Relationships
[+/-]
8.1.4.1 Adding Foreign Key Relationships Using an EER Diagram
8.1.4.2 The Relationship Editor
8.1.4.3 Connection Properties
Foreign key constraints are supported for the InnoDB storage engine only. For other storage engines, the foreign key syntax is correctly parsed but not implemented. For more information, see Foreign Key Differences.
Using MySQL Workbench you may add a foreign key from within the table editor or by using the relationship tools on the vertical toolbar of an EER Diagram. This section deals with adding a foreign key using the foreign key tools. To add a foreign key using the table editor, see Section 7.1.12.4, “The Foreign Keys Tab”.
The graphical tools for adding foreign keys are most effective when you are building tables from the ground up. If you have imported a database using an SQL script and need not add columns to your tables, you may find it more effective to define foreign keys using the table editor.
1 Like