i'm setting mysql db posts , tags looks this:
posts +-------------+--------------+------+-----+-------------------+----------------+ | field | type | null | key | default | | +-------------+--------------+------+-----+-------------------+----------------+ | id | int(11) | no | pri | null | auto_increment | [...] tags +-------+--------------+------+-----+---------+----------------+ | field | type | null | key | default | | +-------+--------------+------+-----+---------+----------------+ | id | int(11) | no | pri | null | auto_increment | | tag | varchar(255) | no | uni | null | | +-------+--------------+------+-----+---------+----------------+ post_tag_map +------------+---------+------+-----+---------+-------+ | field | type | null | key | default | | +------------+---------+------+-----+---------+-------+ | post_id | int(11) | no | pri | null | | | tag_id | int(11) | no | pri | null | | +------------+---------+------+-----+---------+-------+ the tags shared between multiple posts; 'red' may used post 5 , 10.
my question is: how prevent deletion of tag if being used more 1 post , delete if isn't?
note: using foreign keys thought take care of issue doesn't seem working:
create table `post_tag_map` ( `post_id` int(11) not null, `tag_id` int(11) not null, primary key (`post_id`,`tag_id`), foreign key (`post_id`) references posts(`id`), foreign key (`tag_id`) references tag(`id`) )
you can delete tables in 1 go using delete statement this.
delete post_tag_map, posts, tags post.id = post_tag_map.post_id , tags.id = post_tag_map.tag_id , tags.id = 256; however mysql makes no guarantees order deletes take place in. if have foreign keys, may prevent delete taking place.
so either not use foreign key **or** declare them aon delete cascade` clause.
remember myisam not support foreign keys, there have multitable delete.
more multitable deletes here: http://dev.mysql.com/doc/refman/5.1/en/delete.html
Comments
Post a Comment