外键或空值 [英] Foreign key or null value

问题描述

我有2个表:pages和menu

我希望有一个指向菜单表的指针作为pages.id的外键. 问题在于某些菜单行没有页面链接. 当有人单击链接时，将打开一个子菜单. 我如何在phpmyadmin中做到这一点?

I want to have a pointer into menu table as foreign key to pages.id. The problem is that some menu rows don't have a link to page. When someone clicks in the link opens a submenu. How i do this in phpmyadmin?

我想要的比赛是1比1或1比0

The match i want is 1 to 1 or 1 to 0

谢谢

也许如果我对具有id = some_id和pages.body = null的页面有一行 并且我想要没有子菜单的所有菜单都将具有menu.pages_id = some_id 这是我想要的正确方法吗?

Maybe if i have a row to pages that has id=some_id with pages.body=null and all the menus that i want to have no submenu would have menu.pages_id=some_id Is this the right way to do that i want?

推荐答案

为menu.pages_id使用一些不可思议的值不起作用，因为该值无论在pages表的某行上都必须存在.

Using some magic value for menu.pages_id doesn't work because that value whatever it is must exist on some row in the pages table.

执行此操作的正确方法是使menu.pages_id接受NULL.即使列是UNIQUE约束和FOREIGN KEY约束的一部分，也可以将其设为空，这是合法的.

The right way to do this is to make menu.pages_id accept NULL. It's legal for a column to be nullable even if it's part of a UNIQUE constraint and a FOREIGN KEY constraint.

CREATE TABLE menu (
  ...
  pages_id INT NULL,
  UNIQUE KEY (pages_id),
  FOREIGN KEY (pages_id) REFERENCES pages(pages_id)
) ENGINE=InnoDB;

这篇关于外键或空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！