I am trying to add Category_Name as a foreign key to the ITEM table. Category_Name exists in CATEGORY table and this is what i get:
我正在尝试将Category_Name添加为项目表的外键。CATEGORY table中有Category_Name,我得到的是:
mysql> use acmeonline;
Database changed
mysql> show tables;
+----------------------+
| Tables_in_acmeonline |
+----------------------+
| category |
| item |
+----------------------+
2 rows in set (0.00 sec)
mysql> describe item;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| Item_Number | int(11) | NO | PRI | 0 | |
| Item_Name | varchar(35) | YES | | NULL | |
| Model_Num | varchar(15) | YES | | NULL | |
| Description | varchar(255) | YES | | NULL | |
| Price | double(8,2) | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
5 rows in set (0.07 sec)
mysql> describe category;
+------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| Category_Name | varchar(35) | NO | PRI | | |
| ShippingPerPound | double(4,2) | YES | | NULL | |
| DiscountAllowed | char(1) | YES | | NULL | |
+------------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> ALTER TABLE item
-> ADD CONSTRAINT fk_category_name
-> FOREIGN KEY(Category_Name)
-> REFERENCES Category(Category_Name);
ERROR 1072 (42000): Key column 'Category_Name' doesn't exist in table
How do I fix this? Please bear with me because I did look at some sites on how to do it, but I get the same results.
我怎么修复这个?请容忍我,因为我确实看了一些如何做的网站,但我得到了相同的结果。
2 个解决方案
#1
3
You need to add the CATEGORY_NAME column on ITEM TABLE, or map the foreign key to another existing column in ITEM.
您需要在项目表上添加CATEGORY_NAME列,或者将外键映射到项目中的另一个现有列。
Either:
:
ALTER TABLE ITEM
ADD CATEGORY_NAME VARCHAR(35) NOT NULL;
ALTER TABLE ITEM
ADD CONSTRAINT FK_CATEGORY_NAME
FOREIGN KEY (CATEGORY_NAME)
REFERENCES CATEGORY (CATEGORY_NAME);
OR
或
ALTER TABLE ITEM
ADD CONSTRAINT FK_CATEGORY_NAME
FOREIGN KEY (SOME_OTHER_EXISTING_COLUMN)
REFERENCES CATEGORY (CATEGORY_NAME);
#2
1
You must first add the column in a separate alter statement:
您必须首先将列添加到另一个alter语句中:
alter table item add column category_name varchar(35);
Foreign key constraints only create a relationship between tables - they don't also create the column(s) involved.
外键约束只创建表之间的关系——它们也不创建涉及的列。
#1
3
You need to add the CATEGORY_NAME column on ITEM TABLE, or map the foreign key to another existing column in ITEM.
您需要在项目表上添加CATEGORY_NAME列,或者将外键映射到项目中的另一个现有列。
Either:
:
ALTER TABLE ITEM
ADD CATEGORY_NAME VARCHAR(35) NOT NULL;
ALTER TABLE ITEM
ADD CONSTRAINT FK_CATEGORY_NAME
FOREIGN KEY (CATEGORY_NAME)
REFERENCES CATEGORY (CATEGORY_NAME);
OR
或
ALTER TABLE ITEM
ADD CONSTRAINT FK_CATEGORY_NAME
FOREIGN KEY (SOME_OTHER_EXISTING_COLUMN)
REFERENCES CATEGORY (CATEGORY_NAME);
#2
1
You must first add the column in a separate alter statement:
您必须首先将列添加到另一个alter语句中:
alter table item add column category_name varchar(35);
Foreign key constraints only create a relationship between tables - they don't also create the column(s) involved.
外键约束只创建表之间的关系——它们也不创建涉及的列。