CODEWITHSUNDEEP
mysqlforeign-keyscreate-table
zaman sarker
zaman sarker

Reputation: 1

MySQL FOREIGN KEY Constraints Syntax

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 'ADD CONSTRAINT fk_pay_grade_scale FOREIGN KEY pay_scale_id REFERENCES pay_s' at line 11

What is the problem?

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;


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;

Upvotes: -1

Views: 1482

Answers (2)

Faizan Younus
Faizan Younus

Reputation: 803

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;

Upvotes: 0

Mistalis
Mistalis

Reputation: 18279

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.

Upvotes: 4

Related Questions