[Novalug] further questions on learning MySQL on ubuntu

[Bonnie Dalzell]

Supposedly MySQL supports foreign keys if you use the INNODB database.

However my attempts to do a tutorial foreign key example have not been 
successful.

Here is the result from the command line. The tutorial has me set up 
two tables, zoo and species. The foreign key will be in zoo.

describe species;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

mysql> describe zoo
     -> ;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(4)      | NO   | PRI | NULL    | auto_increment |
| name       | varchar(50) | NO   |     | NULL    |                |
| FK_species | tinyint(4)  | NO   | UNI | NULL    |                |
+------------+-------------+------+-----+---------+----------------+

when I try to add a foreign key - the syntax being from a tutorial 
- I get an error:

mysql> alter table zoo add foreign key (FK_species) References species 
(id);
ERROR 1005 (HY000): Can't create table 'test.#sql-592_237' (errno: 150)

anyone have any insight into this?

both zoo and the other table species are set as INNODB.


also if anyone can tell me how to configure my MySQL installation so 
the default collation (character set) is either latin1_general_ci or 
utf8_general_ci rather than latin1_swedish_ci I would appreciate it. Why 
is latin1_swedish_ci the default when MySQL is installed? Was MySQL 
invented by a Swede?


thanks.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                        Bonnie Dalzell, MA
mail:5100 Hydes Rd PO Box 60, Hydes,MD,USA 21082-0060|EMAIL:bdalzell at qis.net
Freelance anatomist, vertebrate paleontologist, writer, illustrator, dog
breeder, computer nerd & iconoclast... Borzoi info at www.borzois.com.
HOME www.batw.net    ART bdalzellart.batw.net  BUSINESS www.boardingatwedge.com