Back

[SOLVED] relationships mariadb

  • 0
  • Databases
nev-21
5 Jun, 2023, 13:41

Does appwrite enforce data integrity?

TL;DR
The user is discussing the concept of relationships and data integrity in a MariaDB database used with Appwrite. They mention the possibility of orphan rows and suggest using foreign keys or a query parameter to check for orphans when performing actions. They also reference a Stack Overflow question on foreign key usage. They make a feature request for optional use of MariaDB foreign keys and a parameter to check for orphans in requests. They also mention the idea of having optional database foreign keys when creating a relationship in a collection. The issue is marked as solved and they express confidence in the data integrity of Appwrite. Solution: The user suggests using foreign keys
Binyamin
5 Jun, 2023, 14:05

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

nev-21
5 Jun, 2023, 14:12

Yeah, it looks good, functions can be used to validate actions with orphan rows

D5
5 Jun, 2023, 14:13

Never saw here any problems related to data integrity related, so I think that should not be a problem

D5
5 Jun, 2023, 14:13

Could happen, yes, and in any platform/database too, but really unlikely

nev-21
5 Jun, 2023, 14:43

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.

nev-21
5 Jun, 2023, 14:43

Or maybe make DB FK optional when creating a relationship in a collection

nev-21
5 Jun, 2023, 14:44

[SOLVED] relationships mariadb

nev-21
5 Jun, 2023, 15:04
D5
5 Jun, 2023, 15:11

@nev-21 I don't understand at all such feature, what's the difference with relationships?

nev-21
5 Jun, 2023, 15:51

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

D5
5 Jun, 2023, 16:31

And what's the advantage/feature you get by using it?

nev-21
5 Jun, 2023, 17:15

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

nev-21
5 Jun, 2023, 17:20
D5
5 Jun, 2023, 17:20

Appwrite relationships have the possibility to make in-delete cascading if needed, so what's the difference?

D5
5 Jun, 2023, 17:21

Also you can set relationships to a null value, or restrict

nev-21
5 Jun, 2023, 17:21

Yeah I think that is good

nev-21
5 Jun, 2023, 17:24

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.

nev-21
5 Jun, 2023, 17:25

so to avoid that, DB FK can be used, or a param in request that check for orphans when an action is needed

D5
5 Jun, 2023, 17:26

Probably it will throw an error or apply the defined action (for example deleting), but not tried

nev-21
5 Jun, 2023, 17:36

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.

nev-21
5 Jun, 2023, 17:37

It will not throw an exception

D5
5 Jun, 2023, 17:37

I'm going to check, but probably appwrite checks that by default

nev-21
5 Jun, 2023, 17:37

cause the assigned department is simple the id

nev-21
5 Jun, 2023, 17:38

please

Reply

Reply to this thread by joining our Discord

Reply on Discord

Need support?

Join our Discord

Get community support by joining our Discord server.

Join Discord

Get premium support

Join Appwrite Pro and get email support from our team.

Learn more