Is it possible to reference a different column in

问题:

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
);

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?

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

回答1:

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)
);

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]

See it working online: sqlfiddle

回答2:

Just use a normal foreign key:

ALTER TABLE categories ADD CONSTRAINT FK_categories_Parent_ID 
REFERENCES categories (ID)

However Parent_ID should be nullable as you'll never be able to insert a record

回答3:

You can use below link. It has how you can do it Oracle database.

http://www.adp-gmbh.ch/ora/data_samples/hierarchic_yahoo.html

Thanks