I just looked at your table syntax. you've got two auto_increment pk columns. do you always have a 1:1 correspondence between the name and gene tables? would it not be better to have a gene_id column in name, put an index on it, and then issue:
"alter table name add foreign key(gene_id) references gene(id) on delete cascade;" In fact, I'm not sure you can actually create the constraint as you currently describe it .... "R.Dobson" wrote: > > Hi, yes, I should have included in the first mail. They are: > > 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. > > > > > > > > > > -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6 http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
