[Novalug] further questions on learning MySQL on ubuntu

[Jeff Stoner]

On Thu, 16 Dec 2010, Bonnie Dalzell wrote:

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

Foreign keys are supported by InnoDB but only between tables using the 
InnoDB engine.

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

What tutorial?

> 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;

Use "show create table <tablename>" as it gives much more info.

> +-------+-------------+------+-----+---------+----------------+
> | Field | Type        | Null | Key | Default | Extra          |
> +-------+-------------+------+-----+---------+----------------+
> | id    | int(11)     | NO   | PRI | NULL    | auto_increment |
> | name  | varchar(50) | NO   |     | 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)

>From the MySQL manual:
Similarly, if an ALTER TABLE fails and it refers to error 150, that means 
a foreign key definition would be incorrectly formed for the altered 
table. You can use SHOW ENGINE INNODB STATUS to display a detailed 
explanation of the most recent InnoDB foreign key error in the server.

Execute the alter table statement again and immediately after getting the 
error, issue the show engine... statement. Then post that if the cause 
isn't immediately evident.

Just some points for future reference:

"test.#sql-592_237" is a temporary table in the test database.

> 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

I'll refer you to Chapter 9 of the manual that covers this. 
http://dev.mysql.com/doc/refman/5.1/en/internationalization-localization.html

> invented by a Swede?

Yes, it was and named after his son, My.

--Jeff

"You cannot unsay a cruel word." - Unknown