I trying to create foreign key in MySQL using MySQL workbench. But there is an error
$ Executing:
ALTER TABLE `project_course`.`attendance`
ADD CONSTRAINT `FK_Student`
FOREIGN KEY ('idStudent')
REFERENCES `project_course`.`student` ('userid')
ON DELETE NO ACTION
ON UPDATE NO ACTION;
Operation failed: There was an error while applying the SQL script to the database.
ERROR 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 ''idStudent')
REFERENCES `project_course`.`student` ('userid')
ON DELETE NO A' at line 3
SQL Statement:
ALTER TABLE `project_course`.`attendance`
ADD CONSTRAINT `FK_Student`
FOREIGN KEY ('idStudent')
REFERENCES `project_course`.`student` ('userid')
ON DELETE NO ACTION
ON UPDATE NO ACTION
The problem is with the quotes (on PC it is located around Enter key). You have used them instead of backticks (on PC it is located under Esc key).
ALTER TABLE `project_course`.`attendance`
ADD CONSTRAINT `FK_Student`
FOREIGN KEY (`idStudent`) # Change them here around `idStudent`
REFERENCES `project_course`.`student` (`userid`) # and here around `userid`
ON DELETE NO ACTION
ON UPDATE NO ACTION;
Musa's answer is correct, but the explanation leaves something to desire. Here's a better one.
You used single quote chars for the column names in the foreign key and references clauses. Single quotes denote a string in MySQL, however at these positions a column reference is required, i.e. an identifier. In your case you wouldn't need quotes at all, because all chars in your identifier are allowed when not using quotes (see the identifier rules for MySQL). However it's a good idea to always quote if you create your query from user input or other generated data (to avoid sql injection and to ensure it works regardless of the used reference name).
Usually quoting means to put an identifier within backticks, which always works. Alternatively, you can use "double quotes", but only if your current SQL mode includes the ANSI_QUOTES mode. Otherwise double quotes also denote strings (like single quotes). It's a bit risky to use double quotes if you cannot ensure ANSI_QUOTES mode is set.
Copy and paste this code in your Mysql script editor and run. You will have two tables categories and products these tables having cat_id as foreign key.
CREATE DATABASE IF NOT EXISTS dbdemo;
USE dbdemo;
CREATE TABLE categories(
cat_id int not null auto_increment primary key,
cat_name varchar(255) not null,
cat_description text
) ENGINE=InnoDB;
CREATE TABLE products(
prd_id int not null auto_increment primary key,
prd_name varchar(355) not null,
prd_price decimal,
cat_id int not null,
FOREIGN KEY fk_cat(cat_id)
REFERENCES categories(cat_id)
ON UPDATE CASCADE
ON DELETE RESTRICT
)ENGINE=InnoDB;
