First of all the referenced key must be on PRIMARY KEY.
But I've seen in your table definition a quite strange thing. You have a
UNIQUE and an ORDINARY key definition on the same field.
Here:
...
> UNIQUE KEY `name_id` (`name_id`), <- THIS IS THE FIRST DEFINITION
> UNIQUE KEY `comp_name` (`comp_name`),
> KEY `conn_idx` (`name_id`) <- AND THIS IS THE SECOND ONE
...
At 10:59 2002.06.10. +0300, you wrote:
>Hello list!
>
>I'm having a bit of trouble getting foreign key constraints to work.
>I'm running MySQL 2.23.50-Max.
>
>Here's what I got:
>
>mysql> SHOW CREATE TABLE conn\G
>*************************** 1. row ***************************
> Table: conn
>Create Table: CREATE TABLE `conn` (
> `id` int(10) unsigned NOT NULL auto_increment,
> `wall_nr` int(10) unsigned NOT NULL default '0',
> `hub_switch` varchar(20) NOT NULL default '',
> `comp_name` varchar(80) NOT NULL default '',
> `name_id` int(10) unsigned NOT NULL default '0',
> PRIMARY KEY (`id`),
> UNIQUE KEY `name_id` (`name_id`),
> UNIQUE KEY `comp_name` (`comp_name`),
> KEY `conn_idx` (`name_id`)
>) TYPE=InnoDB
>1 row in set (0.00 sec)
>
>mysql> SHOW CREATE TABLE ip_name_tbl\G
>*************************** 1. row ***************************
> Table: ip_name_tbl
>Create Table: CREATE TABLE `ip_name_tbl` (
> `id` int(10) unsigned NOT NULL auto_increment,
> `comp_loc` smallint(5) unsigned NOT NULL default '0',
> `comp_sub_loc` smallint(5) unsigned NOT NULL default '0',
> `ip_stat_dyn` enum('DHCP','STATIC') NOT NULL default 'DHCP',
> `IP` varchar(15) default NULL,
> `MAC` varchar(17) NOT NULL default '',
> `network` enum('Hallinto','Asiakas') NOT NULL default 'Hallinto',
> `name_id` int(11) NOT NULL default '0',
> `comments` text,
> PRIMARY KEY (`id`),
> UNIQUE KEY `name_id` (`name_id`),
> KEY `ip_idx` (`name_id`)
>) TYPE=InnoDB
>1 row in set (0.00 sec)
>
>Here's what I get:
>
>mysql> ALTER TABLE ip_name_tbl
> -> ADD CONSTRAINT FOREIGN KEY (name_id)
> -> REFERENCES conn(name_id)
> -> ON DELETE CASCADE;
>ERROR 1005: Can't create table './koneet/#sql-355_4.frm' (errno: 150)
>
>I know error 1005 with an errno 150 means the foreign key would be incorrectly
>formed, but I can't just figure out what's wrong.
>I've been reading TFM, and the sentence "there must be an index where the
>foreign key and the referenced key are listed as the first columns" seems to
>have something to do with my problem, I just find the above a bit... well...
>cryptic. If anyone could lend me a hand, I'd be more than happy.
>
>Thank's in advance!
>
>Cheers,
>Markus
>
>--
>Markus Lervik
>Linux-administrator
>Vaasa City Library - Regional Library, Finland
>[EMAIL PROTECTED]
>+358-6-325 3589/+358-40-832 6709
>
>---------------------------------------------------------------------
>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
