为什么 MySQL 创建表语句失败？
使用 mySQLAdmin 工具，我尝试创建一个表。该工具生成 SQL 语句，然后重新报告“无法创建表”，但没有其他线索表明它是什么错误！
这是：
CREATE TABLE `C121535_vubridge`.`Products` (
`pr_ID` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`pr_Name` VARCHAR(45) NOT NULL,
`pr_Type` VARCHAR(2) NOT NULL COMMENT 'H=Hand Series V=VuBridge software E=Event Subs S=Sponsoring',
`pr_AuthorID` INTEGER UNSIGNED COMMENT '= m_ID (for Bridge Hand Series',
`pr_SponsorID` INTEGER UNSIGNED NOT NULL,
`pr_DateCreation` DATETIME NOT NULL,
`pr_Price` FLOAT NOT NULL,
`pr_DescriptionText` TEXT,
`pr_Description` VARCHAR(245),
PRIMARY KEY (`pr_ID`),
CONSTRAINT `FK_prAuthor` FOREIGN KEY `FK_prAuthor` (`pr_AuthorID`)
REFERENCES `Members` (`m_ID`)
ON DELETE SET NULL
ON UPDATE NO ACTION,
CONSTRAINT `FK_Sponsor` FOREIGN KEY `FK_Sponsor` (`pr_SponsorID`)
REFERENCES `Members` (`m_ID`)
ON DELETE SET NULL
ON UPDATE NO ACTION
) ENGINE = InnoDB;
有人可以帮忙吗？
Using mySQLAdmin tool, I try to create a table. The tool generates the SQL statement, and then replorts a "Can't create table" with no other clue on what error it is!
Here it is :
CREATE TABLE `C121535_vubridge`.`Products` (
`pr_ID` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`pr_Name` VARCHAR(45) NOT NULL,
`pr_Type` VARCHAR(2) NOT NULL COMMENT 'H=Hand Series V=VuBridge software E=Event Subs S=Sponsoring',
`pr_AuthorID` INTEGER UNSIGNED COMMENT '= m_ID (for Bridge Hand Series',
`pr_SponsorID` INTEGER UNSIGNED NOT NULL,
`pr_DateCreation` DATETIME NOT NULL,
`pr_Price` FLOAT NOT NULL,
`pr_DescriptionText` TEXT,
`pr_Description` VARCHAR(245),
PRIMARY KEY (`pr_ID`),
CONSTRAINT `FK_prAuthor` FOREIGN KEY `FK_prAuthor` (`pr_AuthorID`)
REFERENCES `Members` (`m_ID`)
ON DELETE SET NULL
ON UPDATE NO ACTION,
CONSTRAINT `FK_Sponsor` FOREIGN KEY `FK_Sponsor` (`pr_SponsorID`)
REFERENCES `Members` (`m_ID`)
ON DELETE SET NULL
ON UPDATE NO ACTION
) ENGINE = InnoDB;
Can someone help?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果我省略外键引用，则 CREATE TABLE 对我有用：
...所以我倾向于相信
C121535_vubridge.MEMBERS尚不存在。需要在运行 PRODUCTS 表的 CREATE TABLE 语句之前创建C121535_vubridge.MEMBERS。The CREATE TABLE works for me if I omit the foreign key references:
...so I'm inclined to believe that
C121535_vubridge.MEMBERSdoes not already exist.C121535_vubridge.MEMBERSneeds to be created before the CREATE TABLE statement for the PRODUCTS table is run.只需拆分创建表并一次尝试一部分即可。这样您应该能够识别出失败的单行。
Just split up the create table and try one part at the time. This way you should be able to identify a single line that it fails on.
我在参考手册中确实注意到，如果为 CONSTRAINT 子句给出了符号子句（在您的情况下，每个子句中 FOREIGN KEY 之前的反引号字符串，
FK_prAuthor和FK_Sponsorcode>) 在数据库中必须是唯一的。他们是吗？如果没有，可以省略该符号，InnoDB 将自动分配。同样，您的 FK 引用的表可能不具有此 create 语句所期望的结构。
I do note in the reference manual that if a symbol subclause is given for the CONSTRAINT clause (in your case, the back-quoted strings before FOREIGN KEY in each clause,
FK_prAuthorandFK_Sponsor) have to be unique over the database. Are they? If not, that symbol can be omitted and InnoDB will assign then automatically.Similarly, the tables your FKs refer to may not have the structure that this create statement expects.