<div class="gmail_quote">On Thu, Jul 8, 2010 at 10:45 AM, Stephen Hansen <span dir="ltr"><me+list/<a href="mailto:python@ixokai.io">python@ixokai.io</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
<div class="im">On 7/8/10 6:20 AM, Victor Subervi wrote:<br>
> However, I now have another error. Here is my current command:<br>
><br>
> cursor.execute("insert into personalDataKeys (Store, User,<br>
> useFirstName, useLastName, usePhone, useCell, useFax, useAddress,<br>
> useShippingAddress, useDOB, useEmail, usePW) values (%s, %s, %s, %s, %s,<br>
> %s, %s, %s, %s, %s, %s, %s)", ([store, user] + col_vals))<br>
<br>
</div>Quick point: why the parens around [store, user] + col_vars? They're<br>
redundant.<br>
<div class="im"><br>
><br>
> I get this error from MySQL which I am having a hard time understanding:<br>
><br>
> LATEST FOREIGN KEY ERROR<br>
> ------------------------<br>
> 100708 6:15:01 Transaction:<br>
> TRANSACTION 0 9382, ACTIVE 0 sec, process no 5326, OS thread id<br>
> 1169992000 inserting, thread declared inside InnoDB 500<br>
> mysql tables in use 1, locked 1<br>
> 3 lock struct(s), heap size 368, undo log entries 1<br>
> MySQL thread id 1502, query id 23700 localhost beno update<br>
> insert into personalDataKeys (Store, User, useFirstName, useLastName,<br>
> usePhone, useCell, useFax, useAddress, useShippingAddress, useDOB,<br>
> useEmail, usePW) values ('specialty', 'patients', 1, 1, 1, 1, 1, 1, 0,<br>
> 1, 1, 1)<br>
> Foreign key constraint fails for table `test/personalDataKeys`:<br>
> ,<br>
> CONSTRAINT `personalDataKeys_ibfk_1` FOREIGN KEY (`Store`) REFERENCES<br>
> `products` (`Store`)<br>
<br>
</div>A foreign key is a constraint, a restriction, which says that rows in<br>
TableA ("personalDataKeys") depend on certain *matching* rows to already<br>
exist and always be valid in TableB ("products"); the exact match is a<br>
column they have in common ("Store").<br>
<br>
The purpose of foreign keys is to keep data consistent. Here, it appears<br>
as if you have established a key such that the 'store' column in your<br>
personalDataKeys table must point to a certain row in the products table<br>
which has a 'store' column of the exact same value.<br>
<br>
This error message is indicating that when you do this INSERT, there is<br>
no corresponding row in the products table.<br></blockquote><div><br>mysql> describe products Store;<br>+-------+-------------+------+-----+---------+-------+<br>| Field | Type | Null | Key | Default | Extra |<br>
+-------+-------------+------+-----+---------+-------+<br>| Store | varchar(40) | NO | MUL | NULL | | <br>+-------+-------------+------+-----+---------+-------+<br>1 row in set (0.00 sec)<br><br>mysql> describe personalDataKeys Store;<br>
+-------+-------------+------+-----+---------+-------+<br>| Field | Type | Null | Key | Default | Extra |<br>+-------+-------------+------+-----+---------+-------+<br>| Store | varchar(40) | NO | MUL | NULL | | <br>
+-------+-------------+------+-----+---------+-------+<br>1 row in set (0.00 sec)<br><br>They both use innodb. They're both indexed. I was thinking after getting your email that maybe I'd set the varchars to different lengths, but no. However...<br>
<br>mysql> select * from products;<br>Empty set (0.00 sec)<br><br>Is it that I can't insert into personalDataKeys until I've first done so in products? After rethinking this, it occurred to me that I probably made a mistake in copying my create table command from personalData to personalDataKeys, both of which had the foreign key of Store referenced to table products. That wasn't necessary, since personalDataKeys only needs to be associated with personalData, so I dropped and recreated the table, updating personalDataKeys foreign key to reference personalData; however, once again:<br>
<br>mysql> select * from personalData;<br>Empty set (0.00 sec)<br><br>Here's the deal:<br><br>mysql> describe personalData;<br>+-------------------+------------------+------+-----+------------+----------------+<br>
| Field | Type | Null | Key | Default | Extra |<br>+-------------------+------------------+------+-----+------------+----------------+<br>| ID | int(10) unsigned | NO | PRI | NULL | auto_increment | <br>
| Store | varchar(40) | NO | MUL | NULL | | <br>| User | varchar(50) | NO | MUL | NULL | | <br>| FirstName | varchar(100) | NO | | NULL | | <br>
| LastName | varchar(100) | NO | | NULL | | <br>| Phone | varchar(13) | YES | | NULL | | <br>| Cell | varchar(13) | YES | | NULL | | <br>
| Fax | varchar(13) | YES | | NULL | | <br>| AddressID | int(11) | NO | MUL | NULL | | <br>| ShippingAddressID | int(11) | NO | MUL | NULL | | <br>
| DOB | date | YES | | 2000-01-01 | | <br>| Email | varchar(100) | NO | | NULL | | <br>| PW | varchar(12) | NO | | NULL | | <br>
+-------------------+------------------+------+-----+------------+----------------+<br>13 rows in set (0.00 sec)<br><br>mysql> describe personalDataKeys;<br>+--------------------+-------------+------+-----+---------+-------+<br>
| Field | Type | Null | Key | Default | Extra |<br>+--------------------+-------------+------+-----+---------+-------+<br>| Store | varchar(40) | NO | MUL | NULL | | <br>| User | varchar(50) | NO | MUL | NULL | | <br>
| useFirstName | tinyint(1) | NO | | NULL | | <br>| useLastName | tinyint(1) | NO | | NULL | | <br>| usePhone | tinyint(1) | NO | | NULL | | <br>| useCell | tinyint(1) | NO | | NULL | | <br>
| useFax | tinyint(1) | NO | | NULL | | <br>| useAddress | tinyint(1) | NO | | NULL | | <br>| useShippingAddress | tinyint(1) | NO | | NULL | | <br>| useDOB | tinyint(1) | NO | | NULL | | <br>
| useEmail | tinyint(1) | NO | | NULL | | <br>| usePW | tinyint(1) | NO | | NULL | | <br>+--------------------+-------------+------+-----+---------+-------+<br>12 rows in set (0.00 sec)<br>
<br>In personalDataKeys I store which fields will be required for a given store as it relates to personal data. For example, if there is a pharmacy with users 'doctors' and 'patients', certain fields in personalData will be required for one but not the other, and this needs to be inserted into personalDataKeys. All of this, however, obviously happens before any data is actually entered into either personalData or products. It now seems to me that I have mistakenly put the constraint on the wrong table; that it should be on personalData and not personalDataKeys, but before I do that I would like confirmation that this looks correct.<br>
TIA,<br>beno<br></div></div>