如何使用外键？_大数据知识库

我有一张table叫 categories ，包含以下字段：
id（主键，标识类别）
用户id（外键， users.id ，当前类别的创建者）
类别id（外键， categories.id ，父类别id的id）
在这个表中，我有一些记录，每个人都可以访问。
对于这些记录 user_id 以及 category_id 字段是 NULL .
此外，用户还可以创建自己的记录（如果 user_id 字段不是 NULL )，但每个用户只能访问他自己制作的内容。
每个记录必须满足以下条件： user_id 值必须有效
如果 category_id 则它必须是有效的 categories.id 并且该记录必须由给定的用户创建
价值 category_id 可以是一个 categoies.id 在哪里 user_id 是 NULL 我该怎么做？我想我需要更多的外键，但不知道怎么做。
一些例子可以帮助你理解我的问题：
假设我在 categories 表格：

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

假设我想插入这些记录：

+-------------+-----------------+----------------------------------------------------------------+
|   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 |
+-------------+-----------------+----------------------------------------------------------------+

类别表：

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;

不幸的是，mysql外键可以走这么远。这是一种先进的逻辑。我可以推荐使用mysql触发器吗？

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 ;

看这里的小提琴(如果删除最后一个insert查询，则select查询按预期工作）
另外，为了避免一些开销（触发器中的select查询），当category\u id不为null时，您可以对user\u id强制null（使用上面的触发器）。如果category\u id不为null，则只需从父行（或根行（父行的父行…等，如果嵌套））中获取用户\u id。

如何使用外键？

1条答案

相关问题

热门标签

最新问答