newbie: error 1005, cannot create table

Hey

MySql 5.0

When trying to create the Property table (see below) I get this error
message:
ERROR 1005 (HY000): Can't create table '.\demo\property.frm' (errno: 150)

Any suggestions to what I'm doing wrong here?

create table User (
UserId int unsigned not null auto_increment primary key,
UserForname varchar(50) not null
);

create table Property (
PropertyId int unsigned not null auto_increment primary key,
PropertyOwner int not null,
index idxPropertyOwner using hash (PropertyOwner),
foreign key (PropertyOwner) references User(UserId) on delete cascade
);

"Jeff" <it_consultant1@hotmail.com.NOSPAM> wrote in news:1uidnR8xTp-
yJS3YRVnzvQ@telenor.com:


> When trying to create the Property table (see below) I get this error
> message: ERROR 1005 (HY000): Can't create table '.\demo\property.frm'
> (errno: 150)
>
> create table User (
> UserId int unsigned not null auto_increment primary key,
> UserForname varchar(50) not null
> );
>
> create table Property (
> PropertyId int unsigned not null auto_increment primary key,
> PropertyOwner int not null,
> index idxPropertyOwner using hash (PropertyOwner),
> foreign key (PropertyOwner) references User(UserId) on delete cascade
> );

Because of the foreign key constraint, the datatypes of User.UserId and
Property.PropertyOwner should be *exactly* the same.

Why are you using a hash-index? As far as I can tell, it's only supported
for MEMORY tables. And MEMORY tables cannot enforce referential
integrity.


--
felix

Jeff wrote:

> Hey
>
> MySql 5.0
>
> When trying to create the Property table (see below) I get this error
> message:
> ERROR 1005 (HY000): Can't create table '.\demo\property.frm' (errno: 150)
>
> Any suggestions to what I'm doing wrong here?
>
> create table User (
> UserId int unsigned not null auto_increment primary key,
> UserForname varchar(50) not null
> );
>
> create table Property (
> PropertyId int unsigned not null auto_increment primary key,
> PropertyOwner int not null,
> index idxPropertyOwner using hash (PropertyOwner),
> foreign key (PropertyOwner) references User(UserId) on delete cascade
> );

You have a mismatch on the field types. User.Userid is an unsigned int,
whereas Property.PropertyOwner is just int.

In article <1uidnR8xTp-yJS3YRVnzvQ@telenor.com>,
"Jeff" <it_consultant1@hotmail.com.NOSPAM> writes:

> Hey
> MySql 5.0

> When trying to create the Property table (see below) I get this error
> message:
> ERROR 1005 (HY000): Can't create table '.\demo\property.frm' (errno: 150)

> Any suggestions to what I'm doing wrong here?

> create table User (
> UserId int unsigned not null auto_increment primary key,
> UserForname varchar(50) not null
> );

> create table Property (
> PropertyId int unsigned not null auto_increment primary key,
> PropertyOwner int not null,
> index idxPropertyOwner using hash (PropertyOwner),
> foreign key (PropertyOwner) references User(UserId) on delete cascade
> );

In foreign keys, the types of the referencing and the referenced
column must be the same. You've got INT vs. INT UNSIGNED.

"Harald Fuchs" <hf1110x@protecting.net> wrote in message
news:puzm8fm9he.fsf@srv.protecting.net...
> In article <1uidnR8xTp-yJS3YRVnzvQ@telenor.com>,
> "Jeff" <it_consultant1@hotmail.com.NOSPAM> writes:
>
>> Hey
>> MySql 5.0
>
>> When trying to create the Property table (see below) I get this error
>> message:
>> ERROR 1005 (HY000): Can't create table '.\demo\property.frm' (errno: 150)
>
>> Any suggestions to what I'm doing wrong here?
>
>> create table User (
>> UserId int unsigned not null auto_increment primary key,
>> UserForname varchar(50) not null
>> );
>
>> create table Property (
>> PropertyId int unsigned not null auto_increment primary key,
>> PropertyOwner int not null,
>> index idxPropertyOwner using hash (PropertyOwner),
>> foreign key (PropertyOwner) references User(UserId) on delete cascade
>> );
>
> In foreign keys, the types of the referencing and the referenced
> column must be the same. You've got INT vs. INT UNSIGNED.

thanks to you all, it was the datatype mismatch