MySQL: Why doesn't this db creation script work?

Question

For some reason, this MySQL fails:

CREATE SCHEMA IF NOT EXISTS `partB` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `partB`;

CREATE TABLE Employees ( ssn CHAR(11),
Name CHAR(30),
mlot INTEGER,
PRIMARY KEY(ssn))
ENGINE = InnoDB;

CREATE TABLE Dept_Mgr ( did INTEGER,
dname CHAR(20),
ssn CHAR(11) NOT NULL,
PRIMARY KEY (did),
FOREIGN KEY (ssn) REFERENCES Employees
ON DELETE NO ACTION)
ENGINE = InnoDB;

It gives the error:

ERROR 1005 (HY000): Can't create table partb.dept_mgr (errno: 150)

What can be causing this?

Hi Rosarch, was this answered to your satisfaction?

Roland Bouman
– Roland Bouman

2010-02-26 09:37:07 +00:00
Commented Feb 26, 2010 at 9:37 — Roland Bouman
– Roland Bouman, Commented Feb 26, 2010 at 9:37

Michael Pilat · Accepted Answer · 2010-02-25 01:54:38Z

2

You have to specify the column(s) in the foreign table for the key:

FOREIGN KEY (ssn) REFERENCES Employees (ssn) ...

answered Feb 25, 2010 at 1:54

Michael Pilat

6,52029 silver badges31 bronze badges

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

Comments

Roland Bouman · Accepted Answer · 2010-02-25 01:53:55Z

1

This command:

SHOW ENGINE INNODB STATUS;

is your friend when you have trouble creating foreign keys. Output (abridged)

------------------------
LATEST FOREIGN KEY ERROR
------------------------
100225  2:51:42 Error in foreign key constraint of table test/dept_mgr:
FOREIGN KEY (ssn) REFERENCES Employees
ON DELETE NO ACTION)
ENGINE = InnoDB:
Syntax error close to:

ON DELETE NO ACTION)
ENGINE = InnoDB

If you change your statement to:

CREATE TABLE Dept_Mgr ( 
    did INTEGER,
    dname CHAR(20),
    ssn CHAR(11) NOT NULL,
    PRIMARY KEY (did),
    FOREIGN KEY (ssn) REFERENCES Employees(ssn)
) engine = innodb;

it does work.

answered Feb 25, 2010 at 1:53

Roland Bouman

32.1k8 gold badges69 silver badges69 bronze badges

Comments

Cryophallion · Accepted Answer · 2010-02-25 01:53:39Z

0

You only have the foreign key referencing the table, not the column.

Try: references Employee.ssn

answered Feb 25, 2010 at 1:53

Cryophallion

7045 silver badges10 bronze badges

Collectives™ on Stack Overflow

MySQL: Why doesn't this db creation script work?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related