Need help setting up FK on Many-to-Many

[doubledee]

Ugh!

 

I am trying to set up Indexes and Foreign Keys for my many-to-many relationship.

 

Here are my tables...

member -||--------0<- bio_answer ->0----------||- bio_question

 

 

In my "bio_answer" table I have the following fields...

- id
- member_id
- question_id

 

 

I have made "id" an auto_increment PK, and I combined "member_id" and "question_id" and created a Unique Index.

 

 

When I go into phpMyAdmin, and click on the table "bio_answer" and then the "Structure" tab, and then the "Relation View" link, I see this...

Links to
InnoDB
id		_______	ON DELETE______	ON UPDATE________
member_id	_______	ON DELETE______ ON UPDATE________  
question_id 	No index defined!
response 	No index defined!
created_on 	No index defined!
updated_on 	No index defined!

 

 

I would expect a way to map...

 

bio_answer.member_id ---> member.id

 

and

 

bio_answer.question_id ---> question.id

 

...but as you can see above, something is messed up?!

 

I could use some help here with both Foreign Key Constraints and properly setting up my Indexes in phpMyAdmin!!

 

Thanks,

 

 

Debbie

 

[cpd]

To add your constraints you need to run some sql along the lines of

 

ALTER TABLE `bio_answer` ADD CONSTRAINT 
    FOREIGN KEY (`member_id`)
    REFERENCES member(`id`)
        ON UPDATE NO ACTION 
        ON DELETE NO ACTION,
    FOREIGN KEY (`question_id`)
    REFERENCES bio_question(`id`)
        ON UPDATE NO ACTION 
        ON DELETE NO ACTION

 

I think that's pretty much correct.

[doubledee]

To add your constraints you need to run some sql along the lines of

 

ALTER TABLE `bio_answer` ADD CONSTRAINT 
    FOREIGN KEY (`member_id`)
    REFERENCES member(`id`)
        ON UPDATE NO ACTION 
        ON DELETE NO ACTION,
    FOREIGN KEY (`question_id`)
    REFERENCES bio_question(`id`)
        ON UPDATE NO ACTION 
        ON DELETE NO ACTION

 

I think that's pretty much correct.

 

 

Okay, but you missed the pint of my OP...

 

phpMyAdmin is saying I cannot add a Foreign Key Constraint unless I apparently have Indexes on each field I want to use in the FK Constraint.

 

I added some plain-vanilla Indexes to the fields that I want to be Foreign Keys like this...

Keyname		Type		Field
-----------	----------	----------------
PRIMARY		PRIMARY		id
idx_u_answer	UNIQUE		member_id,
			question_id
idx_member_id	INDEX		member_id
idx_question_id	INDEX		question_id

 

 

I guess that is what phpMyAdmin wanted? 

 

 

Debbie

 

[cpd]

Yes sorry I see what you're saying. To make a field a key you must first index it. Apologies I did misinterpret you slightly.

[doubledee]

Yes sorry I see what you're saying. To make a field a key you must first index it. Apologies I did misinterpret you slightly.

 

Having been an MS Access Developer in a former life, I think this is where I am getting confused...

 

In MySQL...

 

An Index is just an Index and is used to help performance, right?

 

And a Foreign Key is NOT an Index but instead a Constraint, right?

 

But to create a Foreign Key Constraint, you first have to create a Foreign Key Index, right?

 

And to further complicate things, a Primary Key is a Primary Key/Unique Constraint combined with a Primary Key/Unique Index rolled up all in one, right?

 

(See, in MS Access, Primary and Foreign Keys are Indexes and Constraints all in one?!)

 

Did I get all of that right? 

 

 

Debbie