[eluser]Twisted1919[/eluser]
Hi , i have the following table schema .
Code: CREATE TABLE IF NOT EXISTS `mdb`.`categories` (
`category_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`parent_id` INT UNSIGNED NOT NULL DEFAULT 0 ,
`name` VARCHAR(50) NULL ,
`description` TEXT NULL ,
`nicename` VARCHAR(50) NULL ,
`url` VARCHAR(100) NULL ,
`sort_order` INT(5) UNSIGNED NOT NULL DEFAULT 0 ,
PRIMARY KEY (`category_id`) ,
INDEX `parent_id` (`parent_id` ASC) )
ENGINE = InnoDB;
Right now a new created category will have parent_id set to 0 , meaning that has no parents and if i create another sub-category, this will get the parent_id of the parent category .
My problem is that i want to represent in this table a relationship of one-to-many , i mean a category can have multiple sub-categories and a sub-category belongs only to a category.
Making this reference , deleting a category having subcategories will delete the subcategories too.
The issue is that when i create the reference between the two , i must always supply a valid parent_id, not 0 or null , and i don't want this , i just want to be able to let the parent id to be optional (0 or null) and on delete , if a category has childs , delete the childs too , if not delete only the category .
How can i achieve this ?
[eluser]weboide[/eluser]
I believe that for a foreign key to be optional, it has to be possible to set it to NULL (ie without NOT NULL).
Try with parent_id like this:
Code: `parent_id` int(10) unsigned DEFAULT NULL
If there is no parent, just set parent_id to NULL or do not set it (as it is by default set to NULL)
[eluser]Twisted1919[/eluser]
Thank you for your reply .
I tried this way , but , somehow the engine looks for a correspondent category to assign that parent_id and results in an error even if i set it to null .
Any other thoughts ?
[eluser]weboide[/eluser]
What is the error?
[eluser]InsiteFX[/eluser]
Code: -- --------------------------------------------------------
--
-- Table structure for table `categories`
--
CREATE TABLE IF NOT EXISTS `categories` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`short_desc` varchar(255) NOT NULL default '',
`long_desc` text NOT NULL,
`status` enum('active', 'inactive') NOT NULL default 'active',
`sort_order` int(3) NOT NULL default '0',
`parent_id` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;
--
-- Dumping data for table `categories`
--
INSERT INTO `categories` VALUES (1, 'Cat-1', '', '', 'active', 0, 0);
INSERT INTO `categories` VALUES (2, 'Cat-2', '', '', 'active', 0, 0);
INSERT INTO `categories` VALUES (3, 'Cat-3', '', '', 'active', 0, 0);
-- Next comes the posts table:
-- --------------------------------------------------------
--
-- Table structure for table `posts`
--
CREATE TABLE IF NOT EXISTS `posts` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(255) NOT NULL,
`tags` varchar(255) NOT NULL,
`status` enum('draft', 'published') NOT NULL,
`body` text NOT NULL,
`category_id` int(11) NOT NULL,
`pub_date` datetime NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;
--
-- Dumping data for table `posts`
--
INSERT INTO `posts` VALUES (1, 'Home', 'home', 'published', 'This is just a test of the home page.\n\nThanks for your patience.', 3, '2008-07-13 15:23:26', 1);
INSERT INTO `posts` VALUES (1, 'Testing', 'test', 'published', 'This is just a test.\n\nThanks for your patience.', 3, '2008-07-13 15:23:26', 1);
INSERT INTO `posts` VALUES (2, 'Another test', 'test', 'published', 'Another test.\r\n\r\nWho knew this could be such fun?', 2, '2008-07-13 15:23:26', 1);
INSERT INTO `posts` VALUES (3, 'Just Another Test, part 3', 'testing testing', 'published', 'just another test!', 1, '2008-07-13 15:58:52', 1);
-- Next, the comments table:
-- --------------------------------------------------------
--
-- Table structure for table `comments`
--
CREATE TABLE IF NOT EXISTS `comments` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`body` varchar(255) NOT NULL,
`post_id` int(11) NOT NULL,
`pub_date` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;
--
-- Dumping data for table `comments`
--
INSERT INTO `comments` VALUES (1, 'InsiteFX', '[email protected]', 'This is a comment.', 1, '2010-03-13 15:26:05');
Enjkoy
InsiteFX
[eluser]Twisted1919[/eluser]
Thanks InsiteFX , but that doesn't help , it has no foreign keys between tables , you missed my point a little bit .
@weboide - the error says that a foreign key fails on insert , which is obvious because the parent id has the id set to 0 or null , and there is no parent category having that id , so the engine doesn't find it and throws the error .
A solution would be to create a default category with id of 0 , but it's not something nice .
Another solution would be to move the relationship between parent / child into another table and create a many-to-many relationship but i don't need it.
And yet the easiest solution is just to set the parent_id as a index and don't set and relationship between parent_id and category_id and on parent category delete, i would delete the child categories from php rather than let mysql internal process handle this .
I don't have other ideas , so probably i'll stick with the easiest solution .
If you have something to add , let me know .
Thanks .
[eluser]InsiteFX[/eluser]
Well can you not check the table with a get_record to see if it has 0 records
and if so skip the record insert.
Code: $query = $this->db->get('mytable');
if ($query->num_rows() == 0)
{
// no records found.
return FALSE;
}
$this->db->insert('mytable', $data);
Or you could just write a check_record() method to see if the table
contains any rows or not.
Enjoy
InsiteFX
[eluser]weboide[/eluser]
I have a project here where I have this foreign key (I changed the names to be more relevant):
Table "Car":
Code: `carinfo_id` int(11) DEFAULT NULL
...
KEY `fk_car_carinfo1` (`carinfo_id`),
CONSTRAINT `fk_car_carinfo1` FOREIGN KEY (`carinfo_id`) REFERENCES `carinfo` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
And carinfo_id can be set to NULL where there is no carinfo related to that car. And also you can see that ON DELETE, it is also set to NULL.
Is that what you are trying to achieve?
[eluser]Twisted1919[/eluser]
Perfect weboide , this is exacly what i need .
Thank you very much for your time .
@ InsiteFX, I know i could do that way , BUT i want mysql to do that job for me because is an internal process therefore is faster .
[eluser]weboide[/eluser]
Glad I could help!
|
