foreign key problem

[pradeepjain]

I have a table with fields like

mysql> desc users;
+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| uid              | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
| name             | varchar(60)      | NO   | UNI |         |                | 
| pass             | varchar(32)      | NO   |     |         |                | 
| mail             | varchar(64)      | YES  | MUL |         |                | 
| mode             | tinyint(4)       | NO   |     | 0       |                | 
| sort             | tinyint(4)       | YES  |     | 0       |                | 
| threshold        | tinyint(4)       | YES  |     | 0       |                | 
| theme            | varchar(255)     | NO   |     |         |                | 
| signature        | varchar(255)     | NO   |     |         |                | 
| signature_format | smallint(6)      | NO   |     | 0       |                | 
| created          | int(11)          | NO   | MUL | 0       |                | 
| access           | int(11)          | NO   | MUL | 0       |                | 
| login            | int(11)          | NO   |     | 0       |                | 
| status           | tinyint(4)       | NO   |     | 0       |                | 
| timezone         | varchar(8)       | YES  |     | NULL    |                | 
| language         | varchar(12)      | NO   |     |         |                | 
| picture          | varchar(255)     | NO   |     |         |                | 
| init             | varchar(64)      | YES  |     |         |                | 
| data             | longtext         | YES  |     | NULL    |                | 
+------------------+------------------+------+-----+---------+----------------+
19 rows in set (0.00 sec)

and another table

mysql> desc viio_patient_doctor_index;
+----------------------------+------------------+------+-----+---------+----------------+
| Field                      | Type             | Null | Key | Default | Extra          |
+----------------------------+------------------+------+-----+---------+----------------+
| Patient_Id                 | int(11)          | NO   | PRI | NULL    | auto_increment | 
| Doctor_Id                  | int(10) unsigned | NO   | MUL | NULL    |                | 
| Patient_Name               | varchar(100)     | NO   |     | NULL    |                | 
| Address                    | varchar(500)     | NO   |     | NULL    |                | 
| City                       | varchar(100)     | NO   |     | NULL    |                | 
| State                      | varchar(100)     | NO   |     | NULL    |                | 
| Postal_Code                | varchar(20)      | NO   |     | NULL    |                | 
| Phone_Home                 | varchar(20)      | YES  |     | NULL    |                | 
| Phone_Work                 | varchar(20)      | YES  |     | NULL    |                | 
| DOB                        | varchar(20)      | NO   |     | NULL    |                | 
| Language_Spoken            | varchar(200)     | YES  |     | NULL    |                | 
| Sex                        | varchar(10)      | NO   |     | NULL    |                | 
| Emergency_Contact_name     | varchar(200)     | YES  |     | NULL    |                | 
| Emergency_Contact_Number   | varchar(20)      | YES  |     | NULL    |                | 
| Gp_Name_Address            | varchar(250)     | YES  |     | NULL    |                | 
| Gp_Phone_Day               | varchar(20)      | YES  |     | NULL    |                | 
| Gp_Phone_Out_Day           | varchar(20)      | YES  |     | NULL    |                | 
| Pharmacist_Name_Number     | varchar(100)     | YES  |     | NULL    |                | 
| Nurse_Name                 | varchar(100)     | YES  |     | NULL    |                | 
| Nurse_Number               | varchar(20)      | YES  |     | NULL    |                | 
| Chiropodist_Name           | varchar(100)     | YES  |     | NULL    |                | 
| Chiropodist_Number         | varchar(20)      | YES  |     | NULL    |                | 
| Dietitian_Name             | varchar(100)     | YES  |     | NULL    |                | 
| Dietitian_Number           | varchar(20)      | YES  |     | NULL    |                | 
| Diabetes_Specialist_Name   | varchar(100)     | YES  |     | NULL    |                | 
| Diabetes_Specialist_Number | varchar(20)      | YES  |     | NULL    |                | 
| Hospital_Number            | varchar(20)      | YES  |     | NULL    |                | 
+----------------------------+------------------+------+-----+---------+----------------+
27 rows in set (0.00 sec)

but when i try to create a foreign key like

mysql> ALTER TABLE viio_patient_doctor_index ADD FOREIGN KEY(Doctor_Id)  REFERENCES users(uid) ON DELETE CASCADE;

ERROR 1005 (HY000): Can't create table './drupalweb/#sql-965_3c2.frm' (errno: 150)

i dono y its giving problem!!! any help on it!!

[pradeepjain]

Now its working !!! one table was MyISAM and another was innoDB.. i changed the MYISAM to InnoDB. then it started working!!! Hope the solution was correct !!!

[Atli]

Hey.

Yea, creating a foreign key from an InnoDB table to a MyISAM table doesn't work.
MyISAM doesn't support foreign keys. (Yet)

P.S.
[code] tags!

[pradeepjain]

Hey.

Yea, creating a foreign key from an InnoDB table to a MyISAM table doesn't work.
MyISAM doesn't support foreign keys. (Yet)

P.S.
[code] tags!

okie srry 4 that !! its just a mysql table structure so did not put code tags!!!