Re: cannot get foreign keys to work
At 18:45 +1100 1/10/03, Ben Mooney wrote:
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???
In a sense, yes. ON DELETE RESTRICT isn't implemented, I believe.
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());
system
os x 10.2.3
mysql 3.23.53-entropy.ch
-
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
cannot get foreign keys to work
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());
system
os x 10.2.3
mysql 3.23.53-entropy.ch
-
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
Re: cannot get foreign keys to work
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
