SQL语句: alter table article add constraint a_u_id foreign key(user_id) references auser(id);报的错误:错误 SQL 查询: ALTER TABLE article ADD CONSTRAINT a_u_id FOREIGN KEY ( user_id ) REFERENCES auser( id ) MySQL 返回: #1005 - Can't create table '.\test\#sql-c0_1e.frm' (errno: 150)
show create table article ; show create table auser;贴出来看一下。
CREATE TABLE `article` ( `id` int(11) NOT NULL auto_increment, `content` varchar(20) NOT NULL, `user_id` int(8) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;CREATE TABLE `auser` ( `id` mediumint(8) NOT NULL auto_increment, `username` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
字段类型不一致。ALTER TABLE article modify `user_id` mediumint(8) NOT NULL; 改成相同的然后再 ALTER TABLE article ADD CONSTRAINT a_u_id FOREIGN KEY ( user_id ) REFERENCES auser( id ) ;mysql> ALTER TABLE article modify `user_id` mediumint(8) NOT NULL; Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0mysql> ALTER TABLE article ADD CONSTRAINT a_u_id FOREIGN KEY ( user_id ) REFEREN CES auser( id ) ; Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0mysql>
把字段类型改成一致以后,还报下面的错误:错误 SQL 查询: ALTER TABLE article ADD CONSTRAINT a_u_id FOREIGN KEY ( user_id ) REFERENCES auser(id ) MySQL 返回: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REFEREN CES auser( id )' at line 1
贴一下你的现在的 show create table 语句和你的 alter table 语句。估计是你自己写错了。
show create table article;CREATE TABLE `article` ( `id` int(11) NOT NULL auto_increment, `content` varchar(20) NOT NULL, `user_id` mediumint(8) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;show create table auser;CREATE TABLE `auser` ( `id` mediumint(8) NOT NULL auto_increment, `username` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;执行下面的语句: ALTER TABLE article ADD CONSTRAINT a_u_id FOREIGN KEY ( user_id ) REFERENCES auser( id ) 报的错误:错误 SQL 查询: ALTER TABLE article ADD CONSTRAINT a_u_id FOREIGN KEY ( user_id ) REFERENCES auser( id ) MySQL 返回: #1452 - Cannot add or update a child row: a foreign key constraint fails (`test/#sql-c0_6c`, CONSTRAINT `a_u_id` FOREIGN KEY (`user_id`) REFERENCES `auser` (`id`))
你表中是不是有数据了啊? 估计是有记录不符合要求!select * from article where user_id not in (select id from auser);看一下。
alter table article add constraint a_u_id foreign key(user_id) references auser(id);报的错误:错误
SQL 查询: ALTER TABLE article ADD CONSTRAINT a_u_id FOREIGN KEY ( user_id ) REFERENCES auser( id ) MySQL 返回: #1005 - Can't create table '.\test\#sql-c0_1e.frm' (errno: 150)
show create table auser;贴出来看一下。
`id` int(11) NOT NULL auto_increment,
`content` varchar(20) NOT NULL,
`user_id` int(8) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;CREATE TABLE `auser` (
`id` mediumint(8) NOT NULL auto_increment,
`username` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
改成相同的然后再
ALTER TABLE article ADD CONSTRAINT a_u_id FOREIGN KEY ( user_id ) REFERENCES auser( id ) ;mysql> ALTER TABLE article modify `user_id` mediumint(8) NOT NULL;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> ALTER TABLE article ADD CONSTRAINT a_u_id FOREIGN KEY ( user_id ) REFEREN
CES auser( id ) ;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql>
SQL 查询: ALTER TABLE article ADD CONSTRAINT a_u_id FOREIGN KEY ( user_id ) REFERENCES auser(id
) MySQL 返回: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REFEREN
CES auser( id )' at line 1
`id` int(11) NOT NULL auto_increment,
`content` varchar(20) NOT NULL,
`user_id` mediumint(8) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;show create table auser;CREATE TABLE `auser` (
`id` mediumint(8) NOT NULL auto_increment,
`username` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;执行下面的语句:
ALTER TABLE article ADD CONSTRAINT a_u_id FOREIGN KEY ( user_id ) REFERENCES auser( id ) 报的错误:错误
SQL 查询: ALTER TABLE article ADD CONSTRAINT a_u_id FOREIGN KEY ( user_id ) REFERENCES auser( id ) MySQL 返回: #1452 - Cannot add or update a child row: a foreign key constraint fails (`test/#sql-c0_6c`, CONSTRAINT `a_u_id` FOREIGN KEY (`user_id`) REFERENCES `auser` (`id`))
估计是有记录不符合要求!select * from article
where user_id not in (select id from auser);看一下。