[SOLVED] relationships mariadb

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