Altering a FOREIGN KEY Data Type

Today another curious issue happened regarding MySQL. 

I had a scenario where the rows in a table grew greatly, so that the previous PRIMARY KEY type, a SMALLINT (up to 32767 different ids), could not bear the new amount of data.
So I had to modify the PRIMARYKEY type.

When trying to alter the field, the following error appeared:

ERROR 1025 (HY000): Error on rename of '.\temp\#sql-248_4' to '.\temp\country' (errno: 150)input

Checking this error number in a shell:

$ perror 150
MySQL error code 150: Foreign key constraint is incorrectly formed

Basically this error was a consequence of trying to modify a field that was a FOREIGN KEY, with the command

ALTER TABLE mytable MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT;

The problem is that in MySQL there is no way of updating a field's type "on cascade". So to update a field that is a FOREIGN KEY one should drop the FOREIGN KEY Relation, change the field type, and then bring the relation back on.
You could also delete the table that uses the FOREIGN KEY, alter the desired field, and then re-create the dropped table.

Comments

Post a Comment

Popular posts from this blog

Slope One

Image
Slope One is a simple and efficient type of recommender system. Introduced by Daniel Lemire and Anna Maclachlan in 2005, it involves a simpler idea than the majority of other collaborative filtering implementations. While these usually calculate the similarity between vectors of items using the cosine or the Pearson methods, the Slope One approach recommends items to users based on the average difference in preferences of items.  The main idea of the algorithm is to create a linear relation between items preferences such as the relation F(x) = x + b. The name "Slope One" cames from the fact that here the "x" is multiplied by "1". It basically calculates the difference between the ratings of items for each user (for every item the user has rated). Then, it creates and average difference (diff) for every pair of items. To make a prediction of the Item A for an User 1 for example, it would get the ratings that User 1 has given to other items and a...
Read more

Apache Mahout

Image
“Scalable machine learning library” Mahout is a solid Java framework in the Artificial Intelligence area. It is a machine learning project by the Apache Software Foundation that tries to build intelligent algorithms that learn from some data input. What is special about Mahout is that  it is a scalable library, prepared to deal with huge datasets. Its algorithms are built on top of the Apache Hadoop project and, so, they work with distributed computing. Mahout offers algorithms in three major  areas: Clustering, Categorization and Recommender Systems. This lats part was incoporated in April 4 th 2008, from the previous Taste Recommender System project. Mahout currently implements  a collaborative filtering engine that supports the user-based, item-based and Slope-one recommender systems. Other algorithms available in the package are  the k-means, fuzzy k-Means clustering, Canopy, Dirichlet and Mean-Shift. They also have The Naive Bayes, Complement...
Read more

Error when using smooth.spline

When trying to interpolate a series of data the cubic spline  is a great technique to be used. I choose to use the smooth.spline function, from the R stats package. > smooth.spline(data$x,  data$y ) Nevertheless, while running smooth.spline on a collection of datasets with different sizes I got the following error: Error in smooth.spline( data$x,  data$y),  :   'tol' must be strictly positive and finite After digging a little bit I discovered that the problem was that some datasets were really small and smooth.spline wasn't being able to compute anything. Hence, make sure your dataset is big enough before applying smooth.spline to it. > if(length(data$x) > 30) {  smooth.spline( data$x,  data$y)  } UPDATE:  A more generalized solution would be: > if(IQR(data$x) > 0) {  smooth.spline( data$x,  data$y)  }
Read more