mysql - MySQL 无法让 FOREIGN KEY 命令正常工作

Question

我发现的所有内容都说明了我试图拉的两个项目在某种程度上与他们的父母不同，但他们不是

CREATE TABLE Servers (
serverId VARCHAR(255) NOT NULL PRIMARY KEY,
serverName VARCHAR(255) NOT NULL,
serverOwnerId VARCHAR(255) NOT NULL,
serverOwnerName VARCHAR(255) NOT NULL,
paidRole BOOLEAN NOT NULL
);

CREATE TABLE ServerConfigurable (
serverId VARCHAR(255) NOT NULL PRIMARY KEY,
serverName VARCHAR(255) NOT NULL,
cmdPrefix VARCHAR(16) DEFAULT '!',
globalUngate BOOLEAN NOT NULL,
globalAutoungate BOOLEAN NOT NULL,
autoungateWebhook VARCHAR(255) NOT NULL,
ungateWebhook VARCHAR(255) NOT NULL,
embedHexcolor VARCHAR(32) NOT NULL,
webhookName VARCHAR(255) DEFAULT 'Gate Bot',
webhookImage VARCHAR(255) DEFAULT 'https://imgur.com/a/eIULgfA',
footerMessage VARCHAR(255) DEFAULT 'Gate Bot V1.0.1',
footerImage VARCHAR(255) DEFAULT 'https://imgur.com/a/eIULgfA',
FOREIGN KEY Servers REFERENCES Servers(serverId),
FOREIGN KEY Servers REFERENCES Servers(serverName)
);

我得到的错误是

ERROR 1064 (42000)：您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以在第 14 行的 'REFERENCES Servers(serverId), FOREIGN KEY serverName REFERENCES Servers(serverNa' 附近使用正确的语法

标签： mysqlforeign-keys

Answer 1

每个被引用的列都必须有一个索引

所以你可以做

CREATE TABLE Servers (
serverId VARCHAR(255) NOT NULL PRIMARY KEY,
serverName VARCHAR(255) NOT NULL,
serverOwnerId VARCHAR(255) NOT NULL,
serverOwnerName VARCHAR(255) NOT NULL,
paidRole BOOLEAN NOT NULL,
KEY(serverName)
);

CREATE TABLE ServerConfigurable (
serverId VARCHAR(255) NOT NULL PRIMARY KEY,
serverName VARCHAR(255) NOT NULL,
cmdPrefix VARCHAR(16) DEFAULT '!',
globalUngate BOOLEAN NOT NULL,
globalAutoungate BOOLEAN NOT NULL,
autoungateWebhook VARCHAR(255) NOT NULL,
ungateWebhook VARCHAR(255) NOT NULL,
embedHexcolor VARCHAR(32) NOT NULL,
webhookName VARCHAR(255) DEFAULT 'Gate Bot',
webhookImage VARCHAR(255) DEFAULT 'https://imgur.com/a/eIULgfA',
footerMessage VARCHAR(255) DEFAULT 'Gate Bot V1.0.1',
footerImage VARCHAR(255) DEFAULT 'https://imgur.com/a/eIULgfA',
FOREIGN KEY (serverId) REFERENCES Servers(serverId),
FOREIGN KEY (serverName) REFERENCES Servers(serverName)
);

或者做这样的事情

CREATE TABLE Servers (
serverId VARCHAR(255) NOT NULL PRIMARY KEY,
serverName VARCHAR(255) NOT NULL,
serverOwnerId VARCHAR(255) NOT NULL,
serverOwnerName VARCHAR(255) NOT NULL,
paidRole BOOLEAN NOT NULL,
KEY(serverId,serverName)
);

CREATE TABLE ServerConfigurable (
serverId VARCHAR(255) NOT NULL PRIMARY KEY,
serverName VARCHAR(255) NOT NULL,
cmdPrefix VARCHAR(16) DEFAULT '!',
globalUngate BOOLEAN NOT NULL,
globalAutoungate BOOLEAN NOT NULL,
autoungateWebhook VARCHAR(255) NOT NULL,
ungateWebhook VARCHAR(255) NOT NULL,
embedHexcolor VARCHAR(32) NOT NULL,
webhookName VARCHAR(255) DEFAULT 'Gate Bot',
webhookImage VARCHAR(255) DEFAULT 'https://imgur.com/a/eIULgfA',
footerMessage VARCHAR(255) DEFAULT 'Gate Bot V1.0.1',
footerImage VARCHAR(255) DEFAULT 'https://imgur.com/a/eIULgfA',
FOREIGN KEY (serverId,serverName) REFERENCES Servers(serverId,serverName)
);