Lesson 4.3: Deleting data
The Delete query
To delete data from the database, we use a delete clause. Delete clauses always follow some preceding query clauses. The way we write Delete queries depends on if we want to delete an entity, relation, or attribute. To begin with, we’ll look at an example of an attribute.
Deleting attributes
It turns out that Fundamentals of Data Engineering has been mistakenly assigned the genre "children’s fiction" (in addition to "nonfiction" and "technology"). We should probably change that before there are too many upset children, which we’ll do with the following Delete query. To run a Delete query, use a write transaction, run the query, and commit the transaction.
match
$fundamentals isa book, has isbn "9781098108274";
$genre isa genre "children's fiction";
delete
has $genre of $fundamentals;→ Commit
To begin with, we’ve matched the book entity by its ISBN in the match clause. Then, we’ve matched the genre attribute by its value. Finally, we’ve deleted the entity’s ownership of the attribute using a different kind of syntax.
In TypeQL, Delete statements (similar to Undefine statements) specify first what to delete from a specific piece of data or schema. Here, we’re specifying we want to delete the has $genre of the $fundamental entity. In other words, we’re going to remove the connection between the book and the genre.
Deleting relations
Next, we’ll see how to delete relations. The courier that is supposed to be delivering order "o0033" has notified us that they will be unable to fulfill the order and are cancelling the pickup. Until we are able to arrange an alternative courier, we should record this fact in the database by deleting the delivery relation that the order plays a role in, which we do with the following query.
match
$order-33 isa order, has id "o0033";
$delivery-33 isa delivery ($order-33);
delete
$delivery-33;→ Commit!
Similar to the previous example, we’ve begun by matching the order by its order ID. Next, we’ve matched the delivery relation in which it plays a role. We’ve omitted the role and let TypeDB infer it. Role inference means we’ll match any delivery in which $order-33 plays any role, but in this case only one such relation exists, in which $order-33 plays delivery:delivered. We’ve also omitted the role players we aren’t interested in from the tuple: the courier and the address. We don’t need to know them to identify or delete the relation!
Finally, the delete clause deletes the relation. Deleting a full entity, relation, or attribute is super easy - just delete the variable itself!
In Lesson 4.2, we added all books to the Spring Sale 2024. However, we’d like to remove The Complete Calvin and Hobbes as it is a special edition, and so shouldn’t be included in the sale. Remove it from the sale by deleting the relevant promotion-inclusion relation. As per good practice, make sure to identify it by ISBN.
Hint 1
To get the correct ISBN, you can use the following query.
match
$calvin-hobbes isa book, has title "The Complete Calvin and Hobbes";
fetch {
"isbn": $calvin-hobbes.isbn,
};Hint 2
To delete the book from only this particular sale, you will need to match the relation by both role players.
Sample solution
match
$spring-sale isa promotion, has code "SPR24";
$calvin-hobbes isa book, has isbn "9780740748479";
$inclusion isa promotion-inclusion ($spring-sale, $calvin-hobbes);
delete
$inclusion;→ Commit!
Deleting entities
Last, we’ll see how to delete entities, which is very similar to deleting relations. Let’s say a user has decided to delete a review they made of the book. In the following queries, we perform their request. First of all, we need to retrieve the ID of the review to be deleted.
match
$book isa book, has isbn "9781859840665";
$user-9 isa user, has id "u0009";
$review isa review;
rating ($review, $book);
action-execution ($review, $user-9);
fetch {
"id": $review.id,
};{ "id": "r0004" }Now that we know the review’s ID, we can go ahead and delete it. We start by deleting any relations that the review plays a role in.
match
$review-4 isa review, has id "r0004";
$rel links ($review-4);
delete
$rel;Once that’s done, we can delete the review itself.
match
$review-4 isa review, has id "r0004";
delete
$review-4;→ Commit!
When deleting an entity, it’s important to also delete any relations it plays a role in. If we don’t, then those relations would be left behind without the entity as a roleplayer. A relation is only deleted automatically if all of its role players are deleted.
|
When deleting a roleplayer, related relations are not deleted automatically unless your schema uses role |
|
The method we’ve used to delete this entity relies on the fact that the review has a unique ID we can retrieve and then match on. If this were not the case, properly deleting it might be significantly harder. For this reason, it’s good to ensure that any entity types that might be regularly deleted have key attributes that we can use. We’ll explore this further in Lesson 5. |
We will no longer be stocking Pet Sematary. Delete it from the database. Remember to delete all the relations it plays a role in first.
Hint
To get the correct ISBN, you can use the following query.
match
$pet-sematary isa book, has title "Pet Sematary";
fetch {
"isbn": $pet-sematary.isbn;
};Sample solution
match
$pet-sematary isa book, has isbn "9780451162076";
$rel links ($pet-sematary);
delete
$rel;match
$pet-sematary isa book, has isbn "9780451162076";
delete
$pet-sematary;→ Commit!