Join Adocasts Plus for $8/mo, or sign into an existing Adocasts Plus account, to get access to all of our lessons.
We'll begin work on our organization's settings page by adding the ability to update and delete the active organization.
Everything works except the delete. I get
organizations where id = 3 - Cannot delete or update a parent row: a foreign key constraint failsI feel like maybe I had something like this in a previous lesson, but can't find it. Is this something with how AdonisJS handles relationships in MySQL? Do I have to alter the code slightly to properly delete related modules?
Hey Aaron!
What you're getting is a foreign key constraint error. You get this when you attempt to delete a record whose id is referenced by another record's foreign key. Foreign keys are essentially in charge of id integrity within our database, ensuring that we aren't referencing any ids that don't actually exist.
For example, if I have an organization with an id of 3 and a course with an id of 1 that contains an organization_id of 3. When I attempt to delete the organization with an id of 3 the course record, via it's foreign key constraint, will prevent the organization from being deleted because the course's organization_id wouldn't exist anymore. As such, the course with an organization_id of 3 must be deleted before we can actually delete the organization with an id of 3.
Throughout this series, we don't need to worry to much about any of this because when we setup our database, through our migrations, we added instructions to our database to cascade deletions. Meaning, if we attempt to delete the organization with an id of 3, our database will automatically cascade that deletion to also delete our courses with an organization_id of 3.
table .integer('organization_id') .unsigned() .references('organizations.id') .onDelete('CASCADE') // 👈 .notNullable()Copied!
It sounds like you might be missing one or more of these cascade instructions. The error you're getting should include the foreign key's name that prevented the deletion. You should be able to use the foreign key's name to determine which relationship is not cascading. I believe you should then be able to switch it to cascade using something like the below. Alternatively, you could just delete the needed records prior to deleting your organization in code (here's an example of that being done).
this.schema.alterTable(this.tableName, (table) => { table.dropForeign("organization_id"); table .foreign("organization_id") .references("organizations.id") .onDelete("CASCADE") });Copied!