Search⌘ K
Home
Courses
SQL Antipatterns: Avoiding the Pitfalls of Database Programming

Maintaining a Tree with an Adjacency List

Explore how the adjacency list model helps maintain hierarchical tree structures in SQL databases. Understand key operations like inserting new leaf nodes, relocating subtrees, and deleting nodes while preserving data integrity. This lesson explains the multiple queries required for subtree deletions and practical use of foreign keys to automate processes, enhancing your database management skills.

We'll cover the following...

Many operations can be accomplished using the Adjacency List.

Inserting a new leaf node

Some operations are simple to accomplish with an Adjacency List, such as adding a new leaf node.

Let’s see how we can add a new leaf node:

MySQL
INSERT INTO Comments (bug_id, parent_id, author, comment)
     VALUES (1234, 7, 'Kukla', 'Thanks!');

After inserting the record, let’s retrieve the data in the next playground.

MySQL
INSERT INTO Comments (bug_id, parent_id, author, comment)
     VALUES (1234, 7, 'Kukla', 'Thanks!');
SELECT comment_id, parent_id, author, comment FROM Comments 
 WHERE bug_id = 1234;

Relocating a single node or a subtree

The process of relocating a single node or a subtree is also easy. Have a look at the next query for more clarification:

MySQL
UPDATE Comments SET parent_id = 3 WHERE comment_id = 6;

We are updating the table by setting 3 as a parent of 6 in the query. Now the parent of node 6 is 3. Let’s ...