Re: More than one foreign key in a table
BKR, you can have any number of foreign keys in a single table. But you have not created the necessary indexes on foreign keys: ... Both tables have to be InnoDB type and there must be an index where the foreign key and the referenced key are listed as the first columns. InnoDB does not auto-create indexes on foreign keys or referenced keys: you have to create them explicitly. Corresponding columns in the foreign key and the referenced key must have similar internal data types inside InnoDB so that they can be compared without a type conversion. The size and the signedness of integer types has to be the same. The length of string types need not be the same. ... 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 - Original Message - From: B.K.R. Shivaprakkash [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Saturday, July 13, 2002 7:13 AM Subject: Re: More than one foreign key in a table Hi, The below mentioned sql makes an error when created with two foreign key constraints under MySQL/Innodb table type. Any help to solve this problem? create table UserMenuRestrictions ( CompanyCodechar(10) not null, UserId char(10) not null, MenuName char(40) not null, MenuParentName char(40) not null, AddRight char(1) , EditRight char(1) , DeleteRightchar(1) , ViewRight char(1) , Visiblechar(1) , Enabledchar(1) , primary key (CompanyCode, UserId, MenuName, MenuParentName), foreign key (CompanyCode, UserId) references UserDetails (CompanyCode, UserId) on update restrict on delete restrict, foreign key (MenuName, MenuParentName) references MenuDetails (MenuName, MenuParentName) on update restrict on delete restrict ) type = innodb; Thankyou in advance. Happiness Always BKR Shivaprakkash __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com - 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
Re: Re: More than one foreign key in a table
B.K.R., Saturday, July 13, 2002, 7:08:54 AM, you wrote: BKRS The below mentioned sql makes an error when created BKRS with two foreign key constraints under MySQL/Innodb BKRS table type. Any help to solve this problem? BKRS create table UserMenuRestrictions BKRS ( BKRS CompanyCodechar(10) not null, BKRS UserId char(10) not null, BKRS MenuName char(40) not null, BKRS MenuParentName char(40) not null, BKRS AddRight char(1) , BKRS EditRight char(1) , BKRS DeleteRightchar(1) , BKRS ViewRight char(1) , BKRS Visiblechar(1) , BKRS Enabledchar(1) , BKRS primary key (CompanyCode, UserId, MenuName, BKRS MenuParentName), BKRS foreign key (CompanyCode, UserId) BKRS references UserDetails (CompanyCode, UserId) BKRS on update restrict on delete restrict, BKRS foreign key (MenuName, MenuParentName) BKRS references MenuDetails (MenuName, MenuParentName) BKRS on update restrict on delete restrict BKRS ) type = innodb; BKRS Thankyou in advance. You must create separate indexes on the pair of columns (CompanyCode, UserId) and (MenuName, MenuParentName) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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
Re: More than one foreign key in a table
You shouldn't have a problem if you list each foreign key separately, as follows: foreign key (MenuName) references MenuDetails (MenuName)on update restrict on delete restrict, foreign key (MenuParentName) references MenuDetails (MenuParentName)on update restrict on delete restrict, That's how I did it on one of my tables that needed two foreign keys and it worked fine. --Michael --- B.K.R. Shivaprakkash [EMAIL PROTECTED] wrote: Hi, The below mentioned sql makes an error when created with two foreign key constraints under MySQL/Innodb table type. Any help to solve this problem? create table UserMenuRestrictions ( CompanyCodechar(10) not null, UserId char(10) not null, MenuName char(40) not null, MenuParentName char(40) not null, AddRight char(1) , EditRight char(1) , DeleteRightchar(1) , ViewRight char(1) , Visiblechar(1) , Enabledchar(1) , primary key (CompanyCode, UserId, MenuName, MenuParentName), foreign key (CompanyCode, UserId) references UserDetails (CompanyCode, UserId) on update restrict on delete restrict , foreign key (MenuName, MenuParentName) references MenuDetails (MenuName, MenuParentName) on update restrict on delete restrict ) type = innodb; Thankyou in advance. Happiness Always BKR Shivaprakkash __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com - 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 __ Do You Yahoo!? Yahoo! Autos - Get free new car price quotes http://autos.yahoo.com - 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
Re: More than one foreign key in a table
Hi, The below mentioned sql makes an error when created with two foreign key constraints under MySQL/Innodb table type. Any help to solve this problem? create table UserMenuRestrictions ( CompanyCodechar(10) not null, UserId char(10) not null, MenuName char(40) not null, MenuParentName char(40) not null, AddRight char(1) , EditRight char(1) , DeleteRightchar(1) , ViewRight char(1) , Visiblechar(1) , Enabledchar(1) , primary key (CompanyCode, UserId, MenuName, MenuParentName), foreign key (CompanyCode, UserId) references UserDetails (CompanyCode, UserId) on update restrict on delete restrict, foreign key (MenuName, MenuParentName) references MenuDetails (MenuName, MenuParentName) on update restrict on delete restrict ) type = innodb; Thankyou in advance. Happiness Always BKR Shivaprakkash __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com - 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
Re: Re: More than one foreign key in a table
B.K.R., Tuesday, June 18, 2002, 5:52:07 AM, you wrote: BKRS Can I have more than one foreign key defined in a BKRS single table using MySQL/Innodb table type? Yes, it's possible. BKRS It gives an error in .frm file, if tried to have BKRS that? BKRS Any solution? Check your CREATE TABLE definition. BKRS Happiness Always BKRS BKR Shivaprakkash -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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
