Hi Victoria, Heikki, List members From another thread "Re: Foreign Key in Innodb and Index Usages" I found this:
>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. an tried to do it in another way f.e mysql> ALTER TABLE db ADD CONSTRAINT FOREIGN KEY (Host) REFERENCES user (Host); ERROR 1216: Cannot add or update a child row: a foreign key constraint fails But as you can see I get another error. Any comment much appreciated. Best regards Nils Valentin Tokyo/Japan 2003年 6月 11日 水曜日 11:06、Nils Valentin さんは書きました: > Hi Victoria, > > I believe that the User column is indexed (please see below): > > show index from user; > +-------+------------+----------+--------------+-------------+-----------+- >------------+----------+--------+------+------------+---------+ > > | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | > > Cardinality | Sub_part | Packed | Null | Index_type | Comment | > +-------+------------+----------+--------------+-------------+-----------+- >------------+----------+--------+------+------------+---------+ > > | user | 0 | PRIMARY | 1 | Host | A | > > 15 | NULL | NULL | | BTREE | | > > | user | 0 | PRIMARY | 2 | User | A | > > 15 | NULL | NULL | | BTREE | | > +-------+------------+----------+--------------+-------------+-----------+- >------------+----------+--------+------+------------+---------+ > > > Any comments appreciated. > > Nils Valentin > Tokyo/Japan > > 2003年 6月 10日 火曜日 22:52、Nils Valentin さんは書きました: > > Hi Victoria, > > > > 2003年 6月 10日 火曜日 19:16、Victoria Reznichenko さんは書きました: > > > "Nils Valentin" <[EMAIL PROTECTED]> wrote: > > > > I try to create foreign keys in a innodb database. I read through > > > > some threats and the manual, and several online resources, but > > > > somehow I am missing something. > > > > > > > > What I try to do is recreate the keys and their functions of the > > > > mysql database (privilege database) as a innodb database, including > > > > foreign keys to illustrate the relations. > > > > > > You shouln't convert privilege tables to the InnoDB. > > > > My description was probably not precise enough. I converted copies of the > > privilege tables (in a new datbase) - but thank you for mentioning it. > > > > > > The copy of the mysql privilege database is called > > > > mysql_privileges_db_inno. > > > > > > > > The 6 tables structure host, user, db etc. was copied to the innodb > > > > database and then modified with ALTER. > > > > > > > > Now when I try to create a foreign key I get the famous Error 1105 > > > > which tells me that I didnt form the request for the foreign key > > > > proberly. > > > > > > > > mysql> ALTER TABLE db ADD FOREIGN KEY (User) REFERENCES user (User); > > > > ERROR 1005: Can't create table > > > > './mysql_privileges_db_inno/#sql-2e8_10.frm' (errno: 150) > > > > > > Column User in the table User must be indexed. > > > > I see thank you very much ;-). I understood that an Index already exists > > from the orginal datbase, but I am going to check it once more. > > > > > -- > > > For technical support contracts, goto > > > https://order.mysql.com/?ref=ensita This email is sponsored by > > > Ensita.net http://www.ensita.net/ > > > __ ___ ___ ____ __ > > > / |/ /_ __/ __/ __ \/ / Victoria Reznichenko > > > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] > > > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net > > > <___/ www.mysql.com > > > > -- > > ================================================ > > Valentin Nils > > Internet Technology > > > > E-Mail: [EMAIL PROTECTED] > > URL: http://www.knowd.co.jp > > ================================================ > > -- > --- > Valentin Nils > Internet Technology > > E-Mail: [EMAIL PROTECTED] > URL: http://www.knowd.co.jp > Personal URL: http://www.knowd.co.jp/staff/nils -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
