need some simple help on referencing

Anon

I have this code for part of my database and i am now using innodb tables which I think are configured to work as when i was the command mysql>show variables like "have_%" it says that innodb is set to YES. This is two of my tables:

CREATE TABLE ARTISTS
( MEMBERID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
HOUSE_NO CHAR(6) NOT NULL,
STREET VARCHAR(30) NOT NULL,
TOWN VARCHAR(20) NOT NULL,
POSTCODE VARCHAR(10) NOT NULL,
DOB DATE NOT NULL,
EMAIL VARCHAR(20),
CONTACT_NO INT NOT NULL, //? HOW DO I STORE A MOBILE NUMBER?
STAGENAME VARCHAR(15),
EXPRNCE_IN_YEARS INT NOT NULL,
SEX CHAR(1) NOT NULL,
NI_NO VARCHAR(12) NOT NULL
)
TYPE=INNODB;

CREATE TABLE SPECIALIST_AREA
( MEMBERID INT UNSIGNED NOT NULL
SPECIAL_AREA VARCHAR(20) NOT NULL //EG DJ, SINGER, DANCER, MC ETC.
GENRE VARCHAR(20),
FOREIGN KEY (MEMBERID)REFERENCES ARTISTS (MEMBERID)
)
TYPE=INNODB;

unfortunately i keep getting a error 1005: cant create table (errno: 150) I know that this error means that I have an incorectly referenced foreign key but I cannot see why? can anyone point out my mistake? thanks very much!
ps if innodb tables are working should i get some random processes going in the console window or is that taken care of in the winmysqladmin program?

Anon

Simon,

In your second table defn, you need to create an index on memberid.

Hidden away in the manual,

"Both tables have to be InnoDB type and there must be an index where the foreign key and the referenced key are listed as the first columns. InnoDB does not auto-create indexes on foreign keys or referenced keys: you have to create them explicitly. "

Cheers

Justin

Anon

i'm not quite sure what u mean. Do you mean that I have to form a composite primary key that in turn will be a foreign key that references the other table? create an index? does that mean create a primary key yeh? could u show me what u mean with my sample of code please? thanks Justin!

Anon

Do you mean like this:

CREATE TABLE SPECIALSIST_AREA
( MEMBERID INT UNSIGNED NOT NULL,//DOES IT HAVE TO BE AUTO_INCREMENT EVEN THOUGH IT IS STATED AS AUTO_INC IN EARLIER TABLE?
SPECIAL_AREA VARCHAR(20) NOT NULL,
GENRE VARCHAR(20),
PRIMARY KEY (MEMBERID, SPECIAL_AREA),
FOREIGN KEY (MEMBERID) REFERENCES ARTISTS (MEMBERID)
)
TYPE=INNODB;

hows this? mysql accepted this table but i'm not sure if it will do what I want it to do! I want my database to function like an oracle database but I cant afford oracle and I'm not sure how to use it with php! i'll stick to mysql for now!
thanks if you can assist!

simon