我有一张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;
1条答案
按热度按时间ergxz8rk1#
不幸的是,mysql外键可以走这么远。这是一种先进的逻辑。我可以推荐使用mysql触发器吗?
看这里的小提琴(如果删除最后一个insert查询,则select查询按预期工作)
另外,为了避免一些开销(触发器中的select查询),当category\u id不为null时,您可以对user\u id强制null(使用上面的触发器)。如果category\u id不为null,则只需从父行(或根行(父行的父行…等,如果嵌套))中获取用户\u id。