CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE cascade
) TYPE=INNODB;Now, insert a couple of lies of data:
mysql> insert into parent values(1); Query OK, 1 row affected (0.00 sec)
mysql> insert into parent values(2); Query OK, 1 row affected (0.00 sec)
mysql> insert into parent values(3); Query OK, 1 row affected (0.00 sec)
mysql> insert into child values(1,1); Query OK, 1 row affected (0.01 sec)
mysql> insert into child values(2,2); Query OK, 1 row affected (0.00 sec)
mysql> insert into child values(3,3); Query OK, 1 row affected (0.00 sec)
mysql> select * from child; +------+-----------+ | id | parent_id | +------+-----------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | +------+-----------+ 3 rows in set (0.00 sec)
mysql> select * from parent; +----+ | id | +----+ | 1 | | 2 | | 3 | +----+ 3 rows in set (0.00 sec)
When I come to try to delete some data from the parent table i'm getting errors as in:
mysql> delete from parent where id=1; ERROR 1217: Cannot delete a parent row: a foreign key constraint fails
any thoughts?
cheers,tia r
Jeff Mathis wrote:
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.Check if table types are InnoDB with SHOW CREATE TABLE or SHOW TABLE STATUS statements.
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?
--
Richard Dobson MRC Bright Study Clinical Pharmacolgy St Bartholomew's and the Royal London School of Medicine and Dentistry Charterhouse Square London EC1M 6BQ Tel: 020-7882-5670 Mob: 07711-522926 Fax: 020-7882-5672 [EMAIL PROTECTED]
