CREATE TABLE t_employee (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(20) NOT NULL,
password varchar(16) NOT NULL DEFAULT '123456',
sex varchar(2) DEFAULT NULL,
birthday date DEFAULT NULL,
email varchar(30) DEFAULT NULL,
position varchar(16) DEFAULT NULL,
hire_date date NOT NULL,
manager_id varchar(16) DEFAULT NULL,
experience varchar(200) DEFAULT '',
major varchar(11) DEFAULT NULL,
degree varchar(16) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8; create table t_user
(
id VARCHAR(16) not null,
password VARCHAR(16) not null,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE t_user
ADD FOREIGN KEY (id) REFERENCES t_employee(id);
DROP TABLE t_plan;
CREATE TABLE t_plan (
id int(11) NOT NULL auto_increment,
name varchar(16) NOT NULL,
description varchar(128) default NULL,
task_id int(11) NOT NULL,
clerk_id varchar(16) NOT NULL,
state varchar(5) NOT NULL,
start datetime,
exp_end datetime NOT NULL,
end datetime,
feedback varchar(5) NOT NULL,
feedback_msg varchar(200) default '',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE t_task;
CREATE TABLE t_task (
id int(11) NOT NULL auto_increment,
name varchar(16) NOT NULL,
description varchar(128) default NULL,
start datetime,
end datetime,
exp_start datetime NOT NULL,
exp_end datetime NOT NULL,
manager_id varchar(16) NOT NULL,
clerk_id varchar(16) NOT NULL,
state varchar(5) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 前两天语句都没有问题。到第三条语句 就提示
1005-Can't create table '.\test\#sql-d34_f.frm'(errno:150)
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(20) NOT NULL,
password varchar(16) NOT NULL DEFAULT '123456',
sex varchar(2) DEFAULT NULL,
birthday date DEFAULT NULL,
email varchar(30) DEFAULT NULL,
position varchar(16) DEFAULT NULL,
hire_date date NOT NULL,
manager_id varchar(16) DEFAULT NULL,
experience varchar(200) DEFAULT '',
major varchar(11) DEFAULT NULL,
degree varchar(16) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8; create table t_user
(
id VARCHAR(16) not null,
password VARCHAR(16) not null,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE t_user
ADD FOREIGN KEY (id) REFERENCES t_employee(id);
DROP TABLE t_plan;
CREATE TABLE t_plan (
id int(11) NOT NULL auto_increment,
name varchar(16) NOT NULL,
description varchar(128) default NULL,
task_id int(11) NOT NULL,
clerk_id varchar(16) NOT NULL,
state varchar(5) NOT NULL,
start datetime,
exp_end datetime NOT NULL,
end datetime,
feedback varchar(5) NOT NULL,
feedback_msg varchar(200) default '',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE t_task;
CREATE TABLE t_task (
id int(11) NOT NULL auto_increment,
name varchar(16) NOT NULL,
description varchar(128) default NULL,
start datetime,
end datetime,
exp_start datetime NOT NULL,
exp_end datetime NOT NULL,
manager_id varchar(16) NOT NULL,
clerk_id varchar(16) NOT NULL,
state varchar(5) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 前两天语句都没有问题。到第三条语句 就提示
1005-Can't create table '.\test\#sql-d34_f.frm'(errno:150)
这句吧
experience varchar(200) DEFAULT null,
ADD FOREIGN KEY (id) REFERENCES t_employee(id);
应该是这句的问题:t_user表中的主键ID 不能设置为t_employee表的外键
如果此字段设置为其他表的外键,那么这列就允许重复,与表本身设置的主键不是互相矛盾了?
(
id int not null,
password VARCHAR(16) not null,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE t_user
ADD FOREIGN KEY (id) REFERENCES t_employee(id);