我正在使用MySQL
我想加外键，但有个错误
错误代码：1215。无法添加外键约束
我使用的查询是
ALTER TABLE person ADD CONSTRAINT person_company_FK FOREIGN KEY (company_id) REFERENCES company(conpany_id) ;
为公司创建表语句是
CREATE TABLE `company` (
`Company_id` int(11) NOT NULL AUTO_INCREMENT,
`Company_name` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
`Address_id` int(11) NOT NULL DEFAULT '0',
`Employee_range_id` int(11) NOT NULL DEFAULT '0',
`Location_type_id` int(11) NOT NULL DEFAULT '0',
`Ownership_id` int(11) NOT NULL DEFAULT '0',
`Annual_sales_id` int(11) NOT NULL DEFAULT '0',
`Sic_id` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`Company_id`)
) ENGINE=InnoDB AUTO_INCREMENT=131135 DEFAULT CHARSET=latin1
为person创建表
CREATE TABLE `person` (
`person_id` int(11) NOT NULL AUTO_INCREMENT,
`FirstName` varchar(255) DEFAULT NULL,
`MiddleName` varchar(255) DEFAULT NULL,
`LastName` varchar(255) DEFAULT NULL,
`Suffix` varchar(255) DEFAULT NULL,
`Number_YearsAnAgent` double DEFAULT NULL,
`BD_RIARep` varchar(255) DEFAULT NULL,
`RepCRD` double DEFAULT NULL,
`BrokerDealerAffiliation` varchar(255) DEFAULT NULL,
`RIAAffiliation` varchar(255) DEFAULT NULL,
`AgentLicenseType_Health` varchar(255) DEFAULT NULL,
`Number_StateLicenses_Health` double DEFAULT NULL,
`AgentLicenseType_Life` varchar(255) DEFAULT NULL,
`Number_StateLicenses_Life` double DEFAULT NULL,
`AgentLicenseType_PropertyCasualty` varchar(255) DEFAULT NULL,
`Number_StateLicenses_PropertyCasualty` double DEFAULT NULL,
`AgentLicenseType_VariableProducts` varchar(255) DEFAULT NULL,
`Number_StateLicenses_VariableProducts` double DEFAULT NULL,
`SellsRetirementPlanProducts` varchar(255) DEFAULT NULL,
`DBAName` varchar(255) DEFAULT NULL,
`DateOfBirth_full` datetime DEFAULT NULL,
`Gender` varchar(255) DEFAULT NULL,
`office_id` int(11) NOT NULL,
`company_id` int(11) NOT NULL DEFAULT '1',
PRIMARY KEY (`person_id`),
KEY `person_Office_FK` (`office_id`),
CONSTRAINT `person_Office_FK` FOREIGN KEY (`office_id`) REFERENCES `office` (`Office_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2890997 DEFAULT CHARSET=latin1
我使用show engine innodb status来检查发生了什么，但不知道响应是
=====================================
2014-08-08 09:26:17 1754 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 25 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 10665 srv_active, 0 srv_shutdown, 244985 srv_idle
srv_master_thread log flush and writes: 255566
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1747533
OS WAIT ARRAY INFO: signal count 1792563
Mutex spin waits 1149803, rounds 6105609, OS waits 107205
RW-shared spins 5797082, rounds 112260476, OS waits 1570178
RW-excl spins 64167, rounds 1886808, OS waits 42317
Spin rounds per wait: 5.31 mutex, 19.36 RW-shared, 29.40 RW-excl
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2014-08-08 09:26:11 1754 Error in foreign key constraint of table trillium_data/#sql-510_460:
FOREIGN KEY (company_id) REFERENCES company(conpany_id):
Cannot resolve column name close to:
)
------------
TRANSACTIONS
------------
Trx id counter 256766
Purge done for trx's n:o < 256306 undo n:o < 0 state: running but idle
History list length 1559
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 256325, not started
MySQL thread id 1120, OS thread handle 0x1754, query id 56155 localhost 127.0.0.1 root init
SHOW ENGINE innodb STATUS
---TRANSACTION 0, not started
MySQL thread id 1119, OS thread handle 0x87c, query id 56126 localhost 127.0.0.1 root cleaning up
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
28498265 OS file reads, 1898344 OS file writes, 194654 OS fsyncs
0.84 reads/s, 16384 avg bytes/read, 0.92 writes/s, 0.40 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 296, seg size 298, 80598 merges
merged operations:
insert 2226499, delete mark 1017126, delete 33664
discarded operations:
insert 46998, delete mark 0, delete 0
Hash table size 69257, node heap has 1 buffer(s)
0.56 hash searches/s, 2.64 non-hash searches/s
---
LOG
---
Log sequence number 68198765705
Log flushed up to 68198765705
Pages flushed up to 68198765705
Last checkpoint at 68198765705
0 pending log writes, 0 pending chkp writes
80801 log i/o's done, 0.12 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 17006592; in additional pool allocated 0
Dictionary memory allocated 574008
Buffer pool size 1024
Free buffers 767
Database pages 256
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 35874501, not young 3712939362
0.00 youngs/s, 0.00 non-youngs/s
Pages read 28455284, created 855601, written 1702138
0.84 reads/s, 0.28 creates/s, 0.56 writes/s
Buffer pool hit rate 946 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 256, unzip_LRU len: 0
I/O sum[35]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread id 3420, state: sleeping
Number of rows inserted 52721481, updated 22524132, deleted 173611, read 1642731205
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
最佳答案
在company_id中应该有conpany_id而不是REFERENCES company(...)
ALTER TABLE person ADD CONSTRAINT person_company_FK
FOREIGN KEY (company_id) REFERENCES company(company_id) ;
只是打字：）
关于mysql - 不允许添加外键，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/25204661/