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?
TIA
Rich
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
