Haisam, ----- Original Message ----- From: ""Haisam K. Ido"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Friday, January 03, 2003 2:01 AM Subject: Re: Foreign Key problem? in MySQL 4.0.7
> Heikki: > > Do you mean the PRIMARY KEY order? >> UNIQUE KEY keyword(id,groupname,gid), >> PRIMARY KEY (groupname, gid) in both of the above indexes (= keys) 'gid' is the second column. InnoDB has to be able to make the parent row check fast when you add a child row. If you do not have 'gid' as the first column in an index, InnoDB would need to do a table scan of the parent table when you add a child row. That is why you must have the column as the first column in an index, in both the parent and the child table. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com sql query > Heikki Tuuri wrote: > > Haisam, > > > > ----- Original Message ----- > > From: ""Haisam K. Ido"" <[EMAIL PROTECTED]> > > Newsgroups: mailing.database.mysql > > Sent: Wednesday, January 01, 2003 10:29 PM > > Subject: Foreign Key problem? in MySQL 4.0.7 > > > > > > > >>I was able to create the "gid" table with no problem under mysql 4.0.7 > >> > >>CREATE TABLE gid ( > >> id INT(11) NOT NULL auto_increment, > >> groupname VARCHAR(128) NOT NULL, > >> passwd VARCHAR(128) NULL, > >> gid INT(11) NOT NULL, > >> username VARCHAR(128) NULL, > >> inserted_by VARCHAR(128) NOT NULL default 'root', > >> epoch_inserted DATETIME NULL, > >> modified_by VARCHAR(128) NOT NULL default 'root', > >> epoch_modified DATETIME NULL, > >> UNIQUE KEY keyword(id,groupname,gid), > >> PRIMARY KEY (groupname, gid) > >>) TYPE=INNODB; > >> > >>but when I attempt to create the "uid" table below I get the following > > > > foreign key > > > >>error. What am I doing wrong? > > > > > > > > the referenced column gid must appear as the FIRST column in some index. > > Above it appears as the second column. > > > > > > > >>ERROR 1005: Can't create table './vhadmindb/uid.frm' (errno: 150) > >> > >>CREATE TABLE uid ( > >> id INT(11) NOT NULL auto_increment, > >> username VARCHAR(128) NOT NULL, > >> passwd VARCHAR(128) NOT NULL, > >> uid INT(11) NOT NULL, > >> gid INT(11) NOT NULL, INDEX gid_ind (gid), FOREIGN KEY > > > > (gid) > > > >>REFERENCES gid(gid), > >> gecos VARCHAR(128) NULL, > >> homedir VARCHAR(128) NOT NULL, > >> shell VARCHAR(128) NOT NULL, > >> inserted_by VARCHAR(128) NOT NULL default 'root', > >> epoch_inserted DATETIME NULL, > >> modified_by VARCHAR(128) NOT NULL default 'root', > >> epoch_modified DATETIME NULL, > >> UNIQUE KEY keyword(id, username, uid), > >> PRIMARY KEY (username,uid) > >>) TYPE=INNODB; > > > > > > Best regards, > > > > Heikki Tuuri > > Innobase Oy > > --- > > Order technical MySQL/InnoDB support at https://order.mysql.com/ > > See http://www.innodb.com for the online manual and latest news on InnoDB > > > > sql query > > > > > > > > > > --------------------------------------------------------------------- > > Before posting, please check: > > http://www.mysql.com/manual.php (the manual) > > http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
