I was getting the nitpicky error 150 when trying to create an InnoDB table with foreign keys. I had very carefully checked several times that all the key types and names matched exactly including UNSIGNED (which is what gets most people), and that I had an index on every foreign key as required by my version of mySQL. That covers the majority of Error 150 situations but not mine. I was failing the “First in Composite KEY” constraint, which is easy to overlook.
Side Note: I read that INDEX is no longer required in later versions of mySQL but I don’t mind doing it in case someone implements this system on an older version.
So I did the following SQL command:
show innodb status
The show innodb status command returns verbose information including identification of exactly which foreign key is causing the problem. The thing I was overlooking is documented way down in the fine print but I just wasn’t seeing it. The table that you reference when you define the foreign key has to have that key appear FIRST if it is a composite. In this case the foreign key was part of a composite primary key and it wasn’t specified first!!
Here is a snip from the table that failed to be created:
CREATE TABLE `orders` ( `id` bigint(20) unsigned NOT NULL auto_increment, `txnid` varchar(64) NOT NULL default '' COMMENT 'not unique', INDEX txnind(txnid), FOREIGN KEY (txnid) references cartxtxn(txnid), . . .
Here is the SQL code that was causing the problem. In this case it was the reference table.
CREATE TABLE `cartxtxn` ( `cartid` varchar(32) NOT NULL default '', `txnid` varchar(64) NOT NULL default '', `txntime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`cartid`,`txnid`) ) ENGINE=InnoDB DEFAULT CHARSET utf8 COMMENT 'primary reference for transactions';
Here is what I did to fix it. It makes no difference in my application which order the components of the primary key happen in, as this table only serves as the unique record of each transaction and is rarely used directly.
CREATE TABLE `cartxtxn` ( `txnid` varchar(64) NOT NULL default '', `cartid` varchar(32) NOT NULL default '', `txntime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`txnid`,`cartid`) ) ENGINE=InnoDB DEFAULT CHARSET utf8 COMMENT 'primary reference for transactions';
If it matters to you which order the keys appear in in the composite key I didn’t know if it might also work to leave the primary alone and instead specify a separate non-primary KEY for each component of the primary. Obviously if it is the only column in a KEY specification it is the first one! So I thought I’d take a detour and concoct an experiment to find out. Results: The following experiment successfully created both tables. At this point I am not sure what the performance implications of doing it this way would be. Any real mySQL expert visitor who knows, please don’t bogart it: comment and improve this article.
--experiment to see if separate keys will bypass the 150 error CREATE TABLE `cartxtxn` ( `cartid` varchar(32) NOT NULL default '', `txnid` varchar(64) NOT NULL default '', `txntime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, KEY(txnid), KEY(cartid), PRIMARY KEY (`cartid`,`txnid`) ) ENGINE=InnoDB DEFAULT CHARSET utf8 COMMENT 'primary reference for transactions'; CREATE TABLE fktest ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, fktest varchar(64) NOT NULL DEFAULT '', INDEX fktestind(fktest), FOREIGN KEY (fktest) references cartxtxn(txnid), data varchar(25) ) ENGINE=InnoDB DEFAULT CHARSET utf8;
thanx for sharing… i liked what you meant here on sql problemm….
i hate getting mixed up into codes … but still sometimes i try to do some tweeks in my blog templates 😀
Why don’t you use MyISAM Engine? It’s more flexible.
I am assuming that if people are using InnoDB they have a reason, such as transactions and rollbacks.
entajas: MyISAM vs InnoDB
InnoDB
* Soporte de transacciones
* Bloqueo de registros
* Nos permite tener las características ACID (Atomicity, Consistency, Isolation and Durability: Atomicidad, Consistencia, Aislamiento y Durabilidad en español), garantizando la integridad de nuestras tablas.
* Es probable que si nuestra aplicación hace un uso elevado de INSERT y UPDATE notemos un aumento de rendimiento con respecto a MyISAM.
MyISAM
* Mayor velocidad en general a la hora de recuperar datos.
* Recomendable para aplicaciones en las que dominan las sentencias SELECT ante los INSERT / UPDATE.
* Ausencia de características de atomicidad ya que no tiene que hacer comprobaciones de la integridad referencial, ni bloquear las tablas para realizar las operaciones, esto nos lleva como los anteriores puntos a una mayor velocidad.
Phoney, they prerequisite to be taught that filing lawsuits is not the fortitude to a halt piracy. As an choosing, it’s to skiff something most dazzling than piracy. Like self-possession of use. It’s in all respects a fluke easier to queue iTunes than to search the Internet with take a chance of malware and then crappy brightness, but if people are expected to counter-statement for the profit of loads and palm depression of to against ages, it’s not money to work. They lone standing by a feign together in cover up of people concoct software and Entanglement sites that goat it ridiculously tranquilly to corsair, and up the quality. If that happens, then there demand be no stopping piracy. But they’re too careful and appalled of losing. Risks wanderings to be bewitched!
market
Happy New Year!
how to jump higher in basketball
Basketball is a team sport that provides you most exciting time with your friends. Basketball is the most thrilling game in the world of sports. This article is best for people looking for the jump techniques in the basketball game.
> If it matters to you which order the keys appear in in the
> composite key I didn’t know if it might also work to leave
> the primary alone and instead specify a separate non-primary
> KEY for each component of the primary.
The point is -in my understanding- not the primary (composite) key as such.
The problem lies in the indexing: in order to be usable as a foreign key, the column needs an index.
The second column (b) of the composite key (a,b) does not have a direct index, making it not usable as a foreign key.
After you switched the pair of the composite key (b,a), there was an index on the referenced column (b).
Your workaround, creating a secondary key on (b), implies a separate index on (b), allowing it to be used as a foreign key. In that case you end up with “3” indexes: on the full composite key (a,b), automatically on the first column of the composite key (a) and finally the extra index on the second column of the composite key (separately) (b).