Azure
Add capability to modify the linking table even if it is not exposed to satisfy foreign key constraints
In M:N relationships when the linking table is not exposed, we need to make sure mutation on the parent tables trigger deletion of rows in the linking table as well. E.g. book_author_link should have its row deleted when a book with book_id referenced in that row is deleted from the books table.
Consider three tables: books, authors and book_author_link tables. There is a M:N relationship between books and authors. book_author_link is the linking table. book_author_link consists of two columns : book_id, author_id as foreign key references to books and authors respectively.
When a delete mutation is executed on either books or authors, the behavior depends on whether CASCADE is set up or not.
- When
ON DELETE CASCADEis setup forauthor_idcolumn in thebook_author_link, then executing a delete mutation (deleteAuthor) results in deletion of entries in bothauthorsandbook_author_linktables. - When
ON DELETE CASCADEis not setup, then executing a delete mutation results in an error.
Error Message:
IMHO, the current behavior that we observe at the moment is what we would want, as it honors the way the tables (and relationships) are setup.
@Aniruddh25 @yorek Please kindly share your thoughts.
CASCADE deletes is a good find, we need to document this. How about updates/inserts? Would the linking table be modified? Would this issue then be same as #479
- When the primary key of a table is not auto-generated, then the update mutation's input type exposes the
idfield. If the primary key is auto-generated, then theidfield is not exposed and updating theidis not possible. - For the cases where the
idis exposed through the update mutation, when an update to theidis performed, the behavior depends on whetherON UPDATE CASCADEis setup. The behavior is very similar to that of delete. If the update cascade is setup, the mutation succeeds and the update flows to linking table as well. - If
ON UPDATE CASCADEis not setup, then the mutation results in an error - Inserting a new book, author and a relationship between these two, will come under nested mutations.
@severussundar @ayush3797 is this needed for Nested mutations?
I don't think this is needed. The user can setup CASCADE DELETE or use an AFTER DELETE trigger to ensure consistency of the linking table. In future, we might consider an option to somehow enable them to specify if they want to peform the deletions in the linking table as well, in which case we can figure out some logic to maintain consistency. However, this for sure should not be an immediate concern.
As outlined in the previous comments, when CASCADE DELETE/CASCADE UPDATE or triggers are setup, the foreign key integrity is taken care of.
To support the same via a GraphQL operation through DAB without requiring users to setup triggers or cascade operations,
- Update: DAB needs to support nested/multiple update feature where updating a Book + Author is possible.
- Delete: For delete, the use-case can be the following. Let's say a Book is associated with 3 Authors name Author1, Author2, Author3. The user wants to remove the link between Book and Author3, but not delete Author3 as it can have associations with other Book items. To support this, DAB has to build a net new delete mutation operation where schema defines a way to accept inputs in the linking table; also query generation logic needs to be updated to understand and process such a delete mutation.
Current requirement is entities must be in config to be modified. The requirement for table mod is met this way, even if the case of this issue is not. For now, we will keep mod ops limited to entities actually in the config file.
