MySQL many-to-many schema setup

I'm setting up a mysql db with posts and tags that looks like this:

posts    
+-------------+--------------+------+-----+-------------------+----------------+
| Field       | Ty开发者_StackOverflowpe         | Null | Key | Default           | Extra          |
+-------------+--------------+------+-----+-------------------+----------------+
| id          | int(11)      | NO   | PRI | NULL              | auto_increment |
[...]

tags
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| tag   | varchar(255) | NO   | UNI | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

post_tag_map
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| post_id    | int(11) | NO   | PRI | NULL    |       |
| tag_id     | int(11) | NO   | PRI | NULL    |       |
+------------+---------+------+-----+---------+-------+

The tags will be shared between multiple posts; 'red' may be used by post 5 and 10.

My question is: how do I prevent the deletion of a tag if it is being used by more than one post and delete it if it isn't?

Note: I am using Foreign Keys which I thought would take care of this issue but it doesn't seem to be 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 all tables in one go using a delete statement like this.

DELETE FROM post_tag_map, posts, tags
WHERE post.id = post_tag_map.post_id
AND tags.id = post_tag_map.tag_id
AND tags.id = 256;

However MySQL makes no guarantees about the order that the deletes will take place in. If you have foreign keys, those may prevent the delete from taking place.

So either do not use FOREIGN KEY **or** declare them with aON DELETE CASCADE` clause.

Remember MyISAM does not support foreign keys, so there you only have the multitable delete.

More about multitable deletes here: http://dev.mysql.com/doc/refman/5.1/en/delete.html

You'd want to declare the foreign keys like this:

FOREIGN KEY (post_id) REFERENCES posts(id)
   ON DELETE CASCADE,
FOREIGN KEY (tag_id) REFERENCES tag(id)
   ON DELETE CASCADE

the 'on delete cascade' is what will initiate the auto-delete. Note that the cascade will NOT propagate "up" the other side of the link table. If you delete a tag, only the matching records in post_tag_map will vanish, but leave the posts they were attached to alone.

You might need to add to your FOREIGN KEY declarations:

http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html