any mysql experts around?

[Doom9]

mysql definitely hates me.. I've spent an hour now trying to add foreign keys to a table, even tried recreating the table and adding foreign keys during the adding phase but without any luck. Here's a piece of my db:

CREATE TABLE `vm` (
`vmid` int(11) NOT NULL auto_increment,
`vmname` varchar(30) NOT NULL default '',
`description` varchar(100) NOT NULL default '',
`vmip` varchar(15) NOT NULL default '',
`vmlogin` varchar(15) NOT NULL default '',
`vmpassword` varchar(100) NOT NULL default '',
`srvid` int(11) NOT NULL default '0',
`user` varchar(30) NOT NULL default '',
PRIMARY KEY (`vmid`),
UNIQUE KEY `vmname` (`vmname`),
KEY `srvid` (`srvid`)
) TYPE=MyISAM AUTO_INCREMENT=11 ;

CREATE TABLE `server` (
`serverid` int(11) NOT NULL auto_increment,
`srvname` varchar(15) NOT NULL default '',
`srvip` varchar(15) NOT NULL default '',
PRIMARY KEY (`serverid`),
UNIQUE KEY `srvname` (`srvname`)
) TYPE=MyISAM AUTO_INCREMENT=10 ;

What I need is vm.srvid to reference server.serverid so that if a vm.srvid links to an existing server, the server can't be deleted as long as that link is there. I've tried the obvious

ALTER table `vm` ADD FOREIGN KEY (srvid) REFERENCES server(serverid) ON UPDATE CASCADE ON DELETE RESTRICT

but that wouldn't to js. I got an example with foreign keys working (the 3 table example described here: http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html) but for the life of it, I can't adapt that to my purposes.. every change I make turns into my SQL commands to create foreign keys being accepted, but not being added.

[Dimmer]

Originally posted by Doom9
CREATE TABLE `vm` (
`vmid` int(11) NOT NULL auto_increment,
`vmname` varchar(30) NOT NULL default '',
`description` varchar(100) NOT NULL default '',
`vmip` varchar(15) NOT NULL default '',
`vmlogin` varchar(15) NOT NULL default '',
`vmpassword` varchar(100) NOT NULL default '',
`srvid` int(11) NOT NULL default '0',
`user` varchar(30) NOT NULL default '',
PRIMARY KEY (`vmid`),
UNIQUE KEY `vmname` (`vmname`),
KEY `srvid` (`srvid`)
) TYPE=MyISAM AUTO_INCREMENT=11 ;

CREATE TABLE `server` (
`serverid` int(11) NOT NULL auto_increment,
`srvname` varchar(15) NOT NULL default '',
`srvip` varchar(15) NOT NULL default '',
PRIMARY KEY (`serverid`),
UNIQUE KEY `srvname` (`srvname`)
) TYPE=MyISAM AUTO_INCREMENT=10 ;
Although I'm more of a T-SQL than MySQL guy myself, as far as I know only InnoDB type tables support FOREIGN KEY constraints. You created both tables as MyISAM type, so the database link is not supposed to work. There is more information about this on the site that you mentioned; that example applies to InnoDB type only, as you can see from Chapter 16 overview (http://dev.mysql.com/doc/mysql/en/InnoDB_overview.html). If possible, try changing the table type (also referred to as engine).

Hope this helps.

[Doom9]

@Dimmer: Thanks for pointing out the obvious. This seems to be the explanation for my problems. Interestingly enough MySQL still lets you add FKs even if you use the wrong table type (and it also allows you to remove them again so it feels like it's actually working but once you try to delete an entry that should't be deletable you know better).