Martin,
-----Original Message-----
From: "Martin Bratbo" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Date: Saturday, February 16, 2002 4:46 PM
Subject: Re: Re: Foreign keys in InnoDB tables
>Heikki
>here is the statements that i cant get to work:
>first I create one innoDB table: fk1
>create table fk1( noegle integer primary key, tekst
varchar(20))type=InnoDB;
>
>then I create a second InnoDB table: fk2which references the first:
>
>create table fk2( prim integer primary key, frem integer, Foreign key fk
(frem) references fk1(noegle))type=InnoDB;
you have a syntax error here: after FOREIGN KEY you have a symbol 'fk'.
MySQL+InnoDB ignores the constraint.
"
The syntax of a foreign key constraint definition in InnoDB:
FOREIGN KEY (index_col_name, ...) REFERENCES table_name (index_col_name,
...)
Note that you should not use quoted table or column names in a FOREIGN KEY
clause. The InnoDB parser does not currently know that notation.
An example:
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))
TYPE=INNODB;
Both tables have to be InnoDB type and there must be an index where the
foreign key and the referenced key are listed as the first columns. InnoDB
does not auto-create indexes on foreign keys or referenced keys: you have to
create them explicitly.
"
Best regards,
Heikki Tuuri
Innobase Oy
---
Order technical MySQL/InnoDB support at https://order.mysql.com/
See http://www.innodb.com for the online manual and latest news on InnoDB
>I then insert a tuple into the first table
>insert into fk1 values (1,'xx');
>
>no problem
>
>then I insert a tuple into the second table a tuble that shoud have been
rejected because it violates the referntial integrity
>
>insert into fk1 values (1,'xx');
>
>but this tuple is accepted and inserted into fk2 it seem that the foreig
keyconstraint doesn't do anythig even thoug both tabels are InnoDB tables,
is the any settings that is neccesarry to activate the constraint ?
>my my.ini file is as follows:
>
>[mysqld]
>basedir=d:/mysql
>datadir=d:/mysql/data
>innodb_data_file_path=ibdata1:500M;ibdata2:500M
>innodb_data_home_dir=D:\mysql\innodb
>set-variable=innodb_mirrored_log_groups=1
>innodb_log_group_home_dir=D:\mysql\iblogs
>set-variable=innodb_log_files_in_group=3
>set-variable=innodb_log_file_size=30M
>set-variable=innodb_log_buffer_size=8M
>innodb_flush_log_at_trx_commit=1
>#.._arch_dir must be the same as .._log_group_home_dir
>innodb_log_arch_dir=D:\mysql\iblogs
>innodb_log_archive=0
>set-variable=innodb_buffer_pool_size=70M
>#454 MySQL Technical Reference for Version 4.0.1-alpha
>set-variable=innodb_additional_mem_pool_size=10M
>set-variable=innodb_file_io_threads=4
>set-variable=innodb_lock_wait_timeout=50
>
>
>Regards
>Martin,
>
>there is a bug in 4.0.1 which can make a foreign key definition to fail in
>an assertion failure in dict0crea.c, if you have set default-character-set
>to something else than latin1 in my.cnf.
>
>Harald Fuchs reported the bug on this mailing list a couple of days ago,
and
>the bug is now fixed in 4.0.2.
>
>Please send your my.cnf or my.ini to this mailing list, and post the exact
>sequence of SQL statements which in your opinion produces a wrong response.
>
>Best regards,
>
>Heikki Tuuri
>Innobase Oy
>---
>InnoDB - transactions, row level locking, and foreign key support for MySQL
>See http://www.innodb.com, download MySQL-Max from http://www.mysql.com
>
>"Martin Bratbo" wrote in message ...
>>According to the manual it should in fact be possible to enforce foreign
>key constraint in MySql if both the referreing and referred tables are of
>type InnoDB. But I haven't been able to, make the foreign keys work, they
>did'nt blok any insertions.
>>
>>Are foreign keys still only for compability, or is there a way to actually
>make the constraints work if the tables are InnoDB ?
>>
>>I am running 4.0.1-alpha-max on win98
>>
>>
>>Regards
>>
>>
>>Martin Bratbo
>
>
>
>
>---------------------------------------------------------------------
>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
>
---------------------------------------------------------------------
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
