Does appwrite enforce data integrity?
From what I've seen in your use case the department
will be assigned to the value.
But, When you'll fetch the User -> departments (one-to-many for example) you won't get the non-existing one.
So to summarize
- If you had deleted the
department
after it was created then of the three will happened- Set Null
- Cascading
- Restricting document
- If you have that edge-case (that can happened) and a relationship was deleted in the middle of the process then the field will have the value but you won't see it, ever.
So as far of data integrity it looks pretty solid to me.
Most of the magic is going in this method https://github.com/utopia-php/database/blob/main/src/Database/Database.php#L2552
Yeah, it looks good, functions can be used to validate actions with orphan rows
Never saw here any problems related to data integrity related, so I think that should not be a problem
Could happen, yes, and in any platform/database too, but really unlikely
Thanks for your answers, it would be nice if the appwrite crud methods/requests have an an optional param to check if relations are orphans, for example if I create/update a record, it can optionally check the relational ids to check if they exists. That should reduce the code developers must write if avoiding orphans is a requirement.
Or maybe make DB FK optional when creating a relationship in a collection
[SOLVED] relationships mariadb
I have made a feature request: https://github.com/appwrite/appwrite/issues/5662
@nev-21 I don't understand at all such feature, what's the difference with relationships?
Current relationships are created with soft-foreign-keys, the feature request is about to optionally use mariadb foreign-keys, and optionally have a query param in requests that checks orphans
And what's the advantage/feature you get by using it?
In my opinion, it applies only when avoiding orphans is a requirement, if having orphans is not a problem the default behaviour of appwrite will be ok. I think the advantages is to avoid making repetitive checks with functions for every model that should not have orphans
I was reading this question: https://stackoverflow.com/questions/83147/whats-wrong-with-foreign-keys
Appwrite relationships have the possibility to make in-delete cascading if needed, so what's the difference?
Also you can set relationships to a null value, or restrict
Yeah I think that is good
but that is not the problem, I was talking about orphan rows: When assigning an employee to a departement, but before press save, other authorized user delete the department. That employee will belong to a non-existing department.
so to avoid that, DB FK can be used, or a param in request that check for orphans when an action is needed
Probably it will throw an error or apply the defined action (for example deleting), but not tried
Yeah I didn't realize, but I think still exist the possibility of a hidden bug (that maybe is critical for the bussines logic) for example: all employees that have an assigned department, can have access to special data(or files), If I forgot check orphans, an employee (with orphan) still have access.
It will not throw an exception
I'm going to check, but probably appwrite checks that by default
cause the assigned department is simple the id
please
Recommended threads
- Type Mismatch in AppwriteException
There is a discrepancy in the TypeScript type definitions for AppwriteException. The response property is defined as a string in the type definitions, but in pr...
- What Query's are valid for GetDocument?
Documentation shows that Queries are valid here, but doesn't explain which queries are valid. At first I presumed this to be a bug, but before creating a githu...
- Appwrite exception: user_unauthorized, t...
After refreshing the app it is working perfectly