error in making table -mysql

Question

The Error is: Can't create table c.handsets

How do i find the problem?

I have made a database and I found that my other tables are created in it but only one is giving me this error.

Answer 1

There are conditions that must be satisfied for a foreign key:

• You must use InnoDB storage engine in both tables.

• The data types must be identical in both tables. E.g. VARCHAR(250) is not identical to VARCHAR(255).

• There must be a PRIMARY KEY or UNIQUE constraint on the column(s) in the parent table.

• Your foreign key must declare the same column(s), in the same order, as those in the PRIMARY KEY or UNIQUE constraint in the parent table. This is your most likely problem.

So for example, if this is your parent table:

CREATE TABLE PhoneModels (
  make VARCHAR(255),
  model VARCHAR(255),
  PRIMARY KEY (make, model)
);

The following would be wrong because the primary key has two columns, but each foreign key has one column.

CREATE TABLE Handsets
(
  ...
  make VARCHAR(255),
  model VARCHAR(255),
  FOREIGN KEY (make) REFERENCES PhoneModels(make),                  // WRONG
  FOREIGN KEY (model) REFERENCES PhoneModels(model)                 // WRONG
);

It doesn't work this way; you should have one foreign key that references both columns.

CREATE TABLE Handsets
(
  ...
  make VARCHAR(255),
  model VARCHAR(255),
  FOREIGN KEY (make, model) REFERENCES PhoneModels(make, model),    // RIGHT
);