What I think, is that your syntax for creating the primary key is slightly
incorrect. I'm not sure if this is also true vor MySQL but I got teached at
school that a foreign key can only point to the primary key of a table.
Perhaps you can try to do the following:
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
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`),
FOREIGN KEY (name_id) REFERENCES conn ON DELETE CASCADES
KEY `id` (`id`)
) TYPE=InnoDB;
Notice the foreign key already in the table definition, and off course
creating table `conn` before the other one. If this doesn't work, try
rewriting your foreign key constraint to:
ALTER TABLE ip_name_tbl
ADD CONSTRAINT FOREIGN KEY (name_id)
REFERENCES conn
ON DELETE CASCADE;
* without pointing to which column the key points, just the table.
Btw, why do you have an `id` field, set as NOT NULL and with an
auto_increment, with besides another field `name_id` set as primary key?
Greetzz,
Wouter
(being my first msg to this list, btw: hello everybody .. i'm wouter and new
to this list ;) hihi )
----------
Alle door mij verzonden email is careware. Dit houdt in dat het alleen
herlezen en bewaard mag worden als je goed omgaat met al het leven op aarde
en daar buiten. Als je het hier niet mee eens bent dien je mijn mailtje
binnen 24 uur terug te sturen, met opgaaf van reden van onenigheid.
All email sent by me is careware. This means that it can only be reread and
kept if you are good for all the life here on earth and beyond. If you don't
agree to these terms, you should return this email in no more than 24 hours
stating the reason of disagreement.
-----Oorspronkelijk bericht-----
Van: Markus Lervik [mailto:[EMAIL PROTECTED]]
Verzonden: maandag 10 juni 2002 10:45
Aan: Kiss Dániel
CC: [EMAIL PROTECTED]
Onderwerp: Re: InnoDB foreign key constraints
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
---------------------------------------------------------------------
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
