"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.
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 inc
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;
No
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,
"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=e
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 =
Hi, yes, I should have included in the first mail. They are:
mysql> show table status like 'gene%';
+--+++--++-+-+--+---++-+-+++-
"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)
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
