인접 리스트와 순환 방지

DROP TABLE IF EXISTS categories_bad;

CREATE TABLE categories_bad (
  category_id BIGINT UNSIGNED NOT NULL,
  category_name VARCHAR(100) NOT NULL,
  parent_id BIGINT UNSIGNED NULL,
  PRIMARY KEY (category_id),
  CONSTRAINT fk_category_bad_parent FOREIGN KEY (parent_id)
    REFERENCES categories_bad (category_id)
) ENGINE=InnoDB;

INSERT INTO categories_bad
  (category_id, category_name, parent_id)
VALUES
  (1, '개발', NULL),
  (2, '백엔드', 1),
  (3, 'Spring', 2);

UPDATE categories_bad
SET parent_id = 3
WHERE category_id = 1;

WITH RECURSIVE descendants AS (
  SELECT category_id, category_name, parent_id, 0 AS depth
  FROM categories_bad
  WHERE category_id = 1
  UNION ALL
  SELECT c.category_id, c.category_name, c.parent_id, d.depth + 1
  FROM categories_bad AS c
  JOIN descendants AS d ON c.parent_id = d.category_id
)
SELECT category_id, category_name, depth
FROM descendants;

UPDATE: Query OK, 1 row affected

ERROR 3636 (HY000): Recursive query aborted after 1001 iterations.
Try increasing @@cte_max_recursion_depth.

foreign key violations: 0

DROP PROCEDURE IF EXISTS move_category_adjacency;
DROP TABLE IF EXISTS categories;

CREATE TABLE categories (
  category_id BIGINT UNSIGNED NOT NULL,
  category_name VARCHAR(100) NOT NULL,
  parent_id BIGINT UNSIGNED NULL,
  sort_order SMALLINT UNSIGNED NOT NULL,
  PRIMARY KEY (category_id),
  CONSTRAINT fk_categories_parent FOREIGN KEY (parent_id)
    REFERENCES categories (category_id) ON DELETE RESTRICT,
  CONSTRAINT chk_categories_self
    CHECK (parent_id IS NULL OR parent_id <> category_id),
  INDEX ix_categories_parent_order (parent_id, sort_order, category_id)
) ENGINE=InnoDB;

INSERT INTO categories
  (category_id, category_name, parent_id, sort_order)
VALUES
  (1, '개발', NULL, 10),
  (2, '백엔드', 1, 10),
  (3, '프론트엔드', 1, 20),
  (4, 'Java', 2, 10),
  (5, 'Spring', 2, 20),
  (6, 'React', 3, 10),
  (7, '데이터베이스', 5, 10);

DELIMITER //
CREATE PROCEDURE move_category_adjacency(
  IN p_category_id BIGINT UNSIGNED,
  IN p_new_parent_id BIGINT UNSIGNED
)
procedure_body: BEGIN
  DECLARE v_category_count INT DEFAULT 0;
  DECLARE v_parent_count INT DEFAULT 0;
  DECLARE v_cycle_count INT DEFAULT 0;

  SELECT COUNT(*) INTO v_category_count
  FROM categories
  WHERE category_id = p_category_id;

  IF v_category_count = 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'category not found';
  END IF;

  IF p_new_parent_id IS NOT NULL THEN
    SELECT COUNT(*) INTO v_parent_count
    FROM categories
    WHERE category_id = p_new_parent_id;

    IF v_parent_count = 0 THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'new parent not found';
    END IF;

    WITH RECURSIVE subtree AS (
      SELECT category_id
      FROM categories
      WHERE category_id = p_category_id
      UNION ALL
      SELECT c.category_id
      FROM categories AS c
      JOIN subtree AS s ON c.parent_id = s.category_id
    )
    SELECT COUNT(*) INTO v_cycle_count
    FROM subtree
    WHERE category_id = p_new_parent_id;

    IF v_cycle_count > 0 THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'cycle-producing move';
    END IF;
  END IF;

  UPDATE categories
  SET parent_id = p_new_parent_id
  WHERE category_id = p_category_id;
END//
DELIMITER ;

categories inserted: 7
roots: 1
direct edges: 6

CALL move_category_adjacency(1, 7):
ERROR 1644 (45000): cycle-producing move

parent of category 1 after failure: NULL

CALL move_category_adjacency(1, 7);
-- ERROR 1644: cycle-producing move

SELECT category_id, category_name, parent_id
FROM categories
WHERE category_id = 1;

CALL move_category_adjacency(5, 3);

SELECT parent.category_name AS parent_name,
       child.category_id,
       child.category_name,
       child.sort_order
FROM categories AS parent
JOIN categories AS child
  ON child.parent_id = parent.category_id
WHERE parent.category_id IN (2, 3)
ORDER BY parent.category_id, child.sort_order, child.category_id;

SELECT COUNT(*) AS root_count
FROM categories
WHERE parent_id IS NULL;