Is it possible to reference a different column in the same table?

Question

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.

Mark Byers · Accepted Answer · 2012-07-12T20:15:56.460

14

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

edited Jul 12 '12 at 20:15

answered Jul 12 '12 at 20:10

Mark Byers

811,555
193
1,581
1,452

So does that mean I should do something like: parent_id INTEGER REFERENCES (id) ? – Jim Johnson Jul 12 '12 at 20:10
@JimJohnson: You need to specify the table name. – Mark Byers Jul 12 '12 at 20:16

score 2 · Answer 2 · answered Jul 12 '12 at 20:12

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

answered Jul 12 '12 at 20:12

GarethD

68,045
10
83
123

1

I never knew that foreign keys could also be columns in the same table >. – Jim Johnson Jul 12 '12 at 20:14
Could I default parent_id to 0 by any chance? – Jim Johnson Jul 12 '12 at 20:15
2

@JimJohnson: Do you have a row 0 in your table? If not, your foreign key constraint will fail if you try to add a row with parent_id = 0. But you can use NULL instead. – Mark Byers Jul 12 '12 at 20:16
@Mark Byers I'll just use NULL instead :D – Jim Johnson Jul 12 '12 at 20:19

score 0 · Answer 3 · answered Jul 12 '12 at 20:12

0

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

answered Jul 12 '12 at 20:12

user1521913

1

Is it possible to reference a different column in the same table?

3 Answers3

Linked