Creation Failed: Key column 'name' doesn't exist in table:

Question

I am trying to create a series of tables using Foreign Keys of Primary Keys from other tables, however I always get the error

Table Creation Failed: Key column 'PACId' doesn't exist in table:

Here is the first case it appears:

CREATE TABLE PlantAreaCodes(
  PACId INT NOT NULL AUTO_INCREMENT,
  AreaCode INT,
  AreaName CHAR(32),
  Comments TEXT,
  PRIMARY KEY (PACId)
);

CREATE TABLE MajorEquipment(
  MEId INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (MEId),
  FOREIGN KEY (PACId) 
  REFERENCES PlantAreaCodes(PACId)
);

Is it to do with the syntax of the foreign key, or is it because the PACId is still empty, and cannot be?

score 2 · Accepted Answer · edited Feb 25 '14 at 03:04

2

It is not that PACId is empty. You haven't declared it in MajorEquipment.

Try using this definition for the second table:

CREATE TABLE MajorEquipment(
  MEId INT NOT NULL AUTO_INCREMENT,
  PACId INT, -- Added this column
  PRIMARY KEY (MEId),
  FOREIGN KEY (PACId) 
  REFERENCES PlantAreaCodes(PACId)
);

Here is a SQL Fiddle.

edited Feb 25 '14 at 03:04

Bohemian

412,405
93
575
722

answered Feb 25 '14 at 03:02

Gordon Linoff

1,242,037
58
646
786

Thanks I did not realise that. Too focussed on the issue to see the solution. – Ben Feb 25 '14 at 03:27

score 0 · Answer 2 · answered Feb 25 '14 at 03:03

0

Try this

CREATE TABLE MajorEquipment(
  MEId INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (MEId),
  PACId INT 
  REFERENCES PlantAreaCodes(PACId)
)

answered Feb 25 '14 at 03:03

Sadikhasan

18,365
21
80
122

FWIW, MySQL does not support column-level constraint syntax. – Bill Karwin Feb 25 '14 at 03:04
MySQL reports no error, but does not create the constraint. Run `SHOW CREATE TABLE MajorEquipment` and you'll see. – Bill Karwin Feb 25 '14 at 04:08

Creation Failed: Key column 'name' doesn't exist in table:

2 Answers2