#1005 - Can't create table Error

[Nibinaear]

I'm trying to create my tables in phpmyadmin and they work perfectly in MySQL 5, but I get the following error in "MySQL 4.0.24_Debian-10".


#1005 - Can't create table './118tracker/news_archive.frm' (errno: 150)


I've researched the problem and all I get it "create and index on the column". I've tried creating standalone indexes, indexes using the alter table syntax and indexes within the table definition as below. That doesn't seem to help. The columns are the same for the news_archive website_id foreign key and the websites primary key as you can see:


CREATE TABLE websites
(
website_id int(11) auto_increment not null,
name varchar(100) not null,
url varchar(100) not null,
home_text text not null,
text_date_edited datetime not null,
PRIMARY KEY(website_id),
INDEX idx_website_id(website_id)
)
ENGINE=InnoDB;

CREATE TABLE news_archive
(
archive_news_id int(11) auto_increment not null,
arch_name varchar(200) not null,
arch_content text not null,
arch_date_added datetime not null,
website_id int(11) not null,
PRIMARY KEY(archive_news_id),
foreign key(website_id) references websites(website_id),
INDEX idx_archive_id(archive_news_id)
)
ENGINE=InnoDB;


There are other unrelated tables in the database using the myisam table type, but I'd like to use InnoDB because I want to be able to do transactions. If indexes really is the answer could you provide an example of what I must do to make it work? Also, does it matter what kind of indexes are used? (BTree for example.)

Daniel

[Nibinaear]

Fixed! After much toil I came across an excellent resource which helped solve the problem. It was to do with indexes but an index needs to be created on the foreign key of the table which references:


CREATE TABLE websites
(
website_id int(11) auto_increment not null,
name varchar(100) not null,
url varchar(100) not null,
home_text text not null,
text_date_edited datetime not null,
PRIMARY KEY(website_id)
)
ENGINE=InnoDB;

CREATE TABLE news_archive
(
archive_news_id int(11) auto_increment not null,
arch_name varchar(200) not null,
arch_content text not null,
arch_date_added datetime not null,
website_id int(11) not null,
PRIMARY KEY(archive_news_id),
foreign key(website_id) references websites(website_id),
INDEX idx_web_an_id(website_id)
)
ENGINE=InnoDB;


Notice the index? Call it whatever you like and make sure it has the same name as the foreign key (obviously) and you're done. This occurs in MySQL version 4.0 and later (I think) so check you're version first. Here is the resource which I found (also identifies other reasons the error occurs):

http://sql-info.de/mysql/referential-integrity.html