Problem creating Foreign Key

[doubledee]

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

 

[gizmola]

Did you try and add the constraint using ALTER TABLE ADD CONSTRAINT.....

[Barand]

also,ensure tables are InnoDB. MyIsam does not support foreign keys

[gizmola]

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.

[doubledee]

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

 

 

 

[Barand]

Give those tinyint fields default values of zero. Omit them from the insert query completely. This will set them to zero initially

[doubledee]

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

 

[Barand]

I can only guess that as they are int fields and no values specified then it automatically defaulted to zero

[doubledee]

Barand,

 

Thanks for the help!!! 

 

 

Debbie