Solved

FK relationship in MySQL in WorkBench IDE

Posted on 2010-08-12
4
655 Views
Last Modified: 2013-11-08
i'm having trouble creating a one to many foreign key relationship between two InnoDB tables that I have created in my MySQL db using the workbench IDE. I'm using this in conjuction with vs 2010 and the entity framework. so i'm looking for some visual aids (tutorials preferably) that address this and show me the proper way to achieve this. Any suggestions and or suggested resources would be appreciated.
0
Comment
Question by:mikesExpertExchange
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 33424308
You implement a one-many relationship simply by including a key of the parent table (the one) in the child table (the many).  Usually, it is the primary key, but any unique key will do.

If you want the database to enforce the integrity of that relationship, then you use a foreign key constraint.  The foreign key constraint is a "rule" applied to the child table that guarantees the value of the foreign key will be present in the referenced unique key of the parent table.

alter table CHILDTABLE add constraint CONSTRAINTNAME foreign key (COL1, COL2...) references PARENTTABLE (COL1, COL2)

I haven't used MySQL IDE, but most development enviornments provide a graphical way to create the necessary keys and constraints.  Someone else will need to assist you with that.  
0
 
LVL 1

Author Comment

by:mikesExpertExchange
ID: 33425010
thank you. as a sidenote, good, bad or indifferent, can you have a primary key that is a character string?
0
 
LVL 42

Expert Comment

by:dqmq
ID: 33432608
Absolutely!  But if it's longer than 16 bytes, I'd reconsider (for optimization reasons).
0
 
LVL 1

Author Comment

by:mikesExpertExchange
ID: 33432781
thanks for your help....
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question