My FK Constraint is working MUCH too well. I want to make sure you can't have "payment_methods" assigned unless you're actually a user. But this happens:

mysql> select id,username from userdata where id=2973;
+-----+----------+
| id  | username |
+-----+----------+
| 2973 | jacobL  |
+-----+----------+
1 row in set (0.00 sec)

mysql> insert into payment_methods values(2973,'');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`dbname/payment_methods`, CONSTRAINT `valid_user` FOREIGN KEY (`seller`) REFERENCES `userdata` (`id`))

mysql> select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where CONSTRAINT_NAME like "%valid%";
+------------------------+-------------+-----------------+-----------------------+------------------------+
| TABLE_NAME             | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+------------------------+-------------+-----------------+-----------------------+------------------------+
| payment_methods | seller      | valid_user      | userdata     | id                     |
+------------------------+-------------+-----------------+-----------------------+------------------------+
1 row in set (0.03 sec)

What have I done wrong?

    Since you're not listing columns to insert into for payment_methods my best guess would be they aren't in the correct order or you're missing one. Probably a poor assumption but I see nothing wrong specifically with what you posted, other than I consider is bad practice not to list the columns :p

      mysql> describe payment_methods;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| seller  | int(11)      | YES  | MUL | 0       |       |
| methods | varchar(128) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

      Telling it values doesn't seem to make any difference:

      mysql> insert into payment_methods (seller,methods) values(2973,"Mastercard");
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`redacted/seller_payment_methods`, CONSTRAINT `valid_user` FOREIGN KEY (`seller`) REFERENCES `userdata` (`id`))

        Maybe you should post a sequence of SQL statements that create the tables with the foreign key constraint, populate the userdata table, and then attempt the insertion into the payment_methods table such that it fails for you.

          I got it this morning.

          The referenced table was MyISAM instead of InnoDB. Definitely a headscratcher ... why was I allowed to create such a key if the referenced table didn't support the operation?

          😕

            Eh, I thought that with MyISAM there would just be no enforcement of foreign key constraints (i.e., the SQL will still be parsed). But your problem appears to be that the foreign key constraints are enforced incorrectly.

              You can make incorrect FK on InnoDB that points to MyISAM. The problem is there is no lookup available in MyISAM (why? I'm not sure....) but the FK can still be enforced, it just compares the incoming value against an empty set (no matter how much is in the referenced table). This is an issue with the validation of creating FK, because it sees the target table as existing, with the target column - but doesn't validate that its a proper engine storage. Just another one of those little MySQL quirks.

                Write a Reply...