ERROR 1005 (HY000): Can't create table 'tracker_phonetracker.location_share' (errno: -1)

Question

I am facing this problem in this query .

CREATE TABLE location_share ( 
    circle_id INT ,
    user_id INT , 
    location_sharing_id  INT , 
    PRIMARY KEY (user_id ,circle_id  ) , 
    CONSTRAINT fkcircle1 FOREIGN KEY (circle_id) REFERENCES circle(id) ON DELETE CASCADE ON UPDATE CASCADE , 
    CONSTRAINT fkuser1 FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE ON UPDATE CASCADE , 
    CONSTRAINT fksharing_policy FOREIGN KEY (location_sharing_id) REFERENCES share(id) ON DELETE CASCADE ON UPDATE CASCADE 
) ENGINE = INNODB

Mysql shows me error 1005 :

ERROR 1005 (HY000): Can't create table 'tracker_phonetracker.location_share' (errno: -1)

Other tables on which this query is dependent are :

Table : circle

CREATE TABLE circle (
    id INT AUTO_INCREMENT PRIMARY KEY , 
    name varchar(35)
) ENGINE=INNODB

Table : user

CREATE TABLE user (
    id INT AUTO_INCREMENT PRIMARY KEY , 
    contact_no VARCHAR(25) UNIQUE NOT NULL, 
    email VARCHAR(50) UNIQUE  NOT     NULL, 
    first_name  VARCHAR(25) NOT NULL , 
    last_name VARCHAR(25) , 
    device_id VARCHAR(250)NOT NULL  , 
    image_path VARCHAR(180) , 
    password VARCHAR(30) NOT NULL , 
    latitude VARCHAR(18) , 
    longitude VARCHAR(18) 
) ENGINE=INNODB

Table : share

CREATE TABLE share (
    id INT AUTO_INCREMENT PRIMARY KEY , 
    policy VARCHAR(6) UNIQUE NOT NULL 
)

Can anybody plz tell me what's wrong with my query .

Thanks !!

There might be something useful in this question for you: http://stackoverflow.com/questions/4063141/mysql-foreign-key-error-1005-errno-150 — DeadChex, Jul 23 '15 at 13:15

score 0 · Answer 1 · answered Jul 23 '15 at 12:58

0

From the manual: http://dev.mysql.com/doc/refman/5.0/en/innodb-error-codes.html

1005 (ER_CANT_CREATE_TABLE)

Cannot create table. If the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed. If the error message refers to error −1, table creation probably failed because the table includes a column name that matched the name of an internal InnoDB table.

This is caused by using user you can fix this by escaping references to user:

 REFERENCES `user`(id)

answered Jul 23 '15 at 12:58

Jim

22,354
6
52
80

Thanks for answer . You mean to say should i remove statement referencing user table . I think i did not get you . – Deepak Rathore Jul 23 '15 at 13:08
@DeepakRathore No need to remove, Just escape the `user` by adding `\`` around it. – Jim Jul 23 '15 at 13:15
i tried using escaping character , but it still shows me same error 1005 – Deepak Rathore Jul 23 '15 at 13:28
@DeepakRathore What version of MySQL are you running on? The above works for me? Have you created your other tables first? – Jim Jul 23 '15 at 14:16

HashSu · Answer 2 · 2015-07-23T13:13:00.053

0

CREATE TABLE `location_share` (
  `circle_id` int(11) NOT NULL DEFAULT '0',
  `user_id` int(11) NOT NULL DEFAULT '0',
  `location_sharing_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`user_id`,`circle_id`),
  KEY `fkcircle1` (`circle_id`),
  KEY `fksharing_policy` (`location_sharing_id`),
  CONSTRAINT `fkcircle1` FOREIGN KEY (`circle_id`) REFERENCES `circle` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fksharing_policy` FOREIGN KEY (`location_sharing_id`) REFERENCES `share` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fkuser1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

edited Jul 23 '15 at 13:13

answered Jul 23 '15 at 13:00

HashSu

1,507
1
13
13

There is not wrong in it. Please run the query which I give and let me know it works' I have replicated ur tables structure in my DB and it worked – HashSu Jul 23 '15 at 13:12
hi i tried your query but it giving me now error #1005 can't create table – Deepak Rathore Jul 23 '15 at 13:26
check this link http://dev.mysql.com/doc/refman/5.0/en/innodb-error-codes.html i didn't face issue in creating ur table in my DB.... is ur reference tables exists? – HashSu Jul 23 '15 at 13:27

score 0 · Answer 3 · answered Aug 13 '17 at 20:53

0

SOLUTION (works for me):

Rename the table in the script. Ex: wp_users to wp_users1
Run the script. No error.
Rename the table with Operation in MySQL (or your Database Engine).
Ready.

Regards!

answered Aug 13 '17 at 20:53

Martin Acosta

51
3

ERROR 1005 (HY000): Can't create table 'tracker_phonetracker.location_share' (errno: -1)

3 Answers3