mysql> show table status like 'gene%';
+------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+----------------+-----------------------+
| Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment |
+------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+----------------+-----------------------+
| gene | InnoDB | Dynamic | 0 | 0 | 16384 | NULL | 0 | 0 | 1 | NULL | NULL | NULL | | InnoDB free: 55296 kB |
+------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+----------------+-----------------------+
1 row in set (0.00 sec)
mysql> show table status like 'name%';
+------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+----------------+-----------------------+
| Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment |
+------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+----------------+-----------------------+
| name | InnoDB | Dynamic | 1 | 16384 | 16384 | NULL | 0 | 0 | 10 | NULL | NULL | NULL | | InnoDB free: 55296 kB |
+------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+----------------+-----------------------+
1 row in set (0.00 sec)
cheers Rich
Egor Egorov wrote:
"R.Dobson" <[EMAIL PROTECTED]> wrote:
Hi, I have a db where I have converted all the tables innodb. I have 2 tables in particular called gene and name.
They both have a primary key call id. I want the primary key from name to be deleted when the corresponding key is deleted from gene.
It doesn't seem to be happening as yet!
show columns from name; +---------------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------------+-----------------------+------+-----+---------+----------------+ | id | mediumint(8) unsigned | | PRI | NULL | auto_increment | | other_name | varchar(100) | | | | | | other_symbol | varchar(100) | | | | | | refseq_ID | varchar(20) | YES | | NULL | | | GO | varchar(20) | YES | | NULL | | | locus_link | varchar(20) | YES | | NULL | | | other_species_index | varchar(20) | YES | | NULL | | +---------------------+-----------------------+------+-----+---------+----------------+ 7 rows in set (0.00 sec)
mysql> show columns from gene; +---------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-----------------------+------+-----+---------+----------------+ | id | mediumint(8) unsigned | | PRI | NULL | auto_increment | | name | varchar(100) | | | | | | species | varchar(100) | | | | | +---------+-----------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
mysql>alter table name add foreign key(id) references gene(id) on delete cascade;
mysql> select * from gene; +----+------+---------+ | id | name | species | +----+------+---------+ | 9 | hi | human | +----+------+---------+ 1 row in set (0.00 sec)
mysql> select * from name; +----+------------+--------------+-----------+------+------------+---------------------+ | id | other_name | other_symbol | refseq_ID | GO | locus_link | other_species_index | +----+------------+--------------+-----------+------+------------+---------------------+ | 9 | hi | human | i | i | i | i | +----+------------+--------------+-----------+------+------------+---------------------+ 1 row in set (0.00 sec)
mysql> delete from gene where id=9; Query OK, 1 row affected (0.00 sec)
mysql> select * from name; +----+------------+--------------+-----------+------+------------+---------------------+ | id | other_name | other_symbol | refseq_ID | GO | locus_link | other_species_index | +----+------------+--------------+-----------+------+------------+---------------------+ | 9 | hi | human | i | i | i | i | +----+------------+--------------+-----------+------+------------+---------------------+ 1 row in set (0.00 sec)
mysql> select * from gene; Empty set (0.00 sec)
The entry from name should be deleted as well?
Check if table types are InnoDB with SHOW CREATE TABLE or SHOW TABLE STATUS statements.
