Hierarchical data - How to make a self to self relationship in Mysql

Questions

I am using Heidi and I have this Table:

Table Category:

• id
• title
• parent_id

The field parent_id is a reference of id. This way I can do a unlimited Hierarchy of category and sub-category. How do I create this relationship in mysql Syntax?

Answers

If you mean how do you create the foreign key constraint, you just need to do something like this:

CREATE TABLE `EXAMPLE` (
    `ID` int(11) NOT NULL,
    `TITLE` varchar(255) NOT NULL,
    `PARENT_ID` int(11) DEFAULT NULL,
    PRIMARY KEY (`ID`),
    KEY `PARENT_ID` (`PARENT_ID`),
    CONSTRAINT `PARENT` FOREIGN KEY (`PARENT_ID`) REFERENCES `EXAMPLE` (`ID`)
);

Source

License : cc by-sa 3.0

http://stackoverflow.com/questions/8716510/how-to-make-a-self-to-self-relationship-in-mysql

Related