Yes, you can use exactly this structure.
There are two things to take into account when you need foreign key constraints:
1) both referencing and referenced table need to be InnoDB tables (see
http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html)
2) you cannot declare a foreign key as a column constraint; you must do it as a table constraint (http://bugs.mysql.com/bug.php?id=17943 and
http://bugs.mysql.com/bug.php?id=4919).
create table hierarchy_tbl (
id int unsigned primary key
, parent_id int unsigned
, data varchar(30)
, constraint fk_hierarchy
foreign key (
parent_id
)references hierarchy_tbl (
id
)
)
ENGINE = InnoDB
;
(some general things: MySQL does not have number or varchar2 datatypes, see:
http://dev.mysql.com/doc/refman/5.0/en/data-types.html)
However, having said all this, you really should consider not using this structure to model hierarchies. MySQL does not support the CONNECT BY syntax, making it next to impossible to query trees of an arbitrary depth. Even in Oracle, this structure is not nice to query as the CONNECT BY is implemented recursively, which leads to quite poor performance. Instead, check out the nested set model. See
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html for a good comparison of both methods and more links to other backgroun articles.