From: | Ian Barwick <barwick(at)gmx(dot)net> |
---|---|
To: | "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>, Bo Lorentsen <bl(at)netgroup(dot)dk> |
Cc: | Vivek Khera <khera(at)kcilink(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Buglist |
Date: | 2003-08-19 22:21:58 |
Message-ID: | 200308200021.58395.barwick@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
On Tuesday 19 August 2003 23:10, scott.marlowe wrote:
> On 19 Aug 2003, Bo Lorentsen wrote:
> > On Tue, 2003-08-19 at 18:17, Vivek Khera wrote:
> > > Since the beginning of time (at least MySQL v3.22) MySQL has silently
> > > ignored the foreign key references in table create statement. Now
> > > that they have foreign key support (version 4.x), do they honor those
> > > statements? Nope. You have to use their own syntax to declare your
> > > FKs. They still silently ignore the references in the table create
> > > statements.
> >
> > Is this really true ?? Does 4.x still not support FK, then how about
> > transactions, does they that not work too ?
> >
> > Is this not just the MyISAM tables that still got the problem (they are
> > verison 3.x) ?
>
> No, the problem is that in SQL spec, you do it with the foreign key
> declaration inside parnes in the create statement like:
>
> create table abc123
> (
> id serial unique,
> info text);
> create table abc1234
> (
> moreinfo text,
> ref_id int,
> foreign key (ref_id)
> references abc123(id)
> on delete cascade
> );
>
> In MySQL this syntax is silently swallowed, while their own "proper"
> syntax is like this:
>
> create table abc123
> (
> id serial unique,
> info text)
> type=innodb;
> create table abc1234
> (
> moreinfo text,
> ref_id int)
> foreign key (ref_id) references abc123(id)
> on delete CASCADE
> type=innodb;
(To be precise this will fail with an obscure message; an
index must be created on ref_id)
> So the syntaxes are different, and one is apparently swallowed without
> error or anything, but in fact you have no fks in place.
Just to confuse things further:
1: if the MySQL version running is not configured for innodb tables,
tables created with type=innodb will be silently converted to
MyISAM;
2: These statements will succeed:
create table abc123 (
id INT unique,
info text
) type=innodb;
create table abc1234 (
moreinfo text,
ref_id int REFERENCES abc123(id)
) type=innodb;
but the foreign key defined on ref_id is (I presume)
transported to a remote forest in Sweden and eaten
by goats ;-)
Ian Barwick
barwick(at)gmx(dot)net
From | Date | Subject | |
---|---|---|---|
Next Message | Matthew T. O'Connor | 2003-08-19 22:53:41 | Re: Buglist |
Previous Message | Bo Lorentsen | 2003-08-19 22:13:09 | Re: Buglist |
From | Date | Subject | |
---|---|---|---|
Next Message | Matthew T. O'Connor | 2003-08-19 22:53:41 | Re: Buglist |
Previous Message | ler | 2003-08-19 22:18:46 | Your details |