MySQL FOREIGN KEY Constraints Syntax

You can not use ADD CONSTRAINT in a CREATE TABLE declaration. Declare your constraint after creating the table or in the CREATE TABLE.

First solution: Add the constraint in CREATE TABLE

CREATE TABLE IF NOT EXISTS `pay_grades` (
  `pay_grade_id` int(20) NOT NULL,
  `pay_scale_id` tinyint(4) NOT NULL,
  `name` varchar(100) NOT NULL,
  `basic_salary` decimal(10,2) NOT NULL,  
  `status` int(2) NOT NULL DEFAULT '1',  
   PRIMARY KEY (`pay_grade_id`),
   INDEX (`pay_scale_id`, `pay_grade_id`),  
   FOREIGN KEY (`pay_scale_id`) REFERENCES `pay_scales`(`id`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Second solution: Alter table to add the constraint

Create your table without the constraint, and then add your constraint as follow:

ALTER TABLE `pay_grades` 
ADD CONSTRAINT `pay_scale_id` FOREIGN KEY REFERENCES `pay_scales`(`id`) 
ON UPDATE CASCADE ON DELETE RESTRICT;

MySQL documentation for foreign keys declaration.

It seems the difference in order of table creation. First create primary key table than create the table of foreign key.

CREATE TABLE IF NOT EXISTS `pay_scales` (
`id` tinyint(4) NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `pay_grades` (
    `pay_grade_id` int(20) NOT NULL,
  `pay_scale_id` tinyint(4) NOT NULL,
  `name` varchar(100) NOT NULL,
  `basic_salary` decimal(10,2) NOT NULL,  
  `status` int(2) NOT NULL DEFAULT '1',  
   PRIMARY KEY (`pay_grade_id`),
   INDEX (`pay_scale_id`, `pay_grade_id`),  
   ADD CONSTRAINT `fk_pay_grade_scale` FOREIGN KEY `pay_scale_id` REFERENCES `pay_scales`(`id`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;