MySQL Forums
Forum List  »  InnoDB

add foreign key that ref's column in same table
Posted by: Don Ireland
Date: January 01, 2016 11:11AM

I'm trying to add a foreign key that references the primary id in the same table.

But when I do so, all I get is a message that " #1215 - Cannot add foreign key constraint".

This is my table definition and the code I'm trying to use to add the fk is below that.

CREATE TABLE IF NOT EXISTS `categories` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(48) NOT NULL,
`userID` smallint(5) unsigned NOT NULL,
`type` enum('income','expense') NOT NULL,
`parentID` bigint(20) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=102 ;


ALTER TABLE `categories`
ADD KEY `fkParentID` (`parentID`),
ADD CONSTRAINT `fkParentID` FOREIGN KEY (`parentID`)
REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

Don

Options: ReplyQuote


Subject
Views
Written By
Posted
add foreign key that ref's column in same table
5033
January 01, 2016 11:11AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.