mysql> create table country(
-> country_id smallint unsigned not null auto_increment,
-> country varchar(50) not null,
-> last_update timestamp not null default current_timestamp on update current_timestamp,
-> primary key(country_id)
-> )engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.29 sec)
mysql> create table city(
-> city_id smallint unsigned not null auto_increment,
-> city varchar(50) not null,
-> country_id smallint unsigned not null,
-> last_update timestamp not null default current_timestamp on update current_timestamp,
-> primary key(city_id),
-> key idx_fk_country_id(country_id),
-> constraint 'fk_city_country' foreign key(country_id) references country(country_id) on delete restrict on update cascade
-> )
-> engine=innodb default charset=utf8;
ERROR 1064 (42000): 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 ''fk_city_country' foreign key(country_id) references country(country_id) on dele' at line 8
经过测试,问题出在constraint 'fk_city_country' ,这貌似是oracle的写法,MySQL中不需要这个.
看去掉之后,执行成功.
mysql>
mysql> create table city(
-> city_id smallint unsigned not null auto_increment,
-> city varchar(50) not null,
-> country_id smallint unsigned not null,
-> last_update timestamp not null default current_timestamp on update current_timestamp,
-> primary key(city_id),
-> key idx_fk_country_id(country_id),
-> foreign key(country_id) references country(country_id) on delete restrict on update cascade
-> )
-> engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.20 sec)
mysql> show create table city;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| city | CREATE TABLE `city` (
`city_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`city` varchar(50) NOT NULL,
`country_id` smallint(5) unsigned NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`city_id`),
KEY `idx_fk_country_id` (`country_id`),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)
mysql>
看到这里,发现create table 语句中可以定义CONSTRAINT `city_ibfk_1`啊,仔细想想错误的create 语句与执行成功之后的show create语句之间的差别不就是
CONSTRAINT `city_ibfk_1` 和 CONSTRAINT 'city_ibfk_1' 一个'一个`,看到这里恍然大悟,`是分隔符加在外键名称上是可以的,而且定义外键名字不需要加单引号和双引号,直接命名即可,所以加了'的会报错!
不信再试试看,不加`也不加'试试看效果:
mysql> create table city4(
-> city_id smallint unsigned not null auto_increment,
-> city varchar(50) not null,
-> country_id smallint unsigned not null,
-> last_update timestamp not null default current_timestamp on update current_timestamp,
-> primary key(city_id),
-> key idx_fk_country_id4(country_id),
-> CONSTRAINT city1_ibfk_4 foreign key(country_id) references country(country_id) on update cascade
-> )
-> engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.13 sec)
看到了,搞定成功了,所以在define外键的时候,外键名称不可以添加单引号和双引号。
分享到:
相关推荐
check the manual that corresponds to your MySQL server version for the right syntax to use near ‘order’ at line 1 数据库报错如下: Error querying database. Cause: java.sql.SQLSyntaxErrorException: ...
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 ‘(0) NOT NULL, endTime datetime(0) NOT NULL,avaliableTime int(8) ...
ERROR 1064 (42000): 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 'mysqltump -u root -p sa > D:/sa.sql' at line ...
### The error occurred while setting parameters ### SQL: SELECT * FROM Domain_System WHERE domain = ? LIMIT 1 ### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'zup.Domain_...
本文实例讲述了mysql报错:...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 ‘type=InnoDB’ at line 1 二、解决方案
mysqldump: Couldn’t execute ‘SET OPTION SQL_QUOTE_SHOW_CREATE=1’: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use ...
前几天帮同事解决一个案例,在主从复制环境下,从库上的MySQL版本号是5.5.5,... Last_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for t
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 'identified by 'password' with grant option' mysql 5和mysql 8都有...
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server versio····这句话对于咱们并不陌生,无非就是多了“,”之类的问题。但是你如果无意之中添加了一个mysql中的...
Err] 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 ‘`company_id` int) RETURNS varchar(20) CHARSET utf8 ...
命令成功执行。操作发生错误,相关的信息为... Check the manual that corresponds to your MySQL server version for the right syntax to use near ‘old_password(‘123456′)’ at line 1命令为:set password for
在搭建springmvc框架时,底层使用... 代码如下:[13-04-13 19:11:37.190] {resin-60} You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right synt
check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1 MySQL Errno : 1064 Message : MySQL Query Error 在未修复栏目前,添加信息出错的问题。...
check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by "12345678"' at line 1 的错误,原因是新版本mysql版本把将创建账户和赋予权限分开了。 ...