How to do this with foreign keys?

Question

I have a table called categories, with the following fields:

• id (primary key, identifies the categories)
• user_id (foreign key, users.id, the creator of the current category)
• category_id (foreign key, categories.id, the id of the parent category id)

Inside this table I have some records that are accessible to everyone.

For these records, both the user_id and the category_id fields are NULL.

In addition, users can create their own records (where the user_id field is not NULL), but every user can access only those that he has made himself.

Each record must meet these conditions:

• user_id values must be valid
• if the category_id is given then it must be a valid categories.id and that record must be created by the given user
• the value of the category_id can be a categoies.id where the user_id is NULL

How can I do this? I think I need more foreign keys for this, but not sure how to do it.

Some examples that may help you to understand my problem:

Lets say I have the following records inside the categories table:

+--------+-------------+-----------------+
|   id   |   user_id   |   category_id   |
+--------+-------------+-----------------+
|   1    |     NULL    |      NULL       |
+--------+-------------+-----------------+
|   2    |     NULL    |      NULL       |
+--------+-------------+-----------------+
|   3    |      1      |        1        |
+--------+-------------+-----------------+
|   4    |      2      |        1        |
+--------+-------------+-----------------+

and lets say that I want to insert these records:

+-------------+-----------------+----------------------------------------------------------------+
|   user_id   |   category_id   | is it insertable?                        
+-------------+-----------------+----------------------------------------------------------------+
|   1         |     NULL        | yes, because the value of the user_id is valid id              |
+-------------+-----------------+----------------------------------------------------------------+
|   1         |     1           | yes, because the record with id of 1 is created by NULL        |
+-------------+-----------------+----------------------------------------------------------------+
|   1         |     3           | yes, because the record with id of 3 is created by user #1     |
+-------------+-----------------+----------------------------------------------------------------+
|   1         |     4           | no, because the record with id of 4 is created by another user |
+-------------+-----------------+----------------------------------------------------------------+

Categories table:

CREATE TABLE `categories` (
  `id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL,
  `name` varchar(150) NOT NULL,
  `type` enum('income','expense') NOT NULL DEFAULT 'income',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `categories`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `category_id` (`category_id`,`user_id`,`name`),
  ADD KEY `user_id` (`user_id`);

ALTER TABLE `categories`
  ADD CONSTRAINT `categories_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `categories_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

Answer 1

Unfortunately, MySQL foreign keys can go so far. This is a bit of advanced logic. Might I recommend using a MySQL trigger?

DELIMITER $$
CREATE PROCEDURE `check_categories_user_id`(IN p_category_id INT(11), IN p_user_id INT(11))
BEGIN
    IF (p_category_id IS NOT NULL) THEN

        SET @other_user_id = (SELECT user_id
           FROM categories
           WHERE id = p_category_id);              

        IF (p_user_id <> @other_user_id) THEN
            SIGNAL SQLSTATE '45000'
                SET MESSAGE_TEXT = 'check constraint on categories.user_id failed';
        END IF;
   END IF;
END$$

CREATE TRIGGER `categories_before_update` BEFORE UPDATE ON `categories`
FOR EACH ROW
BEGIN
    CALL check_categories_user_id(new.category_id, new.user_id);
END$$


CREATE TRIGGER `categories_before_insert` BEFORE INSERT ON `categories`
FOR EACH ROW
BEGIN
    CALL check_categories_user_id(new.category_id, new.user_id);
END$$   
DELIMITER ;

See dbfiddle here. (If you remove the last INSERT query, the SELECT query works as expected)

Additionally, to avoid some overhead (The SELECT query in the trigger), you can just enforce NULL on user_id when category_id IS NOT NULL (Using triggers like above). If category_id is NOT NULL, you'll just fetch the user_id from the parent row (Or the root row (parent's parent row...etc), if nested).