java - 错误号 : 150 "Foreign key constraint is incorrectly formed-mysql

Question

我有两个表，名称分别是 patient_info 和 report。但是当我尝试创建这两个表时，只创建了 patient_info 而没有创建 report 表。它显示以下错误。为什么会这样？java.sql.SQLException: Can't create table sampath.report (errno: 150 "Foreign key constraint is incorrectly formed")

MySql代码:

private static final String patient_info="CREATE TABLE patient_info(Id_person int(11) NOT null AUTO_INCREMENT,"+
        "ref varchar(10),first_name varchar(100),"+
        "last_name varchar(100),age int(5) not null,"+
        "dob date,address varchar(255),status varchar(20),sample varchar(20),report_no varchar(100),"+
        "primary key(Id_person,report_no))";

private static final String report="CREATE TABLE report(report_no varchar(100),"+
        "Id_person int(11) not null,"+
        "sample varchar(100),report varchar(100),"+
                "foreign key(report_no) references patient_info(report_no) ON DELETE CASCADE ON UPDATE CASCADE)";

Answer 1

我的代码如下..

private static final String patient_info="CREATE TABLE patient_info(Id_person int(11) NOT null AUTO_INCREMENT,"+
        "ref varchar(10),first_name varchar(100),"+
        "last_name varchar(100),age int(5) not null,"+
        "dob date,address varchar(255),status varchar(20),sample varchar(20),report_no varchar(100),"+
        "primary key(Id_person,report_no))";

private static final String mobile="CREATE TABLE mobile(Id_person int(11) NOT null ,"+
        "mobile_no VARCHAR(10),"+
        "home_no varchar(10),"+
        "primary key(Id_person),foreign key(Id_person) references patient_info(Id_person) ON DELETE CASCADE ON UPDATE CASCADE)";

private static final String report="CREATE TABLE report(report_no varchar(100),"+
        "Id_person int(11) not null,"+
        "sample varchar(100),report varchar(100),"+
                "foreign key (report_no) references patient_info.report_no ON DELETE CASCADE ON UPDATE CASCADE)";

private static final String lipid="create table lipid("+
        "report_no varchar(100) not null,"+
        "triglycerides int(11),chol_HDL int(11),"+
        "primary key(report_no),foreign key(report_no) references report(report_no) ON DELETE CASCADE ON UPDATE CASCADE)";

private static final String cholesterol_table="CREATE TABLE cholesterol_table("+
        "report_no varchar(100),chol_total int(11),"+
        "primary key(report_no),foreign key(report_no) references report(report_no) ON DELETE CASCADE ON UPDATE CASCADE)";

private static final String wbc="CREATE TABLE wbc("+
        "report_no varchar(100),wbc float(11),neutrophiles int(11),"+
        "lymphocytes int(11),eosinophiles int(11),monocytes int(11),"+
        "basophiles int(11),abnormal_cell int(11),primary key(report_no),foreign key(report_no) references report(report_no) ON DELETE CASCADE ON UPDATE CASCADE)";

private static final String haemoglobin="CREATE TABLE haemoglobin("+
        "report_no varchar(100),haemoglobin float(5,2),primary key(report_no),foreign key(report_no) references report(report_no) ON DELETE CASCADE ON UPDATE CASCADE)";

private static final String platelet_count="CREATE TABLE platelet_count("+
        "report_no varchar(100),platelet_count int(11),primary key(report_no),foreign key(report_no) references report(report_no) ON DELETE CASCADE ON UPDATE CASCADE)";

private static final String fbs="CREATE table fbs("+
        "report_no varchar(100),fs_clucose float(5,2),primary key(report_no),foreign key(report_no) references report(report_no) ON DELETE CASCADE ON UPDATE CASCADE)";

private static final String urine="CREATE TABLE urine(report_no varchar(100),colour varchar(20),apperance varchar(20),"+
        "SG float(5,2),PH float(5,2),protein varchar(20),glucose varchar(20),"+
        "ketone_bodies varchar(20),nitrite varchar(20),bilirubin varchar(20),"+
        "urobilinogen varchar(20),pus_cells varchar(20),red_cells varchar(20),"+
        "epithelial_cells varchar(20),casts varchar(20),crystals varchar(20),primary key(report_no),"
        +"foreign key(report_no) references report(report_no) ON DELETE CASCADE ON UPDATE CASCADE)";


public int createdb(){
    String sql="create database sampath";
    try{
    Class.forName(Dbstatic.DRIVER);
    System.out.println("Driver load ok");

    conn=DriverManager.getConnection(Dbstatic.URL,Dbstatic.USER,Dbstatic.PASSWORD);
    System.out.println("Connection ok");

    st=conn.createStatement();
    System.out.println("statement ok");

    result=st.executeUpdate(sql);
    System.out.println("Executed");
    System.out.println("New database created");
    result=19901228;   // value for tableCreator function 

    }catch(ClassNotFoundException cfe){
        System.out.println("Error Dbcreator-createDb(): MYSQL driver not found");
        System.out.println(cfe);
    }
    catch(SQLException sqlex){
        result=sqlex.getErrorCode();
        System.out.println("Error Dbcreator-createDb() :");
        System.out.println("Error : "+sqlex+"\nError code : "+result);   
    }
    catch(Exception ex){

        System.out.println("Error Dbcreator-createDb() : "+ex);

    }

    return result;
    }



private void tableCreator(int value,String use,String table_sql){
    if(value==1007||value==19901228){
        try{
           result_table=st.executeUpdate(use);

           result_table=st.executeUpdate(table_sql); 
        }
        catch(MySQLSyntaxErrorException ex){
            System.out.println("Error Dbcreator-tableCreator() : "+ex);
        }
        catch(Exception ex){
            System.out.println("Error Dbcreator-tableCreator() : "+ex);
        }

    }else{
        System.out.println("Database not exsist");
    }
}



public void table_maker(int value){

    tableCreator(value,use,patient_info);
    tableCreator(value,use,mobile);
    tableCreator(value,use,report);
    tableCreator(value,use,lipid);
    tableCreator(value,use,cholesterol_table);
    tableCreator(value,use,wbc);
    tableCreator(value,use,haemoglobin);
    tableCreator(value,use,platelet_count);
    tableCreator(value,use,fbs);
    tableCreator(value,use,urine);

}
}

异常如下

Error Dbcreator-tableCreator() : java.sql.SQLException: Can't create table sampath.report (errno: 150 "外键约束形成不正确")错误 Dbcreator-tableCreator():java.sql.SQLException:无法创建表 sampath。lipid(错误号:150“外键约束形成不正确”)错误 Dbcreator-tableCreator():java.sql.SQLException:无法创建表 sampath。cholesterol_table(错误号:150“外键约束形成不正确”)错误 Dbcreator-tableCreator():java.sql.SQLException:无法创建表 sampath。wbc(错误号:150“外键约束形成不正确”)错误 Dbcreator-tableCreator():java.sql.SQLException:无法创建表 sampath。haemoglobin(错误号:150“外键约束形成不正确”)错误 Dbcreator-tableCreator():java.sql.SQLException:无法创建表 sampath。platelet_count(错误号:150“外键约束形成不正确”)错误 Dbcreator-tableCreator():java.sql.SQLException:无法创建表 sampath。fbs(错误号:150“外键约束形成不正确”)错误 Dbcreator-tableCreator() : java.sql.SQLException: 无法创建表 sampath.urine (errno: 150 “外键约束不正确”)