Here is the table I am attempting to create.

DROP TABLE proc_trans_distinctions IF EXISTS;
CREATE TABLE proc_trans_distinctions (
`m_id` int(10) unsigned NOT NULL,
`p_id` int(10) unsigned NOT NULL,
`mid` varchar(256) NOT NULL DEFAULT '0',
`cc_id` int(10) unsigned NOT NULL,
distinction_class TINYINT UNSIGNED NOT NULL,
PRIMARY KEY (p_id,mid,cc_id) USING BTREE,
CONSTRAINT m_ptd_mfk FOREIGN KEY (m_id) REFERENCES merchants
(idmerchants),
CONSTRAINT p_ptd_pfk FOREIGN KEY (p_id) REFERENCES processors
(p_id),
CONSTRAINT cc_ptd_ccfk FOREIGN KEY (cc_id) REFERENCES
credit_card_types (id)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=latin1;

The tables and columns in the references clauses are correct, and the
columns in question are primary keys in those tables and have the same
type as the columns in the corresponding fields in this table.

This produces the following error:

ERROR 1005 (HY000): Can't create table
'merchants2.proc_trans_distinctions' (errno: 150)
mysql> SHOW ERRORS;
+-------+------
+----------------------------------------------------------------------
+
+-------+------
+----------------------------------------------------------------------
+
+-------+------
+----------------------------------------------------------------------
+
1 row in set (0.00 sec)

In the online MySQL 5.0 reference manual, I see:

13.2.12.1. InnoDB Error Codes
The following is a nonexhaustive list of common InnoDB-specific errors
that you may encounter, with information about why each occurs and how
to resolve the problem.
•1005 (ER_CANT_CREATE_TABLE)
Cannot create table. If the error message refers to error 150, table
creation failed because a foreign key constraint was not correctly
formed. If the error message refers to error –1, table creation
probably failed because the table includes a column name that matched
the name of an internal InnoDB table.

If I create the table WITHOUT the foreign keys, and then ADD the
foreign keys, I get the following result:

mysql> ALTER TABLE proc_trans_distinctions
-> ADD CONSTRAINT m_ptd_mfk FOREIGN KEY (m_id) REFERENCES
merchants (idmerchants);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE proc_trans_distinctions
-> ADD CONSTRAINT p_ptd_pfk FOREIGN KEY (p_id) REFERENCES
processors (p_id);
ERROR 1005 (HY000): Can't create table
'merchants2.#sql-1248_d' (errno: 150)
mysql> ALTER TABLE proc_trans_distinctions
-> ADD CONSTRAINT cc_ptd_ccfk FOREIGN KEY (cc_id) REFERENCES
credit_card_types (id);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

I really don't understand why two out of the three foreign keys get
properly created while the third fails. I do not see any significant
differences in the syntax used to create them.

can anyone shed light on what is going awry here, and how to fix it?

Thanks

Ted

El 16/02/2011 18:30, Ted Byers escribió/wrote:
Run this as well:

SHOW INNODB STATUS

In practice, I've found that many foreign key errors get hidden there.



--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

On Feb 16, 1:00*pm, "Álvaro G. Vicario"
<alvaro.NOSPAMTH... (AT) demogracia (DOT) com.invalid> wrote:
lighted one thing I missed, the error still makes no sense.

Here is the definition of the processors table (that the failed
foreign key references:

CREATE TABLE `processors` (
`p_id` int(11) NOT NULL AUTO_INCREMENT,
`processor_name` varchar(256) NOT NULL,
`tz_processor` varchar(26) NOT NULL DEFAULT 'GMT',
`tz_processor_DST` varchar(45) DEFAULT 'UTC',
`processor_name2` varchar(45) DEFAULT NULL,
`payment_type` varchar(2) NOT NULL DEFAULT 'cc',
PRIMARY KEY (`p_id`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=latin1;

Thus, I changed the type of p_id in my new table to be int(11) instead
of int(10), but still the error remains.

From "SHOW INNODB STATUS", the error is:

"LATEST FOREIGN KEY ERROR
'------------------------
"110216 13:21:27 Error in foreign key constraint of table merchants2/
#sql-1248_d:
"FOREIGN KEY (p_id) REFERENCES processors (p_id):
"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.1/...nstraints.html
"for correct foreign key definition."

Obviously, the referenced column in the processors table exists, it is
the first column, and it is the primary key in that table, and it is
now the same tpye even to the size of the integer.

So why is it not finding a column that clearly exists?

Thanks

Ted

El 16/02/2011 19:29, Ted Byers escribió/wrote:
It's not exactly the same type:

`p_id` int(11) NOT NULL AUTO_INCREMENT,
`p_id` int(10) unsigned NOT NULL,

Notice the signed/unsigned difference.

--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

On Feb 17, 5:11*am, "Álvaro G. Vicario"
<alvaro.NOSPAMTH... (AT) demogracia (DOT) com.invalid> wrote:
Ok, I missed that.

Changing it to:

`p_id` int(11) NOT NULL,

fixes it (it had been changed to int11 before - I just didn't think
about unsigned vs unsigned, or notice it).

Ted

El 17/02/2011 19:34, Ted Byers escribió/wrote:
It's certainly hard to spot and MySQL's error messages do not help

The 11 part doesn't really matter. Sizes in integers are one of those
MySQL oddities we all love:

«MySQL supports an extension for optionally specifying the display width
of integer data types in parentheses following the base keyword for the
type. For example, INT(4) specifies an INT with a display width of four
digits. This optional display width may be used by applications to
display integer values having a width less than the width specified for
the column by left-padding them with spaces. (That is, this width is
present in the metadata returned with result sets. Whether it is used or
not is up to the application.)

The display width does not constrain the range of values that can be
stored in the column. Nor does it prevent values wider than the column
display width from being displayed correctly. For example, a column
specified as SMALLINT(3) has the usual SMALLINT range of -32768 to
32767, and values outside the range permitted by three digits are
displayed in full using more than three digits.»

http://dev.mysql.com/doc/refman/5.1/...ric-types.html



--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--