Problem with creating table which includes foreign key??? Errn150

[joeserhal]

Hey there,

I'm trying to build a db with the following tables:

Create table student (
sid int primary key,
name varchar(20) not null,
year varchar(15) not null,
major varchar(5) not null)ENGINE=InnoDB DEFAULT CHARSET=latin1;

create table courses (
courseno varchar(6) primary key,
title varchar(20) not null,
credit int default 0,
category varchar(5) not null)ENGINE=InnoDB DEFAULT CHARSET=latin1;

create table class_session (
courseno varchar(6),
classno int,
date varchar(10),
room varchar(6) default null,
addr varchar(20) default null,
primary key (courseno,classno,date),
foreign key (courseno) references courses(courseno))ENGINE=InnoDB DEFAULT CHARSET=latin1;

create table plan (
sid int,
courseno varchar(6),
classno int,
date varchar(10) not null,
primary key(sid,courseno),

constraint `FK_plan_1` foreign key (sid) references student(sid),
constraint `FK_plan_2` foreign key (classno) references class_session(classno),
constraint `FK_plan_3`foreign key (courseno) references courses(courseno))ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED;


I'm able to create the first 3 tables (student, courses and class_sessions)...however, I'm unable to create the table "plan": it has has foreign key (classno) which references a composite primary key in another table "class_session"...

The error message is: can't create table "mydb_name\plan.frm" (errno:150)

Any help would be greatly appreciated!!!

Thanks

[Fumigator]

I didn't google the error message but it looks to me like you are trying to place a constraint on a column that can be non-unique. The key you refer back to must be unique to be a foreign key in another table.

(edit) I misspoke; seems it doesn't have to be unique, but you must create an index on that column (or create a compound index with that column being the first column in the index).

[joeserhal]

I tried creating index on the column classno still it is giving the same error. Can a foreign key reference an attribute which is a part of primary key??

This is what I tried

create table plan (
sid int,
courseno varchar(6),
classno int,
index classno_plan(classno),
date varchar(10) not null,
primary key(sid,courseno),

foreign key (sid) references student(sid),
foreign key (classno) references class_session(classno),
foreign key (courseno) references courses(courseno));

Also I tried below

mysql> create table plan (
-> sid int,
-> courseno varchar(6),
-> classno int,
-> date varchar(10) not null,
-> primary key(sid,courseno),
-> KEY `FK_plan_1` (`classno`),
-> foreign key (sid) references student(sid),
-> CONSTRAINT `FK_plan_1` foreign key (classno) references class_session(cla
ssno),
-> foreign key (courseno) references courses(courseno));

ERROR 1005 (HY000): Can't create table '.\mayur\plan.frm' (errno: 150)

[Fumigator]

Trying running Show innoDB status; from the command prompt and see if you can find information on the error.

[joeserhal]

Below is the part of the output I got after running show innoDB status;
Kindly note the foreign key error(RED). It doesnt work even if I created index for that column. So all the problem lies on foreign reference to "classno"
which is a composite primary key

Pleaase suggest some solution for the same

=====================================
081119 17:50:32 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 43 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 5, signal count 5
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 10, OS waits 5; RW-excl spins 0, OS waits 0
------------------------
LATEST FOREIGN KEY ERROR
------------------------
081119 17:50:17 Error in foreign key constraint of table mayur_project/plan:
foreign key (classno) references class_session(classno),
foreign key (courseno) references courses(courseno))ENGINE=InnoDB DEFAULT CHARSE
T=latin1 ROW_FORMAT=FIXED:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html
for correct foreign key definition.
------------
TRANSACTIONS
------------
Trx id counter 0 8968
Purge done for trx's n: o < 0 8964 undo n: o < 0 0
History list length 6
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 5076
MySQL thread id 3, query id 26 localhost 127.0.0.1 root
show innoDB status

[joeserhal]

I tried removing foreign key reference to classno from the below table plan.
There was no error found and it created the plan table.
So the problem is classno is part of composite primary key, so it cant be referred from other table


create table plan (
sid int,
courseno varchar(6),
classno int,
date varchar(10) not null,
primary key(sid,courseno),
constraint `FK_plan_1` foreign key (sid) references student(sid),
constraint `FK_plan_3`foreign key (courseno) references courses(courseno))ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED;

So is there any solution if I want to refer a composite primary key i.e a key which is part of a primary key (classno in my case)???

[bazz]

In the table class-session, don't you need to index the 'classno' column?
Have you indexed courseno in 'plan'?

hth

bazz

[mayurn]

Hey I m extremely thankful ...The above solution i.e creating index on classno in class_session worked!!!
I created index on plan table(referencing) table...thinking that it would work...

What is the exact funda of the above solution??
Once again thanks!!