Re: innodb on delete cascade
R.Dobson [EMAIL PROTECTED] wrote: mmm, i've just tried the example within the mysql docs: 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? Worked perfect for me: mysql delete from parent where id=1; Query OK, 1 row affected (0.04 sec) mysql select * from child; +--+---+ | id | parent_id | +--+---+ |2 | 2 | |3 | 3 | +--+---+ 2 rows in set (0.02 sec) mysql select * from parent; ++ | id | ++ | 2 | | 3 | ++ 2 rows in set (0.00 sec) What version of MySQL do you use? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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]
Re: innodb on delete cascade
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) mysqlalter 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 |
Re: innodb on delete cascade
R.Dobson [EMAIL PROTECTED] wrote: Hi, i'm using Distrib 4.0.1-alpha, for sun-solaris2.8 (sparc) It's an outdated version and it doesn't support ON DELETE, just parsed. Upgraid MySQL to the recent version (4.0.14) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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]
Re: innodb on delete cascade
Hi, i'm using Distrib 4.0.1-alpha, for sun-solaris2.8 (sparc) Victoria Reznichenko wrote: R.Dobson [EMAIL PROTECTED] wrote: mmm, i've just tried the example within the mysql docs: 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? Worked perfect for me: mysql delete from parent where id=1; Query OK, 1 row affected (0.04 sec) mysql select * from child; +--+---+ | id | parent_id | +--+---+ |2 | 2 | |3 | 3 | +--+---+ 2 rows in set (0.02 sec) mysql select * from parent; ++ | id | ++ | 2 | | 3 | ++ 2 rows in set (0.00 sec) What version of MySQL do you use?
innodb on delete cascade
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) mysqlalter 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]
Re: innodb on delete cascade
mmm, i've just tried the example within the mysql docs: 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. 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
Re: innodb on delete cascade
I'm using mysql-max 4.04. This works. drop table if exists Parent; create table Parent ( id int not null auto_increment primary key ) type = InnoDB; drop table if exists Child; create table Child ( id int not null auto_increment primary key, parent_id int not null ) type = InnoDB; alter table Child add index(parent_id); alter table Child add constraint foreign key(parent_id) references Parent(id) on delete cascade; mysql insert into Parent values(1); Query OK, 1 row affected (0.04 sec) mysql insert into Parent values(2); Query OK, 1 row affected (0.04 sec) mysql insert into Parent values(3); Query OK, 1 row affected (0.03 sec) mysql select * from Parent; ++ | id | ++ | 1 | | 2 | | 3 | ++ 3 rows in set (0.00 sec) mysql insert into Child (parent_id) values (1); Query OK, 1 row affected (0.04 sec) mysql insert into Child (parent_id) values (2); Query OK, 1 row affected (0.04 sec) mysql insert into Child (parent_id) values (3); Query OK, 1 row affected (0.11 sec) mysql select * from Child; ++---+ | id | parent_id | ++---+ | 2 | 1 | | 3 | 2 | | 4 | 3 | ++---+ 3 rows in set (0.00 sec) mysql delete from Parent where id = 1; Query OK, 1 row affected (0.04 sec) mysql select * from Child; ++---+ | id | parent_id | ++---+ | 3 | 2 | | 4 | 3 | ++---+ 2 rows in set (0.00 sec) you see the cascade. the only thing I can suggest is have a look at your msql version, or use the syntax I have above... good luck R.Dobson wrote: mmm, i've just tried the example within the mysql docs: 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
Re: innodb on delete cascade
we're using a slightly different syntax. add the word constraint in front of foreign key. alter table name add constraint foreign key(id) references gene(id) on delete cascade; we rely on cascading deletes. This works in our case. good luck. 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) mysqlalter 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;
Re: innodb on delete cascade
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) mysqlalter 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]
Re: innodb on delete cascade
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) mysqlalter table name add foreign key(id) references gene(id) on delete cascade; mysql select * from gene; ++--+-+ | id | name | species |
