Problem with foreign keys in .install part of a module
AlfSimen - April 4, 2008 - 09:31
Can anyone tell me if it is even possible to use foreign keys in the database?
Because I'm trying to use this code (shown below) in a .install file for a module I'm making, and we get a "ERROR 1005: Can't create table (errno: 150)" everytime.
What's wrong?
db_query("CREATE TABLE {Company_contact_ref}
(
parent_company_id INT NOT NULL,
parent_contact_id INT NOT NULL,
position VARCHAR(50),
updated DATETIME,
PRIMARY KEY (company_id, contact_id),
INDEX(parent_company_id, parent_contact_id),
FOREIGN KEY (parent_company_id)
REFERENCES {ContactCompany} (company_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (parent_contact_id)
REFERENCES {ContactPerson} (contact_id)
ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=INNODB;");
mysql configuration problem
Have you started here?
http://www.google.ca/search?q=mysql+errno+150
This is a mysql configuration problem, by the looks of it.
Apparently it turned out to
Apparently it turned out to be that MySQL 5.0.51a doesent use "foreign key" in the sql.
This on the other hand seemed to work:
db_query("CREATE TABLE {Company_contact_ref}(
company_id INT NOT NULL,
contact_id INT NOT NULL,
position VARCHAR(50),
updated DATETIME,
parent_company_id INT REFERENCES {ContactCompany} (company_id) ON DELETE CASCADE ON UPDATE CASCADE,
parent_contact_id INT REFERENCES {ContactPerson} (contact_id) ON DELETE NO ACTION ON UPDATE CASCADE,
PRIMARY KEY (company_id, contact_id)
);");