一个可能为 NULL 值的属性如何仍然可以引用 MySQL 中的另一个属性

发布于 2024-12-06 06:12:58 字数 323 浏览 0 评论 0原文

想象一下描述文件系统中文件夹的 MySQL 表：

folder_id INT,
parent_folder_id INT,
folder_name VARCHAR(64)

我想添加一个约束“parent_folder_id REFERENCESfolder_id”以确保数据库中不存在死链接。

但如果文件夹位于顶层，则没有父文件夹，因此它应该为 NULL。据我了解，约束不允许我插入parent_folder_id = NULL 的元组。

如何正确设计呢？

原文

Imagine MySQL table describing a folders in filesystem:

folder_id INT,
parent_folder_id INT,
folder_name VARCHAR(64)

And I want to add a constraint 'parent_folder_id REFERENCES folder_id' to be sure that no dead links are there in the DB.

But in case folder is in the top-level there is no parent folder, so it should be NULL. As far as I understand, constraint won't let me insert tuple with parent_folder_id = NULL.

How to design it properly?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

无言温柔 2024-12-13 06:12:58

您只需添加外键即可：

CREATE TABLE `folder` (
    `folder_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `parent_folder_id` INTEGER UNSIGNED,
    `folder_name ` VARCHAR(64) NOT NULL,
    CONSTRAINT `PK_folder` PRIMARY KEY (`folder_id`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `folder` ADD CONSTRAINT `FK_folder_parent_folder` 
    FOREIGN KEY (`parent_folder_id`) REFERENCES `folder` (`folder_id`) 
    ON DELETE CASCADE ON UPDATE CASCADE;

当 parent_folder_id 为 NULL 时，外键约束不会生效。

You can just add a foreign key:

CREATE TABLE `folder` (
    `folder_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `parent_folder_id` INTEGER UNSIGNED,
    `folder_name ` VARCHAR(64) NOT NULL,
    CONSTRAINT `PK_folder` PRIMARY KEY (`folder_id`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `folder` ADD CONSTRAINT `FK_folder_parent_folder` 
    FOREIGN KEY (`parent_folder_id`) REFERENCES `folder` (`folder_id`) 
    ON DELETE CASCADE ON UPDATE CASCADE;

The foreign key constraint won't kick in when parent_folder_id is NULL.

回复收藏 0 原文

祁梦 2024-12-13 06:12:58

我认为您最好使用两个触发器（一个用于插入，一个用于更新）检查表中的 folder_id 列中是否存在 parent_folder_id ；如果没有引发错误或放弃插入/更新操作。
对于根文件夹，分配 parent_folder_id = 0 并在触发器中使用 CASE 语句使其有效。

编辑：使用它作为伪代码

CREATE TRIGGER trig_ins BEFORE INSERT ON table
  FOR EACH ROW BEGIN
    IF (NEW.parent_folder_id > 0) THEN
      IF (SELECT COUNT(folder_id) FROM table 
          WHERE folder_id = NEW.parent_folder_id) = 0 THEN
              raise_an_error_here
      END
    END
  END

I think you'd better using two triggers (one on insert and one on update) checking that parent_folder_id exists in your table in folder_id column; if not raise an error or discard the insert/update operation.
For root folder assign parent_folder_id = 0 and use a CASE statement in your trigger to make this valid.

EDITED: use this as pseudo-code

CREATE TRIGGER trig_ins BEFORE INSERT ON table
  FOR EACH ROW BEGIN
    IF (NEW.parent_folder_id > 0) THEN
      IF (SELECT COUNT(folder_id) FROM table 
          WHERE folder_id = NEW.parent_folder_id) = 0 THEN
              raise_an_error_here
      END
    END
  END

回复收藏 0 原文