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 [EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
