Professional Web Applications Themes

newbie: error 1005, cannot create table - MySQL

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 );...

  1. #1

    Default 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 Guest

  2. #2

    Default Re: newbie: error 1005, cannot create table

    "Jeff" <com.NOSPAM> wrote in news:1uidnR8xTp-
    com:

     

    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
    Felix Guest

  3. #3

    Default Re: newbie: error 1005, cannot create table


    Jeff wrote:
     

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

    Captain Guest

  4. #4

    Default Re: newbie: error 1005, cannot create table

    In article <com>,
    "Jeff" <com.NOSPAM> writes:
     
     
     
     
     

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

  5. #5

    Default Re: newbie: error 1005, cannot create table


    "Harald Fuchs" <net> wrote in message
    news:protecting.net... 




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

    thanks to you all, it was the datatype mismatch


    Jeff Guest

Similar Threads

  1. Create table overwrites existing table in mssql
    By bmyers in forum Coldfusion Database Access
    Replies: 2
    Last Post: November 25th, 10:00 AM
  2. newbie many to many table
    By news2003@wanadoo.es in forum MySQL
    Replies: 3
    Last Post: August 25th, 06:17 PM
  3. Replies: 11
    Last Post: June 2nd, 04:01 AM
  4. How to create a table with dynamic table name
    By cathy wang in forum Informix
    Replies: 3
    Last Post: July 17th, 12:46 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139