Fabio Viola Fabio Viola - 15 days ago 7
MySQL Question

My sql table receives an error message

I am having trouble setting up these two tables. I receive the following error message related to the second table:

1064 - 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 'COMMENT 'FOREIGN KEY referencing to cliCommand record',
example TEXT COMMENT ' at line 4

CREATE TABLE cliCommand
(
cliCommandId INT(11) PRIMARY KEY NOT NULL COMMENT 'Auto_Increment primary key used for interal purposes' AUTO_INCREMENT,
code ENUM('CI', 'LX', 'MO', 'SQ', 'UX', 'WI') NOT NULL COMMENT 'Command unique code shared with users to enable search using this code.',
os ENUM('CiscoIOS', 'Linux', 'macOS', 'SQL', 'Unix', 'Windows') NOT NULL COMMENT 'Operating Systems this command works with',
title TEXT NOT NULL COMMENT 'command short description/title to be displayed in search result listing along with command code and os',
tag TEXT COMMENT 'any other meta data associated with command'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Keeps all cli commands with meta data & description';


CREATE TABLE commandExample
(
commandExampleId INT(11) PRIMARY KEY NOT NULL COMMENT 'Autoincrement key for internal purposes' AUTO_INCREMENT,
`_cliCommandId` INT(11) REFERENCES cliCommand(cliCommandId) COMMENT 'FOREIGN KEY referencing to cliCommand record',
example TEXT COMMENT 'an example associated with this command, there could be multiple examples associated with a command, each as a new record in this table'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Keeps examples, if any, for a command to be displayed';

Answer

Use an explicit constraint definition. I don't think that MySQL supports in-line foreign key references:

CREATE TABLE commandExample
(
  commandExampleId INT(11) PRIMARY KEY NOT NULL COMMENT 'Autoincrement key for internal purposes' AUTO_INCREMENT,
  `_cliCommandId` INT(11) COMMENT 'FOREIGN KEY referencing to cliCommand record',
  example TEXT COMMENT 'an example associated with this command, there could be multiple examples associated with a command, each as a new record in this table',
  constraint fk_clicommand_clicommand foreign key (_cliCommandId) REFERENCES cliCommand(cliCommandId) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Keeps examples, if any, for a command to be displayed';

Here is an example in SQL Fiddle.

Comments