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@gmx.net
