情况我说明一下,我这里有三个表,orders,products,vendors,三个表,三个表的列定义分别如下:
mysql> show columns from orders;
+-------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| product_id | int(10) unsigned | NO | MUL | NULL | |
| customer_id | int(10) unsigned | NO | MUL | NULL | |
| create_time | datetime | NO | | NULL | |
+-------------+------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
mysql> show columns from products;
+-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(45) | NO | | NULL | |
| vendor_id | int(10) unsigned | NO | MUL | NULL | |
| price | decimal(10,2) | NO | | NULL | |
+-----------+------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)mysql> show columns from vendors;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(45) | NO | | NULL | |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
其中,products.id是orders.product_id的外键。vendors.id是products.vendor_id的外键。mysql> select * from vendors;
+------+----------+
| id | name |
+------+----------+
| 1001 | seagate |
| 1002 | ibm |
| 1003 | wd |
| 1004 | hitachi |
| 1005 | intel |
| 1006 | amd |
| 1007 | via |
| 1008 | nvidia |
| 1009 | soundmax |
| 1010 | kingmax |
| 1011 | kingston |
| 1012 | samsung |
| 1013 | msi |
| 1014 | asus |
| 1015 | s3 |
+------+----------+
15 rows in set (0.00 sec)
我在对表products执行插入的时候执行如下命令:
mysql> insert into products_old(name,vendor_id,price) values("wd500G",1003,210.0
0);
ERROR 1054 (42S22): Unknown column 'products.id' in 'field list'
mysql> insert into products_old(id,name,vendor_id,price) values(2013,"wd500G",10
03,210.00);
ERROR 1054 (42S22): Unknown column 'products.id' in 'field list'
mysql>
两种插入方式都报错。而且错的莫名其妙。
接下来我把原来的表改成products_old复制了一个新表。操作如下:
mysql> alter table products rename to products_old;
Query OK, 0 rows affected (0.13 sec)mysql> create table products like products_old;
Query OK, 0 rows affected (0.26 sec)mysql> insert into products select * from products_old;
Query OK, 11 rows affected (0.02 sec)
Records: 11 Duplicates: 0 Warnings: 0
然后再次执行插入:
mysql> insert into products(name,vendor_id,price) values("wd500G",1003,210.00);
Query OK, 1 row affected (0.01 sec)mysql> insert into products_old(name,vendor_id,price) values("wd500G",1003,210.0
0);
ERROR 1054 (42S22): Unknown column 'products.id' in 'field list'
结果还是原来的那个表有问题,根据那个有问题的表复制出来的表反倒没问题。已经检查过表的外键关系还是正常的,我实在找不出来以前的那个表问题出在哪里?
附上两个表的创建语句:
show create table products;| products | CREATE TABLE `products` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL,
`vendor_id` int(10) unsigned NOT NULL,
`price` decimal(10,2) NOT NULL,
PRIMARY KEY (`id`),
KEY `id` (`vendor_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2014 DEFAULT CHARSET=utf8 |
show create table products_old;| products_old | CREATE TABLE `products_old` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL,
`vendor_id` int(10) unsigned NOT NULL,
`price` decimal(10,2) NOT NULL,
PRIMARY KEY (`id`),
KEY `id` (`vendor_id`),
CONSTRAINT `id` FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2014 DEFAULT CHARSET=utf8 |查看orders表的外键约束仍然指向旧表。
感觉貌似问题处在主键跟外键的约束上,那位能帮我解释一下这个问题的原因啊?可能的话,给我详细讲一下主键和外键在约束的时候的注意事项。谢啦。
mysql> show columns from orders;
+-------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| product_id | int(10) unsigned | NO | MUL | NULL | |
| customer_id | int(10) unsigned | NO | MUL | NULL | |
| create_time | datetime | NO | | NULL | |
+-------------+------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
mysql> show columns from products;
+-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(45) | NO | | NULL | |
| vendor_id | int(10) unsigned | NO | MUL | NULL | |
| price | decimal(10,2) | NO | | NULL | |
+-----------+------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)mysql> show columns from vendors;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(45) | NO | | NULL | |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
其中,products.id是orders.product_id的外键。vendors.id是products.vendor_id的外键。mysql> select * from vendors;
+------+----------+
| id | name |
+------+----------+
| 1001 | seagate |
| 1002 | ibm |
| 1003 | wd |
| 1004 | hitachi |
| 1005 | intel |
| 1006 | amd |
| 1007 | via |
| 1008 | nvidia |
| 1009 | soundmax |
| 1010 | kingmax |
| 1011 | kingston |
| 1012 | samsung |
| 1013 | msi |
| 1014 | asus |
| 1015 | s3 |
+------+----------+
15 rows in set (0.00 sec)
我在对表products执行插入的时候执行如下命令:
mysql> insert into products_old(name,vendor_id,price) values("wd500G",1003,210.0
0);
ERROR 1054 (42S22): Unknown column 'products.id' in 'field list'
mysql> insert into products_old(id,name,vendor_id,price) values(2013,"wd500G",10
03,210.00);
ERROR 1054 (42S22): Unknown column 'products.id' in 'field list'
mysql>
两种插入方式都报错。而且错的莫名其妙。
接下来我把原来的表改成products_old复制了一个新表。操作如下:
mysql> alter table products rename to products_old;
Query OK, 0 rows affected (0.13 sec)mysql> create table products like products_old;
Query OK, 0 rows affected (0.26 sec)mysql> insert into products select * from products_old;
Query OK, 11 rows affected (0.02 sec)
Records: 11 Duplicates: 0 Warnings: 0
然后再次执行插入:
mysql> insert into products(name,vendor_id,price) values("wd500G",1003,210.00);
Query OK, 1 row affected (0.01 sec)mysql> insert into products_old(name,vendor_id,price) values("wd500G",1003,210.0
0);
ERROR 1054 (42S22): Unknown column 'products.id' in 'field list'
结果还是原来的那个表有问题,根据那个有问题的表复制出来的表反倒没问题。已经检查过表的外键关系还是正常的,我实在找不出来以前的那个表问题出在哪里?
附上两个表的创建语句:
show create table products;| products | CREATE TABLE `products` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL,
`vendor_id` int(10) unsigned NOT NULL,
`price` decimal(10,2) NOT NULL,
PRIMARY KEY (`id`),
KEY `id` (`vendor_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2014 DEFAULT CHARSET=utf8 |
show create table products_old;| products_old | CREATE TABLE `products_old` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL,
`vendor_id` int(10) unsigned NOT NULL,
`price` decimal(10,2) NOT NULL,
PRIMARY KEY (`id`),
KEY `id` (`vendor_id`),
CONSTRAINT `id` FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2014 DEFAULT CHARSET=utf8 |查看orders表的外键约束仍然指向旧表。
感觉貌似问题处在主键跟外键的约束上,那位能帮我解释一下这个问题的原因啊?可能的话,给我详细讲一下主键和外键在约束的时候的注意事项。谢啦。
mysql> insert into products_old(name,vendor_id,price) values("wd500G",1003,210.0
0);
ERROR 1054 (42S22): Unknown column 'products.id' in 'field list'
mysql> insert into products_old(id,name,vendor_id,price) values(2013,"wd500G",10
03,210.00);
ERROR 1054 (42S22): Unknown column 'products.id' in 'field list'
mysql>
这一段是不是贴错了