|
|
Posted by Erwin Moller on 01/24/07 16:28
Rik wrote:
> 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 doesn'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 reasons.
>
>> 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=InnoDB;
>>
>> 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-constraint.
>
>
> 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`
> (`artid`)
> ) ENGINE=InnoDB
>
> 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)
Hi Rik,
Thanks for your suggestion: using the FK constraint in that way makes mySQL
indeed remember the syntax with SHOW TABLE. :-)
I only gave samplecode: In my real database I kept receiving an annoying
errno: 105.
On mysql website this is listed as 'internal InnoDB error regarding a
FOREIGN KEY'.
After some investigation I found the reason was this:
- On the parenttable I used 'unsigned' for the auto_increment PK column.
- When referencing it I ommitted that 'unsigned'.
I would like to use this oppertunity to thank the mySQL team for their
great, clear, and lengthy errordescriptions.
I would also like to thank them for using ` instead of ' as the rest of the
world does. It is a mistake I keep making. :-/
I would also like to thank them for silently ignoring REFERENCES in
tablecreation when not on InnoDB.
OMG, do I miss PostgreSQL!
(For dutchies: Why does XS4ALL not offer that?)
Sorry for the rant, and thanks for putting me on the right track.
Regards,
Erwin Moller
[Back to original message]
|