首页 文章

MySql错误1005 erno:105无法创建表

提问于
浏览
0
drop table if exists patient;
drop table if exists pan;
drop table if exists device;
drop table if exists sensor;
drop table if exists actuator;
drop table if exists wears;
drop table if exists connects;
drop table if exists period;
drop table if exists reading;
drop table if exists setting;
drop table if exists lives;
drop table if exists municipality;

create table patient
    (patient_name varchar(255),
     patient_number integer(10),
     patient_address varchar(255),
     primary key(patient_number));

create table pan
    (pan_domain varchar(255),
     pan_phone integer(9),
     primary key(pan_domain));

create table device
    (device_snum integer(10),
     device_manuf varchar(255),
     device_description varchar(255),
     primary key(device_snum, device_manuf));

create table municipality
    (municipality_nut4code integer(10),
     municipality_name varchar(255),
     primary key(municipality_nut4code));

create table period
    (period_start time,
     period_end time,
     primary key(period_start, period_end));

create table sensor
    (sensor_snum integer(10),
     sensor_manuf varchar(255),
     sensor_units varchar(255),
     primary key(sensor_snum, sensor_manuf),
     foreign key(sensor_snum) references device(device_snum),
     foreign key(sensor_manuf) references device(device_manuf));

create table actuator
    (actuator_snum integer(10),
     actuator_manuf varchar(255),
     actuator_units varchar(255),
     primary key(actuator_snum, actuator_manuf),
     foreign key(actuator_snum) references device(device_snum),
     foreign key(actuator_manuf) references device(device_manuf));

create table wears
    (wears_start time,
     wears_end time,
     wears_patient integer(10),
     wears_pan varchar(255),
     primary key(wears_start, wears_end, wears_patient),
     foreign key(wears_start) references period(period_start),
     foreign key(wears_end) references period(period_end),
     foreign key(wears_patient) references patient(patient_number),
     foreign key(wears_pan) references pan(pan_domain));   

create table connects
    (connects_start time,
     connects_end time,
     connects_snum integer(10),
     connects_manuf varchar(255),
     connects_pan varchar(255),
     primary key(connects_start, connects_end, connects_snum,    connects_manuf),
     foreign key(connects_start) references period(period_start),
     foreign key(connects_end) references period(period_end),
     foreign key(connects_snum) references device(device_snum),
     foreign key(connects_manuf) references device(device_manuf),
     foreign key(connects_pan) references pan(pan_domain));

 create table reading
    (reading_snum integer(10),
     reading_manuf varchar(255),
     reading_datetime date,
     reading_value numeric(10,2),
     primary key(reading_snum, reading_manuf, reading_datetime),
     foreign key(reading_snum) references sensor(sensor_snum),
     foreign key(reading_manuf) references sensor(sensor_manuf));

create table setting
    (setting_snum integer(10),
     setting_manuf varchar(255),
     setting_datetime date,
     setting_value numeric(10,2),
     primary key(setting_snum, setting_manuf, setting_datetime),
     foreign key(setting_snum) references actuator(actuator_snum),
     foreign key(setting_manuf) references actuator(actuator_manuf));

create table lives
    (lives_start time,
     lives_end time,
     lives_patient integer(10),
     lives_municipality integer(10),
     primary key(lives_start, lives_end, lives_patient),
     foreign key(lives_start) references period(period_start),
     foreign key(lives_end) references period(period_end),
     foreign key(lives_patient) references patient(patient_number),
     foreign key(lives_municipality) references    municipality(municipality_nut4code));

当我尝试在MySQL上创建这个数据库时,我得到一个错误,说ERROR 1005(HY000)无法创建表*****(错误号:150) . 无法创建的表是具有外键的表 . 我已经检查了数据类型,并且引用与先前创建的表的主键匹配 . 我没有得到错误 . 有人可以帮忙吗?

1 回答

  • 0

    由于您在引用的表中有复合键,因此您也应该创建外键复合键 . 例如:

    create table sensor
        (sensor_snum integer(10),
         sensor_manuf varchar(255),
         sensor_units varchar(255),
         primary key(sensor_snum, sensor_manuf),
         foreign key(sensor_snum, sensor_manuf) references device(device_snum, device_manuf));
    

    此外,如果您有两个具有相同外键的表,则需要为外键指定名称 . 所有外键都需要有不同的名称,如果你没有明确地给出一个名字,MySQL只根据外键中的列生成名称,你最终会得到重复 .

相关问题