CODEWITHSUNDEEP
mysqlsql
user11594895
user11594895

Reputation:

Using foreign key in the same table

I created a table inside my database named categories:

CREATE TABLE IF NOT EXISTS `category` 
( `categoryName` varchar(128)  ,
 `subcategoryName` varchar(128) ,
 PRIMARY KEY (`categoryName`) )
 ENGINE=InnoDB DEFAULT CHARSET=latin1

A category in this table may be a subcategory of another category in this field.So,subcategory is a FK to categories.Initialiasiation:

    ALTER TABLE `category` ADD FOREIGN KEY (`subcategoryName`) 
REFERENCES `category`(`categoryName`) ON DELETE RESTRICT ON UPDATE RESTRICT;

So i tried inserting values in this table like this:

INSERT INTO `category` (`categoryName`, `subcategoryName`) VALUES
( 'Literature','Satire'),
( 'Science','Mathematics'),
( 'Science','Physics');

but i get this error:

#1452 - Cannot add or update a child row: a foreign key constraint fails (`php_beginner_crud_level_1`.`category`, CONSTRAINT `category_ibfk_1` FOREIGN KEY (`subcategoryName`) REFERENCES `category` (`categoryName`))

I think this means that i can't isent a subcategory if the category does not already exist.So what i did is insert first single categories:

INSERT INTO `category` (`categoryName`) VALUES
( 'Literature'),
( 'Mathematics'),
( 'Programming');

Hw can i insert subcategories for these categories now? for example if i want a subcategory: Java for Programming how can i insert it into the table?

Upvotes: 5

Views: 221

Answers (1)

Barmar
Barmar

Reputation: 780724

You have the foreign key relationship backwards. Subcategories reference categories, so it should be:

ALTER TABLE `category` ADD FOREIGN KEY (`categoryName`) 
REFERENCES `category`(`subcategoryName`) ON DELETE RESTRICT ON UPDATE RESTRICT;

And the primary key should be subcategoryName, not categoryName.

You need to create subcategories with no category for the top-level categories in the hierarchy.

INSERT INTO `category` (`categoryName`, `subcategoryName`) VALUES
(NULL, 'Literature'),
(NULL, 'Science'),
( 'Literature','Satire'),
( 'Science','Mathematics'),
( 'Physics','Science');

Or rename things to make more sense.

CREATE TABLE IF NOT EXISTS `category` 
( `categoryName` varchar(128)  NOT NULL,
 `supercategoryName` varchar(128) ,
 PRIMARY KEY (`categoryName`) )
 ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `category` ADD FOREIGN KEY (`supercategoryName`) 
REFERENCES `category`(`categoryName`) ON DELETE RESTRICT ON UPDATE RESTRICT;

INSERT INTO `category` (`categoryName`, `subcategoryName`) VALUES
(NULL, 'Literature'),
(NULL, 'Science'),
('Literature', NULL),
('Science', NULL),
( 'Satire','Literature'),
( 'Mathematics','Science'),
( 'Science'',Physics');

Upvotes: 3

Related Questions