MySQL Foreign Key syntax error

Question

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 |

You sure that FOREIGHN key is spelled with an h? or is that a typo when you pasted — Youn Elan
– Youn Elan, Commented Apr 11, 2017 at 3:06
Thanks. I fixed it, but now it's giving me "cannot add foreign key constraint" — Mint.K
– Mint.K, Commented Apr 11, 2017 at 3:09
"By default, table aliases are case sensitive on Unix, but not so on Windows or OS X. ... it is best to adopt a consistent convention, such as always creating and referring to databases and tables using lowercase names. This convention is recommended for maximum portability and ease of use." dev.mysql.com/doc/refman/5.7/en/… — spencer7593
– spencer7593, Commented Apr 11, 2017 at 3:39

racraman · Accepted Answer · 2017-04-11 03:28:22Z

1

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

answered Apr 11, 2017 at 3:28

racraman

5,0441 gold badge18 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mint.K Over a year ago

Only 'ON DELETE CASCADE' works. 'ON DELETE SET NULL' or 'ON DELETE SET DEFAULT' do not work. Interesting.

racraman Over a year ago

I'm guessing RESTRICT would also work, so basically looks like you can't update the child record when the master record is deleted.

spencer7593 Over a year ago

@MintK: to clarify your comment: ON DELETE SET NULL is allowed with InnoDB as long as the foreign key column allows for null values (i.e. if the column is not declared with NOT NULL constraint.)

Collectives™ on Stack Overflow

MySQL Foreign Key syntax error

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related