找到你要的答案

Q:MySQL table creation error #1005

Q:MySQL表创建错误# 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 
)

查询:

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 ; 

错误消息:

错误:# 1005无法创建表,phonetracker \ location_share。FRM的(错误:150)

其他的表:

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 
)
answer1: 回答1:

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 ; 

HTTP:/ / sqlfiddle。COM / #!9 / c3d04 / 1

您的查询没有问题,只是需要排序,如果使用外键,父表必须首先声明。

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 ; 
mysql