I just found out that my version of mysql does not support control structure. Now I've notice that I can't create a foreign key. Is it my syntax or version 4.0.17 does not support foreign key?

FOREIGN KEY(fk__ID)
REFERENCES table1(pk__ID)
ON UPDATE CASCADE
ON DELETE NO ACTION,

Thanks,

xayavon

Only the InnoDB table type supports referential integrity
(and foreign keys). To create an InnoDB table:

CREATE TABLE yourtablename (
blah blah bitty blah,
PRIMARY KEY(keyfield) )
TYPE=INNODB;

After creating the table, make sure it was created
with the right table type by doing a,

SHOW CREATE TABLE yourtablename;

and look for the word INNODB.

Hi

MySQL Supoort Foreing Keys in InooDB Tables.

For better understanding to read the articulate http://www.databasejournal.com/featu...le.php/2248101

Tool to administer graphically the database
http://www.mentattech.com/

Kind Regards

Thanks for the replies,

CREATE TABLE IF NOT EXISTS activitycodes
(
pk_ActivityCode_ID smallint UNSIGNED AUTO_INCREMENT NOT NULL,
PRIMARY KEY (pk_ActivityCode_ID),
) TYPE=INNODB;

CREATE TABLE IF NOT EXISTS activities
(
pk_Activity_ID int UNSIGNED AUTO_INCREMENT NOT NULL,
fk_ActivityCode_ID smallint NOT NULL,
PRIMARY KEY (pk_Activity_ID),
INDEX (fk_ActivityCode_ID),
FOREIGN KEY (fk_ActivityCode_ID)
REFERENCES activitycodes(pk_ActivityCode_ID)
ON UPDATE NO ACTION
ON DELETE NO ACTION,
) TYPE=INNODB;

I don't see what can be wrong with this. I keep getting an error.

So what's the error message?

You have two extra commas that shouldn't be there:

PRIMARY KEY (pk_ActivityCode_ID),

ON DELETE NO ACTION,

You will have fewer problems in the long run if you change
your primary keys from smallints and unsigned ints to just
a plain 'int'.

here is the error that I get.

Error Code : 1005
Can't create table './test/activities.frm' (errno: 150)
(16 ms taken)

C:\mysql\bin>perror 150
Error code 150: Unknown error
150 = Foreign key constraint is incorrectly formed



I just ran the following successfully on my computer.
(As I mentioned earlier, you will have fewer problems
if you change the primary key to just an 'int'.)


drop TABLE activitycodes;
CREATE TABLE IF NOT EXISTS activitycodes
(
pk_ActivityCode_ID int AUTO_INCREMENT NOT NULL,
PRIMARY KEY (pk_ActivityCode_ID)
) TYPE=INNODB;


drop TABLE activities;
CREATE TABLE IF NOT EXISTS activities
(
pk_Activity_ID int AUTO_INCREMENT NOT NULL,
fk_ActivityCode_ID int NOT NULL,
PRIMARY KEY (pk_Activity_ID),
INDEX (fk_ActivityCode_ID),
FOREIGN KEY (fk_ActivityCode_ID)
REFERENCES activitycodes(pk_ActivityCode_ID)
ON UPDATE NO ACTION
ON DELETE NO ACTION
) TYPE=INNODB;

vanekl,

Thanks for the solution! The problem was my primary key was defined int unsigned, but I defined my fk as just int. I will take your advice and change my pk definition.

Thanks,

xayavon