Dear Ben,

> i have been using the following sql to try and get foreign keys
> working, the table creation works fine but when i try and delete data
> from the parent table it deletes as would normally happen in mysql,
> also the desired results do not happen if i use RESTRICT.
> is this a problem to do with indexes???
> 
> DROP TABLE IF EXISTS parent;
> CREATE TABLE parent(id INT NOT NULL, name char(5), PRIMARY KEY (id))
> TYPE=INNODB;
> DROP TABLE IF EXISTS child;
> CREATE TABLE child(id INT NOT NULL, parent_id INT, INDEX par_ind
> (parent_id), PRIMARY KEY (id),
>               FOREIGN KEY (parent_id) REFERENCES parent(id)
>               ON DELETE RESTRICT
> ) TYPE=INNODB;
> 
> insert into parent(name) values('asdfg');
> insert into child(parent_id) values(last_insert_id());

When you installed MySQL, did you follow the instructions in the manual 
to enable the innobb features? You can check by:

  ./mysqladmin variables | grep have_innodb

If you see this: sorry, it's fine and you need a better guru :)

  | have_innodb | YES |

If you see this: Read the doc snippets below and follow the 
  instructions in the MySQL manual.

  | have_innodb | DISABLED |

You can also verify the existance of the foreign keys by:

  mysql> show create table parent;

Snippet from the MySQL documentation:

  2.3.1 Quick Installation Overview

  If you want to have support for InnoDB tables, you should edit the
  /etc/my.cnf file and remove the # character before the parameter that
  starts with innodb_.... See section 4.1.2 `my.cnf' Option Files, and
  section 7.5.2 InnoDB Startup Options.

Unfortunately, MySQL silently accepts and ignores 'innodb' type tables 
when innodb is disabled. All the foreign key constraints are accepted 
and ignored too.

Good luck,

Alan.

---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Reply via email to