how do I add a recursive foreign key to a table?

Question

I have a table like:

table: comments, with rows: id, author, content, replyto

I would like 'replyto' to reference 'id' in the same table, how would I do that?

Many thanks.

Answer 1

Something like:

ALTER TABLE comments ADD CONSTRAINT `my_recursive_constraint` FOREIGN KEY (`reply_to`) REFERENCES `comments` (`id`) 

Answer 2

alter table $table_name add foreign key references $table_name(id);

see this http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html (search for "self referential")

and this http://www.codeproject.com/KB/database/Trees_in_SQL_databases.aspx