On Monday 10 Jun 2002 11:17 am, you wrote:
> First of all the referenced key must be on PRIMARY KEY.
...which means my 'id' -field can't be a primary key, right?
> 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
I've probably messed something up while fooling around with indexes and trying
to get it to work. Now, I took the advices I got, but I still can't get it to
work. I altered the table to make the 'id' -field an ordinary index, and
changed the name_id -field to a primary key, without luck. So now my
SHOW CREATE TABLE gives:
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 (`name_id`),
KEY `id` (`id`)
) TYPE=InnoDB
...and
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(6) unsigned NOT NULL default '0',
`hub_switch` varchar(20) NOT NULL default '',
`comp_name` varchar(50) NOT NULL default '',
`name_id` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`name_id`),
KEY `id` (`id`)
) TYPE=InnoDB
and, again:
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)
*sigh*
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
