This question does not so much pertain to MySQL as it does database design in general. I am however using MySQL for my project and I've been stumped for a couple days now on the many-to-many relationship issue even though I've had two other friend developers explain it to me. What I need is someone who can articulate an answer from basic to advanced in description. My main problem is that I understand "how you set it up", but I'm failing to see how (what method of thinking or whatever) the join or junction table gets updated (how its managed) after the relationships are established. I will site an example:

Tables
employee | fields: employeeid (PK) and employeename
project | fields: projectid (PK) and projectname
emp_proj (junction table) | fields: employeeid & projectid - both form a composite PK

I have these relationships SETUP in a sample MS Access Database just to play around with, but again I am using MySQL in my project that this whole issue pertains to.
I can insert and delete to the employee and project table just fine, but I get constraint violations when I try to update/manage the junction table. What am I missing? What is the SQL I'd use to update the junction table and make it useful data?

Whats really bothering me is that I could be this far along with understanding the many-to-many relationship and still not be getting it.... how to manage the junction table itself. Like: When do I manage it? How do I manage it? I think a lot of this has to do with stress and anxiety too since I'm a lil behind on the project. I think I have the answer, but I need someone to shake it out of me so anyone that can help would be greatly appreciated.

in the relationship you describe (nice job, by the way), you would never update the emp_proj table

you would insert into it, and delete from it, but you would never update it

example: add a new employee and register him to several existing projects
1. insert employee (obtain employeeid PK value assigned, if using auto_increment)
2. insert multiple emp_proj rows, each with same employeeid values, different projectid values

Well, sorry I mis-spoke on the "update" word, but thats a good point. I meant update in the general sense, but yes only "inserts" and "deletes" should occur. Thats what I'm missing. When and how (what sql) do I insert into the junction table? When do I obtain the employeeid PK value? (yes I'm using auto_increment) When do I insert into the emp_proj table?

I tried this (is this stupid?):
insert into emp_proj (employeeid, projectid) values ('select employeeid from employee', 'select projectid from project');

Tried that in my MS Access play tables and it didn't like it due to key constraints.

1. Tried this since your post:
insert into emp_proj (employeeid, projectid) values (7,2);
-That works (which is progress)

2. Tried this since your post:
insert into emp_proj (employeeid, projectid) values (7,3);
-That DOESN'T work

Why? There is a projectid with the number 3 in it in the project table. Is it something to do with how I have the relationships setup? Should I not be doing a composite key in the emp_proj table? Should I have 3 keys instead 1 being a PK, and the other 2 just Numbers?

Thanks so much. You've helped me further myself this morning.

I just figured out why #2 insert in my above post didn't work. I had set in access to both fields in the junction table: "Indexed: Yes (No Duplicates)" Therefore if I'm thinking clearly but implementing the "(No Duplicates)" setting I basically strickened myself to associating only 1 employee with 1 project which is kinda funny because that is the point of using NO JUNCTION table to begin with. LOL - I'm sorry, I'm so spent right now.

After removing the "(No Duplicates)" setting on both keys in the emp_proj table yet leaving the composite primary key setup intact I'm now able to insert any combination of employeeid and projectid into the emp_proj table and INTEGRITY is working cause I checked. I tried to insert a non-existent employeeid Value into the emp_proj along with a known projectid Value and Access said "NO". I went and added the employeeid Value (11) that I was previously using when I knew it wasn't there and this time the same insert statement worked !

I'm so damn excited ! Ok - But since I just discovered this principle and have just now comprehended it...... is there anything I'm missing? or am I set? Does my analysis and thinking sound right?

Thanks.

your second insert for (7,3) should work

yes, you need a composite key for emp_proj

this might be tricky to set up in msaccess -- how did you define the keys for the relationships? what's the PK for that table?

oops, you posted while i was posting

sounds like you're good to go now ...

Thanks r937. Yeah the composite key setup in MSAccess was actually not that hard cause it was in the Access help. All you do is highlight both keys and right click the highlight area and left click "Primary Key". Thanks again for your help. I dunno why the way you explained it fixed my brain, but it did whereas others I've talked to it didn't.

thanks for the kind words

yes, explaining things as simply as possible often takes a lot of effort