Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Problems creating foreign key

Status
Not open for further replies.

Crundy

Programmer
Jul 20, 2001
305
GB
Hello,
I have two tables, here are the 'show create' statements for them:

Code:
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:

Code:
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!!
 
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!!
 
try this --
Code:
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)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top