Search code examples
mysqlforeign-keys

MySQL trouble getting FOREIGN KEY command to work

everything i find says something about the two items im trying to pull are different from their parent in some way but they arentthough

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)
);

and the error i get is

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REFERENCES Servers(serverId), FOREIGN KEY serverName REFERENCES Servers(serverNa' at line 14

Solution

  • Every referencd column has to have an index

    So you can do

    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)
);

    Or Do something like this

    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)
);