How to know the on delete action of references table while adding foreign keys

Question

I am working on building new stuff on database that is already built by someone else and with little documentation. I need add a new table with many to many reference to a table already existing.

The following is the table already existing wp_posts:

+-----------------------+---------------------+------+-----+---------------------+----------------+
| Field                 | Type                | Null | Key | Default             | Extra          |
+-----------------------+---------------------+------+-----+---------------------+----------------+
| ID                    | bigint(20) unsigned | NO   | PRI | NULL                | auto_increment |
| post_author           | bigint(20) unsigned | NO   | MUL | 0                   |                |
| post_date             | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| guid                  | varchar(255)        | NO   |     |                     |                |
| menu_order            | int(11)             | NO   |     | 0                   |                |
| post_type             | varchar(20)         | NO   | MUL | post                |                |
| post_mime_type        | varchar(100)        | NO   |     |                     |                |
| comment_count         | bigint(20)          | NO   |     | 0                   |                |
| legacy_id             | int(11)             | YES  |     | NULL                |                |
+-----------------------+---------------------+------+-----+---------------------+----------------+

I need to create a table tag and tag_posts_reference which helps me for many to many reference tables. The Tag table is wp_tags:

+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| tag   | varchar(255)        | YES  |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+

I am having trouble in creating the table wp_tags_posts. Its is giving me the following error:

    mysql> create table wp_posts_tags( id bigint(20) unsigned auto_increment, 
    post_id bigint(20) unsigned not null, 
    tag_id bigint(20) unsigned not null, 
    primary key(id), index(post_id), 
    foreign key(post_id) references wp_posts(id) on Update cascade on delete restrict,
index(tag_id), 
    foreign key(tag_id) references wp_tags(id)) 
    ENGINE = INNODB;
    ERROR 1005 (HY000): Can't create table 'openexhibits_dev.wp_posts_tags' (errno: 150)

I think there is some delete on cascade actions I am supposed to add. I added few and tried by trial and error but none of them work. Is there a way I can find delete cascade action that I am supposed to use while creating the foreign key reference? Is there any command like dec table_name which can show me those actions? I tried on delete no action and on delete set null etc but I dont want to do something by trial and error blindly especially while dealing with relations between tables. Can anyone help me? Thank you very much.

Answer 1

This works in MySQL 5.5. The fact that the target table is a Wordpress table shouldn't affect foreign key references as far as I know.

create table wp_posts (
  ID bigint(20) unsigned,
  post_author bigint(20) unsigned,
  post_date datetime,
  guid varchar(255),
  menu_order int(11),
  post_type varchar(20),
  post_mime_type varchar(100),
  comment_count bigint(20),
  legacy_id int(11),
  primary key (ID)
) engine = INNODB;

create table wp_tags (
  id bigint unsigned,
  tag varchar(25),
  primary key (id)
) engine = INNODB;

create table wp_tags_posts(
  id bigint(20) unsigned auto_increment,  
  tag_id bigint(20) unsigned, 
  post_id bigint(20) unsigned, 
  primary key(id), 
  foreign key(tag_id) references wp_tags(id), 
  foreign key(post_id) references wp_posts(ID)
) engine = INNODB;

Answer 2

which engine is your default engine, use InnoDB engine for a table with foreign keys.

mysql> create table wp_tags_posts(id bigint(20) unsigned auto_increment,
tag_id bigint(20) unsigned, post_id bigint(20) unsigned, primary key(id), foreign key(tag_id) references wp_tags(id), foreign key(post_id) references wp_posts(ID))engine=InnoDB;