"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. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
