Posted by Rik on 01/24/07 14:35

On Wed, 24 Jan 2007 12:35:25 +0100, Erwin Moller =

<since_humans_read_this_I_am_spammed_too_much@spamyourself.com> wrote:
> Coming from Postgres, still learning mysql, so be gentle. :)
> [I posted this question in alt.comp.database.mysql but that group does=
n't
> seem to be very active, and I am in a hurry, so forgive me for asking
> here.]

Hey Erwin, try comp.databases.mysql instead. It's much more active, =

allthough is seems a lot of newsserver don't carry it for obscure reason=
s.

> I use mysql 5 with innodb engine.
> When I create a table like:
> create table tbltest(
> testid int(10) UNSIGNED NOT NULL auto_increment,
> artid int(10) UNSIGNED NOT NULL REFERENCES tblart.artid,
> PRIMARY KEY (testid)
> ) ENGINE=3DInnoDB;
>
> Works fine.
> If I insert a value in artid that doesn't exist in tblart, mysql =

> produces an
> error as it should do.
>
> Now if I want to find out about the tablestructure, like this:
> SHOW CREATE TABLE tbltest;
> mysql just shows the create table syntax, BUT without the FK-constrain=
t.


As far as I know, as long as you have:
1. An index on artid
2. An explict FOREIGN KEY.

It should work.

Try:
CREATE TABLE `tbl_test` (
`id` int(10) NOT NULL auto_increment,
`artid` int(10) NOT NULL,
PRIMARY KEY (`id`),
KEY `artid` (`artid`),
CONSTRAINT `tbl_test` FOREIGN KEY (`artid`) REFERENCES `tblart` (`art=
id`)
) ENGINE=3DInnoDB

This is just to tell you how it works here, I have no idea _why_. That I=
=

leave up to the fine people of comp.databases.mysql (or I see =

mysql.general here also, seems better visited then a.c.d.mysql)
-- =

Rik Wasmus
* I'm testing several new newsreaders at the moment. Please excuse =

possible errors and weird content. *

[Back to original message]