"FOREIGN" is not valid in this position, expecting ')'-CodePudding

I am trying to create two tables in a database on MySQL:

Here is the parent table: CREATE DATABASE FULLMETAL_ALCHEMIST; USE FULLMETAL_ALCHEMIST;

CREATE TABLE CHARACTERS
(NAME VARCHAR(50) PRIMARY KEY,
AGE INT,
BIRTHYEAR DATE,
BIRTHPLACE VARCHAR(50),
HAIR_COLOUR VARCHAR(50),
EYE_COLOUR VARCHAR(50),
ALCHEMY_STATUS BOOLEAN,
MILITARY_STATUS BOOLEAN);

And I want the column NAME to be a foreign key in this table:

CREATE TABLE ALCHEMY
(ALCHEMY_ALIAS VARCHAR(50) PRIMARY KEY,
NAME VARCHAR(50) FOREIGN KEY REFERENCES CHARACTERS(NAME));

For some reason, this method of adding a foreign key is not working, but this method works:

NAME VARCHAR(50),
FOREIGN KEY (NAME) REFERENCES CHARACTERS(NAME))

I'd like to understand why my first method does not work. Various websites tutorials have used both methods, but for some reason, only the second one is working for me.

CodePudding user response：

For MySQL the 1st syntax is wrong. 2nd is the correct syntax for MySQL.

Refer this link: https://www.w3schools.com/sql/sql_foreignkey.asp