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
