CODEWITHSUNDEEP
mysqlsql
Mint.K
Mint.K

Reputation: 899

MySQL Foreign Key syntax error

CREATE TABLE DEPARTMENT ( Dname VARCHAR(15) NOT NULL, 
Dnumber INT NOT NULL, 
Mgr_ssn CHAR(9) NOT NULL,
 Mgr_start_Date DATE, 
PRIMARY KEY(Dnumber), 
UNIQUE(Dname), 
FOREIGN KEY(Mgr_ssn) REFERENCES EMPLOYEE (Ssn) 
ON DELETE SET DEFAULT ON UPDATE CASCADE);

I am a beginner in MySQL. The command above gives me this:

Cannot add foreign key constraint

I have tried SHOW ENGINE INNODB STATUS; I checked EMPLOYEE.Ssn's data type. Please help.

Edit: My EMPLOYEE table

| employee | CREATE TABLE `employee` (
  `Fname` varchar(10) DEFAULT NULL,
  `Minit` char(1) DEFAULT NULL,
  `Lname` varchar(10) DEFAULT NULL,
  `Ssn` char(9) NOT NULL,
  `Bdate` date DEFAULT NULL,
  `Address` varchar(40) DEFAULT NULL,
  `Sex` char(1) DEFAULT NULL,
  `Salary` int(11) DEFAULT NULL,
  `Super_ssn` char(9) DEFAULT NULL,
  `Dno` int(11) DEFAULT NULL,
  PRIMARY KEY (`Ssn`),
  KEY `Super_ssn` (`Super_ssn`),
  CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`Super_ssn`) REFERENCES `EMPLOYEE` (`Ssn`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

Upvotes: 0

Views: 268

Answers (1)

racraman
racraman

Reputation: 5034

According to the MySQL docs at https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html , you can't use SET DEFAULT with INNODB :

SET DEFAULT: This action is recognized by the MySQL parser, but both InnoDB and NDB reject table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT clauses

Upvotes: 1

Related Questions