CODEWITHSUNDEEP
mysqlsql
starkk92
starkk92

Reputation: 5924

Error during the creation of table due to foreign key

I have table1 already in my db.

Table1: 

CREATE TABLE `product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `typename` varchar(255) DEFAULT NULL,
  `typecode` varchar(55) DEFAULT NULL,
  `parent1` int(11) DEFAULT NULL,
  `parent2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `parent1` (`parent1`),
  KEY `parent2` (`parent2`)
) ENGINE=InnoDB AUTO_INCREMENT=396 DEFAULT CHARSET=latin1;

I tried to create the second table with foreign key which has reference to product.typename

this is the creation query I have used.

 CREATE TABLE measurements (
    id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    age_group varchar(20) NOT NULL,
    article_type varchar(255) DEFAULT NULL,
    dimension text ,
    createdOn int(11) NOT NULL,
    updatedOn int(11) NOT NULL,
    createdBy text NOT NULL,
    foreign KEY(article_type) references product(typename)
)ENGINE=InnoDB AUTO_INCREMENT=396 DEFAULT CHARSET=latin1;

But this table creation is a failure with the following error.

ERROR 1215 (HY000): Cannot add foreign key constraint

I have done show engine innodb\g

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2015-05-15 19:03:28 131f71000 Error in foreign key constraint of table db/measurements:
foreign KEY(article_type) references product(typename)
)ENGINE=InnoDB AUTO_INCREMENT=396 DEFAULT CHARSET=latin1:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html
for correct foreign key definition.

Can some one point me the problem and what is this first columns concept?

Upvotes: 3

Views: 67

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

A foreign key references a key. This is usually the primary key, but doesn't have to be. In your case however you reference a column (typename) which is not defined as a key. This shows a design flaw.

You decided to use technical IDs as primary keys for your tables. You can do this. But if you do this, keep two things in mind:

  1. You've created IDs in order to link tables easily. So don't reference a record by another column (such as typename), but by its ID.
  2. You must still make sure that the table's natural key is unique.

As to point 2: What is your table's natural key? What is or are the fields that uniquely identify a record (apart from your technically created ID)? Is it typename? Is typename the product's name and must it be unique? Or is this typecode? Whatever it is, give this field a unique constraint, so you cannot have the same product twice in your table.

Maybe it would help you learn to design your database, if you didn't use technical IDs at all. Give it a thought.

Just a side note: Be aware that MySQL has a strange way of using the keyword KEY:

create table t (col int key);

Here KEY really means the table's primary key. col cannot be null and col must be unique. It is short for:

create table t (col int primary key);

However,

create table t (col int, key(col));

is something entirely else. Here, KEY is not short for PRIMARY KEY, but a synonym for INDEX. col can be null, col doesn't have to be unique. So better use the synonym INDEX to make it clear to a reader:

create table t (col int, index(col));

When working with an additional ID, as you are doing, you even need a unique index:

create table t (id int primary key, col int, unique index(col));

or

create table t (id int, col int, primary key(id), unique index(col));

Upvotes: 0

Robert
Robert

Reputation: 25763

Referenced column should be Primary key. Here 

foreign KEY(article_type) references product(typename)

you want to reference with typename column which is not PK.

To do it in properly way you should create table ProductType like this:

CREATE TABLE `ProductType` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `typename` varchar(255) DEFAULT NULL,
  `typecode` varchar(55) DEFAULT NULL,
) ENGINE=InnoDB AUTO_INCREMENT=396 DEFAULT CHARSET=latin1;

then you can create reference like this:

 CREATE TABLE measurements (
    id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    age_group varchar(20) NOT NULL,
    IdProductType NOT NULL,
    dimension text ,
    createdOn int(11) NOT NULL,
    updatedOn int(11) NOT NULL,
    createdBy text NOT NULL,
    foreign KEY(IdProductType) references ProductType(Id)
)ENGINE=InnoDB AUTO_INCREMENT=396 DEFAULT CHARSET=latin1;

Don't forget to do it with Product table. Above solution is only suggestion, you have to consider your table structure yourself.

Upvotes: 2

Related Questions