frappe
frappe copied to clipboard
frappe
Support foreign keys in Database
Is your feature request related to a problem? Please describe. The Database-API is currently lacking support for foreign keys. This leads to an unstructured database and makes querying structured data hard. Having foreign keys, introduces more a hierarchy to the tables, makes 1:n/n:1/n:m relationships clearer. Introducing these would also provide additional checks for data-consistency and update/delete logic.
Describe the solution you'd like
It would be nice to be able to define foreign keys and key constraints in the database api. This would best be archived by extending the frappe/database/schema.py file to include foreign-key definitions when creating the table. The syntax has to be generated for both postgres and mariaDB as both have different syntax.
postgresDB Syntax
mariaDB Syntax
Describe alternatives you've considered Currently there is no way at all to define foreign-keys via the API (as far as I can tell 😄). The only way is to create these by accessing the database directly.
Additional context First and foremost, I would like to know, if there are plans to support foreign keys.
Some considerations:
- This can be done without breaking/deprecating the database API
- Potentially, supporting foreign keys, would introduce a double structure, with the Api Logic and these database constraints.
- It would help understanding the structure of frappe-databases, when first learning new systems (eg. the erpNext database is not understandable without major work)
Some arrows would make things easier here
At present, there is still a controversy about whether it is good or bad to use relational fields in database . But there are some obvious disadvantages: 1, the use of associated fields will bring great trouble to rapid iterative development 2. In the case of large concurrency, the query speed is affected
But I also want to know the development team's consideration on this matter . e.g what deliberation is about giving up relational fields . How to ensure data consistency .
Just for discussing . From China, please forgive the mistakes in English . Best regards.
It would be of great help if the development team can provide the technical considerations on implementing application level referential integrity over the traditional database level referential integrity.
