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?
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');
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments