Markus,
the problem is that name_id is UNSIGNED in the first table and SIGNED in the
second.
As stated in the manual, integer columns must have the same signedness and
size in a foreign key constraint.
Regards,
Heikki
Innobase Oy
----- Original Message -----
From: "Markus Lervik" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Monday, June 10, 2002 10:57 AM
Subject: InnoDB foreign key constraints
>
> 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
