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 |
> >>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.
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >
> >
> >
>
> --
>
> 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]
--
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]
