Problems creating foreign key

[Crundy]

Hello,
I have two tables, here are the 'show create' statements for them:

CREATE TABLE `useractskills` (
  `username` varchar(20) NOT NULL default '',
  `skilltype` int(11) NOT NULL default '0',
  `skillid` int(11) NOT NULL default '0',
  `dept` varchar(20) NOT NULL default '',
  KEY `xusername` (`username`),
  KEY `xskills` (`skilltype`,`skillid`,`dept`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `skills` (
  `skilltype` int(11) NOT NULL default '0',
  `id` int(11) NOT NULL default '0',
  `dept` varchar(20) NOT NULL default '',
  `name` text,
  `description` text,
  `details` text,
  KEY `xdept` (`dept`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

I'm trying to create a foreign key on useractskills for skilltype, skillid and dept:

ALTER TABLE useractskills ADD FOREIGN KEY `FK_skills` (`skilltype`, `skillid`, `dept`)
    REFERENCES `skills` (`skilltype`, `id`, `dept`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE;

But this fails:

ERROR 1005 (HY000): Can't create table './dbspd/#sql-8b6_51.frm' (errno: 150)

The only references I can find about this are that the fields have to be indexed (which they are), the types have to be the same across the tables (which they are) and you don't break any NOT NULL constraints using SET NULL (which I don't).

Does anyone know what the problem is?

C:\DOS:>
C:\DOS:>RUN
RUN DOS RUN!!

 

[Crundy]

OK, got it.
It looks like the column(s) in the primary table have to either be the primary key or have a unique constraint on them, so I can't do it anyway :-(

Nevermind!

C:\DOS:>
C:\DOS:>RUN
RUN DOS RUN!!

 

[r937]

try this --

create table skills 
( skilltype integer     not null default 0
, id        integer     not null default 0
, dept      varchar(20) not null default ''
, name        text
, description text
, details     text
, primary key (skilltype, id, dept)
) type=innodb 
;
create table useractskills 
( username  varchar(20) not null default ''
, skilltype integer     not null default 0
, skillid   integer     not null default 0
, dept      varchar(20) not null default ''
, primary key (username)
, key xskills (skilltype,skillid,dept)
, foreign key fk_skills (skilltype,skillid,dept)
    references skills (skilltype,id,dept)
    on delete no action
    on update cascade
) type=innodb 
;

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)