[SOLVED] Help please - Cant create table?

[bleachie]

Hi guys,

 

I keep getting the errors:

 

[root@localhost:3306] ERROR 1005: Can't create table '.\videogame\gameasset.frm' (errno: 150)

[root@localhost:3306] ERROR 1005: Can't create table '.\videogame\teamasset.frm' (errno: 150)

 

for this database

 

create database VideoGame;
use VideoGame;

create table Game
(
GameID int not null auto_increment,
Title varchar (50),
ReleaseDate date null,
Publisher varchar (75),
primary key (GameID)
)type=innodb;

create table GameAsset
(
GameID int not null auto_increment,
FileName varchar (60),
primary key (GameID, FileName),
index (GameID),
foreign key (GameID) references Game (GameID),
index (FileName),
foreign key (FileName) references Asset (FileName)
)type=innodb;


create table Asset
(
FileName int not null,
Title varchar (75) not null,
AssetType varchar (40),
Filesize int,
DateCreated date null,
VersionNumber tinyint not null,
MediaSample blob null,
primary key (FileName),
index (AssetType),
foreign key (AssetType) references Asset (AssetType)
)type=innodb;

create table AssetType
(
AssetType varchar (40) not null,
Description varchar (80) null,
MIMEType varchar (18),
primary key (AssetType)
)type=innodb;

create table TeamAsset
(
TeamName varchar (70) not null,
FileName varchar (60) not null,
primary key (TeamName, FileName),
index (TeamName),
foreign key (TeamName) references Team (TeamName),
index (FileName),
foreign key (FileName) references Asset (FileName)
)type=innodb;

create table Team
(
TeamName varchar (70) not null,
ContactEmail varchar (75),
primary key (TeamName)
)type=innodb;


create table DesignerTeam
(
DesignerEmail varchar (75) not null,
TeamName varchar (70) not null,
primary key (TeamName, DesignerEmail),
index (TeamName),
foreign key (TeamName) references Team (TeamName),
index (DesignerEmail),
             foreign key (DesignerEmail) references Designer (DesignerEmail)
)type=innodb;

create table Designer
(
DesignerEmail varchar (75) not null,
FirstName varchar (50) not null,
LastName varchar (50) not null,
Nationality varchar (20),
DateOfBirth date null,
Biography blob null,
Photo blob null,
primary key (DesignerEmail)
)type=innodb;

create table DesignerSkill
(
DesignerEmail varchar (75) not null,
Skillname varchar (50) not null,
AbilityRating varchar (15) null,
primary key (SkillName, DesignerEmail),
index (SkillName),
foreign key (SkillName) references Skill (SkillName),
index (DesignerEmail),
foreign key (DesignerEmail) references Designer (DesignerEmail)
)type=innodb;

create table Skill
(
Skillname varchar (50) not null,
primary key (Skillname)
)type=innodb;

 

please could someone tell me whats wrong or give me some hints..

 

Thanks

 

[fenway]

Sounds like a permissions error on the directory tree.

[Barand]

I'm guessing that in table gameAsset you have a foreign key that references the Asset table, but the Asset hasn't been created at that point.

[bleachie]

the asset table does get created tho.

[Barand]

Its foreign key only references itself, although I suspect that's an error and it should reference AssetType table.

[bleachie]

thanks for your help its fixed