Clever Geek Handbook

Error while creating table due to foreign key

I have table1 already in my db.

Table 1:

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 a second foreign key table that refers to product.typename

this is the create query I 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 an error with the following error.

ERROR 1215 (HY000): Cannot add foreign key constraint

I did 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 anyone point me to the problem and what is this first concept of columns?

+3


source to share


2 answers


The column reference must be Primary key

. Here

foreign KEY(article_type) references product(typename)

you want to refer to a column typename

that is not PK

.

To do this correctly, you must create a 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;

you can create a link 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 this with a table Product

. The above solution is just a suggestion, you should consider your table structure yourself.

+2


source


Foreign key refers to a key. This is usually the primary key, but not required. However, in your case, you are referring to a column (typename) that is not defined as a key. This shows a design flaw.

You have chosen to use technical IDs as primary keys for your tables. You can do it. But if you do, consider two things:

  • You have created identifiers to easily link tables. Therefore, do not refer to the entry by another column (eg typename), but by its ID.
  • You should still make sure the natural key of the table is unique.

Regarding point 2: What's your natural table key? What are or are there fields that uniquely identify a record (other than your technically generated ID)? Is this typename? Is typename the name of the product and should it be unique? Or is it a typecode? Whatever it is, give this field a unique constraint, so you cannot have the same product twice in your table.

Perhaps this will help you learn how to create your database if you don't use technical IDs at all. Let him think.

Just note, remember that MySQL has a weird way of using the KEY keyword:

create table t (col int key);

Here KEY really means the primary key of the table. col cannot be null and col must be unique. This is shorthand for:

create table t (col int primary key);


However

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

is something completely different. Here KEY is not short for PRIMARY KEY, but is synonymous with INDEX. col can be null, col does not have to be unique. Therefore, it is better to use the synonym INDEX to make it clear to the reader:

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

When dealing with an optional identifier, like you do, 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));
0


source






More articles:


All Articles