CODEWITHSUNDEEP
mysql
Neeru Jain
Neeru Jain

Reputation: 153

MySQL table creation error #1005

Query:

CREATE TABLE location_share (
    id INT AUTO_INCREMENT PRIMARY KEY ,   
    user_id INT ,   
    circle_id INT ,   
    location_sharing_id  INT ,    
    CONSTRAINT fkuser1 FOREIGN KEY (user_id)
        REFERENCES user(id) ON DELETE CASCADE ON UPDATE CASCADE,    
    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  
) ENGINE = INNODB ;

Error message:

error : #1005 - Can't create table '.\phonetracker\location_share.frm' (errno: 150)

Other tables:

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

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

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

Upvotes: 3

Views: 103

Answers (1)

Alex
Alex

Reputation: 17289

http://sqlfiddle.com/#!9/c3d04/1

Nothing wrong with your query, just need to be ordered, if you use FOREIGN KEY the parent table must be declared first.

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;

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

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

CREATE TABLE location_share (
    id INT AUTO_INCREMENT PRIMARY KEY ,   
    user_id INT ,   
    circle_id INT ,   
    location_sharing_id  INT ,    
    CONSTRAINT fkuser1 FOREIGN KEY (user_id)
        REFERENCES user(id) ON DELETE CASCADE ON UPDATE CASCADE,    
    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  
) ENGINE = INNODB ;

Upvotes: 1

Related Questions