The following constraint is failing: ALTER TABLE address_list ADD CONSTRAINT FOREIGN KEY (address_type_id) REFERENCES address_type (address_type_id);
The address_list table looks like: mysql> desc address_list; +----------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------+--------------+------+-----+---------+-------+ | ADDRESS_LIST_ID | int(10) | | PRI | 0 | | | ADDRESS_1 | varchar(100) | YES | | NULL | | | ADDRESS_2 | varchar(100) | YES | | NULL | | | ADDRESS_3 | varchar(100) | YES | | NULL | | | CITY | varchar(100) | YES | MUL | NULL | | | ZIP_CODE | varchar(20) | YES | | NULL | | | PHONE_NUM_1 | varchar(100) | YES | | NULL | | | PHONE_NUM_2 | varchar(100) | YES | | NULL | | | PHONE_NUM_FAX | varchar(100) | YES | | NULL | | | STATE_PROVINCE_ID | int(10) | YES | MUL | NULL | | | USER_ACCOUNT_ID | int(10) | YES | MUL | NULL | | | MARINA_ID | int(10) | YES | MUL | NULL | | | COMMERCIAL_ENTITY_ID | int(10) | YES | MUL | NULL | | | ADDRESS_TYPE_ID | int(10) | | MUL | 0 | | | DISTRIBUTOR_ID | int(10) | YES | MUL | NULL | | | CONTACT_INFO_ID | int(10) | YES | MUL | NULL | | | COUNTRY_ID | int(10) | YES | MUL | NULL | | | LANG_ID | int(10) | YES | | NULL | | | BOAT_LISTING_ID | int(10) | YES | MUL | NULL | | +----------------------+--------------+------+-----+---------+-------+ mysql> select count(*) from address_list; +----------+ | count(*) | +----------+ | 202118 | +----------+ The address_type table looks like: mysql> desc address_type; +-------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+--------------+------+-----+---------+-------+ | ADDRESS_TYPE_ID | int(10) | | PRI | 0 | | | ADDRESS_TYPE_DESC | varchar(100) | | | | | +-------------------+--------------+------+-----+---------+-------+ mysql> select count(*) from address_type; +----------+ | count(*) | +----------+ | 5 | +----------+ There is, of course, and index on address_list.address_type_id. Here are all the unique address_type_id values in address_list: mysql> select distinct address_type_id from address_list; +-----------------+ | address_type_id | +-----------------+ | 100 | | 101 | | 102 | | 104 | | 105 | +-----------------+ And here are all the address_type.address_type_id values: mysql> select address_type_id from address_type; +-----------------+ | address_type_id | +-----------------+ | 101 | | 102 | | 103 | | 104 | | 105 | +-----------------+ As you can see, there are no "null" or "0" address_list.address_type_id, and the address_type_id-values in address_list are the same as the address_type.address_type_id values. There are indexes on both columns (address_list.address_type_id and address_type.address_type_id (via the primary key)), the data types are exactly the same and have the same size. "show innodb status" gives a "LATEST FOREIGN KEY ERROR": ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 030918 16:39:46 Transaction: TRANSACTION 0 1534907, ACTIVE 19 sec, process no 5741, OS thread id 1106012224 inserting, thread declared inside InnoDB 231 617 lock struct(s), heap size 44352, undo log entries 72029 MySQL thread id 28, query id 1476997 localhost mysql copy to tmp table ALTER TABLE address_list ADD CONSTRAINT FOREIGN KEY (address_type_id) REFERENCES address_type (address_type_id) Foreign key constraint fails for table benchtest/#sql-166d_1c: , CONSTRAINT `0_144` FOREIGN KEY (`ADDRESS_TYPE_ID`) REFERENCES `address_type` (`ADDRESS_TYPE_ID`) Trying to add in child table, in index IF_ADDRESS_LIST_1 tuple: 0: len 4; hex 80000064; asc ...d;; 1: len 4; hex 800f4240; asc ..B@;; But in parent table benchtest/address_type, in index PRIMARY, the closest match we can find is record: RECORD: info bits 0 0: len 4; hex 80000065; asc ...e;; 1: len 6; hex 00000000ac16; asc ......;; 2: len 7; hex 800000002d0084; asc ....-..;; 3: len 8; hex 427573696e657373; asc Business;; Can anyone tell me what the issue might be? According to the manual, everything should work. It doesn't look like a data issue.... David. ----- Original Message ----- From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, September 18, 2003 3:14 PM Subject: Update question > > I'm not quite sure why I haven't run across this in the past, but now that I > have I am stumped. I am needing to update a table based on criteria found in > it and one other table, but I am uncertain how to proceed. If I had > subselects I would run the query as follows, I believe: > > UPDATE suppliercatlink > SET suppliercatlink.catid=124 > WHERE suppliercatlink.supid IN > (SELECT supplier.id > FROM supplier > WHERE supplier.company_name LIKE %exteri%) > AND suppliercatlink.catid=10 > ; > > Knowing that this is not an option I figure maybe I could join the tables in > my UPDATE statement like: > > UPDATE suppliercatlink, supplier > SET suppliercatlink.catid=124 > WHERE supplier.company_name LIKE '%brick%' > AND supplier.id=suppliercatlink.supid > AND suppliercatlink.catid=10 > ; > > Looking at the documentation it appears this will not work, at least not > with 3.23 which I am currently running. It appears that something of this > nature would work if I upgraded to 4.0.4, but I really prefer to update > mySQL before or after a project, not right in the middle of it. Can anyone > help me figure out a way around this problem? > > Jay Drake > [EMAIL PROTECTED] > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
