是否可以在同一张表中引用其他列？ [英] Is it possible to reference a different column in the same table?

问题描述

如果博客具有类别表，例如：

If a blog has a 'categories' table such as the following:

CREATE TABLE categories
(
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  parent_id INTEGER NOT NULL,
  name VARCHAR(30) NOT NULL,
  description TEXT,
  count INTEGER NOT NULL DEFAULT 0
);

如果parent_id字段旨在引用类别表的'id'字段，则我如何添加约束以确保插入parent_id的值引用id字段？

And if the parent_id field is intended to refer to the 'id' field of the categories table, then how could I add a constraint that would ensure that values inserted into parent_id references the id field?

我只是想确保只有存在的类别ID值可以用作新插入类别的父项。

I simply want to make sure that only category id values that exist can be used as a parent of a newly inserted category.

推荐答案

是的，您可以引用同一表中的列。

Yes, you can reference a column in the same table.

但是该列应该为空，否则您将无法插入第一条记录。

But that column should be nullable otherwise you can't insert the first record.

CREATE TABLE categories
(
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  parent_id INTEGER NULL,
  name VARCHAR(30) NOT NULL,
  description TEXT,
  count INTEGER NOT NULL DEFAULT 0,
  FOREIGN KEY (parent_id) REFERENCES categories(id)
);

请注意，在REFERENCES关键字之后，表名不是可选的，因此即使您必须引用同一表中的一列。从文档：

Note that after the REFERENCES keyword the table name is not optional, so you must specify it even if you are referencing a column in the same table. From the documentation:

reference_definition:
    REFERENCES tbl_name (index_col_name,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE reference_option]
      [ON UPDATE reference_option]

查看在线工作： sqlfiddle

这篇关于是否可以在同一张表中引用其他列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！