doubledee Posted May 7, 2012 Share Posted May 7, 2012 I have a "friends" table that is used to keep track of Users' Friends. In it are two fields ("requestor_approved" and "requestee_approved") that can only be one of three values: 0, 1, 2 I created a lookup table called "ternary" that is supposed to enforce those three values, but I cannot seem to create a Foreign Key Constraint, even though I have done it before on other tables... Here are the details of each table... friend Field Type Null Default id mediumint( No requestor mediumint( No requestee mediumint( No requestor_approved tinyint(1) No requestee_approved tinyint(1) No Indexes: Keyname Type Cardinality Field PRIMARY PRIMARY 2 id idx_requestor INDEX 2 requestor idx_requestee INDEX 2 requestee idx_requestee_approved INDEX 2 requestee_approved idx_requestor_approved INDEX 2 requestor_approved ternary Field Type Null Default Comments state tinyint(1) No value varchar(5) No Indexes: Keyname Type Cardinality Field PRIMARY PRIMARY 3 state When I am in phpMyAdmin and go to add the Foreign Key COnstraint, the "ternary" table doesn't appear even though it seems like my data-types and everything else should match up. What is going on?? Thanks, Debbie Quote Link to comment https://forums.phpfreaks.com/topic/262230-problem-creating-foreign-key/ Share on other sites More sharing options...
gizmola Posted May 8, 2012 Share Posted May 8, 2012 Did you try and add the constraint using ALTER TABLE ADD CONSTRAINT..... Quote Link to comment https://forums.phpfreaks.com/topic/262230-problem-creating-foreign-key/#findComment-1343907 Share on other sites More sharing options...
Barand Posted May 8, 2012 Share Posted May 8, 2012 also,ensure tables are InnoDB. MyIsam does not support foreign keys Quote Link to comment https://forums.phpfreaks.com/topic/262230-problem-creating-foreign-key/#findComment-1343908 Share on other sites More sharing options...
gizmola Posted May 8, 2012 Share Posted May 8, 2012 Oh yeah, great point Barand. The default mysql storage engine "myisam" simply ignores any constraints in your DDL. Myisam doesn't support referential integrity, nor transactions. If your tables are myisam that could explain why you don't have an option to set constraints in phpMyAdmin. Quote Link to comment https://forums.phpfreaks.com/topic/262230-problem-creating-foreign-key/#findComment-1343913 Share on other sites More sharing options...
doubledee Posted May 8, 2012 Author Share Posted May 8, 2012 also,ensure tables are InnoDB. MyIsam does not support foreign keys You get the golden prize!!! THANK YOU!!! What is funny is that I have my "friend" table as Inno but left "ternary" as ISAM because I figured it wouldn't hurt since it was the look-up table itself and thus had no Foreign Key?! (Guess it did matter!) BTW, not that that is fixed... Why is it that if I try this add... INSERT INTO `doubledee`.`friend` ( `id` , `requestor` , `requestee` , `requestor_approved` , `requestee_approved` ) VALUES ( '3', '19', '1', '', '' ); where I am leaving out two required (i.e. NOT NULL) fields, the INSERT works but I get this in phpMyAdmin... Inserted rows: 1 Warning: #1366 Incorrect integer value: '' for column 'requestor_approved' at row 1 Warning: #1366 Incorrect integer value: '' for column 'requestee_approved' at row 1 and it just INSERTS a 0 for each field I forgot to specify?? I would expect my INSERT to fail... Thanks, Debbie Quote Link to comment https://forums.phpfreaks.com/topic/262230-problem-creating-foreign-key/#findComment-1343994 Share on other sites More sharing options...
Barand Posted May 8, 2012 Share Posted May 8, 2012 Give those tinyint fields default values of zero. Omit them from the insert query completely. This will set them to zero initially Quote Link to comment https://forums.phpfreaks.com/topic/262230-problem-creating-foreign-key/#findComment-1344042 Share on other sites More sharing options...
doubledee Posted May 8, 2012 Author Share Posted May 8, 2012 Give those tinyint fields default values of zero. Omit them from the insert query completely. This will set them to zero initially I had left out defaults while I was debugging. But back to my question... Why does phpMyAdmin/MySQL put in "0" for those two fields when there were no values in my INSERT and there were no defaults set on the fields? It appears to just grab the first value in the Lookup Table? :-/ Debbie Quote Link to comment https://forums.phpfreaks.com/topic/262230-problem-creating-foreign-key/#findComment-1344043 Share on other sites More sharing options...
Barand Posted May 8, 2012 Share Posted May 8, 2012 I can only guess that as they are int fields and no values specified then it automatically defaulted to zero Quote Link to comment https://forums.phpfreaks.com/topic/262230-problem-creating-foreign-key/#findComment-1344102 Share on other sites More sharing options...
doubledee Posted May 8, 2012 Author Share Posted May 8, 2012 Barand, Thanks for the help!!! Debbie Quote Link to comment https://forums.phpfreaks.com/topic/262230-problem-creating-foreign-key/#findComment-1344106 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.