douan7601
douan7601
2017-12-10 00:00

插入记录时出错无法添加或更新子行:外键约束失败

已采纳

I have created tables in MYSQL as follows

Author Table

CREATE TABLE `author` (
`AuthorId` varchar(25) NOT NULL,
`AuthorName` varchar(50) NOT NULL,
 PRIMARY KEY (`AuthorId`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1

Books Table

 CREATE TABLE `books` (
 `DocId` int(11) NOT NULL,
 `ISBN` varchar(13) NOT NULL,
 PRIMARY KEY (`DocId`),
 KEY `DocId` (`DocId`),
 CONSTRAINT `books_ibfk_1` FOREIGN KEY (`DocId`) REFERENCES `document` 
 (`DocId`)
 ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1

Document Table

 CREATE TABLE `document` (
 `DocId` int(11) NOT NULL,
 `Title` varchar(50) NOT NULL,
 `PublishDate` date DEFAULT NULL,
 `AuthorId` varchar(10) DEFAULT NULL,
 `DocType` varchar(10) DEFAULT NULL,
 PRIMARY KEY (`DocId`),
 KEY `AuthorId` (`AuthorId`),    
 CONSTRAINT `document_ibfk_3` FOREIGN KEY (`AuthorId`) REFERENCES `author` 
 (`AuthorId`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1

I made a server for my PHP website on XAMPP and am using mysql but when I try inserting the values in Document table I get

error while inserting the recordsCannot add or update a child row: a foreign 
key constraint fails (`librarydb`.`document`, CONSTRAINT `document_ibfk_3` 
FOREIGN KEY (`AuthorId`) REFERENCES `author` (`AuthorId`))

How do I resolve this issue?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • dqg2269 dqg2269 4年前

    Your linked fields author.AuthorId and document.AuthorId are not defined the same way.
    From the docs:

    Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same. The length of string types need not be the same.

    So make them both varchar(10) NOT NULL - or whatever you need.

    点赞 评论 复制链接分享