Click here to Skip to main content
14,880,257 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a specific, yet simple question. I have three tables in my SQL database. I want to use the primary key in the first one as the foreign key in the other two. Now, this is where I run into problems.

I have made all three tables with no problem. Basically I have companyData, contactData and repData. I'm pretty sure it's a one to many relation. Every company can have a contact and multiple reps. A rep and contact can have only one company.

My question is... even if I tie to tables together with a 'same' primary key, is there a certain way to pull the data from the database or will it just automatically associate the rep's ID with the company ID?

What I have tried:

Just creating the tables in the database and making the foreign keys.
Updated 6-Apr-17 17:38pm
CHill60 6-Apr-17 15:49pm
What do you mean by "pull the data from the database"? You will need to use a JOIN - which one depends on what you are trying to query and from where
[no name] 6-Apr-17 16:06pm
Quote: "Every company can have a contact and multiple reps. A rep and contact can have only one company."

1.) "Every company can have a contact"
companyData contactData 1:1 relation

2.) "Every company ..... can have multiple reps"
companyData repData 1:N relation

-> Rep and contact do _not_have direct relation

Some more Explanation needed I think.

1 solution

Based on your description, there are at least three entities, first state clearly the what's of these entities based on the user requirements, e.g.:
1. What is a company, its purpose, and its data attributes?
2. What is a contact, its purpose, and its data attributes?
3. What is a rep, its purpose, and its data attributes?
4. What is the relationships between them, if any?
Use a diagram to represent points 1 to 4.
Use this as reference Introduction to database design[^]
CPallini 7-Apr-17 3:25am
Peter Leow 7-Apr-17 4:42am
Thank you, CPallini.
Jawanaut 7-Apr-17 13:48pm
Thank you for the reply. That was very informative. Unfortuantely it left me face with a new problem. When trying to create the constraints for the relations, the field will not submit to the database. Instead I get an error with the constraints.

I feel I may have set it up wrong. That link gave examples differently. It looks more like EER diagram than SQL. Either way, I'm not using either. I'm using phpAdmin's 'Foreign key constraint (INNODB)'.

Each table has an ID. Since two can be in the one but not related to each other, I made a column in both repData and contactData called companyID and made it relational to companyID in companyData table.

Now, just wondering if I'm doing it backwards... or completely wrong? I do really appreciate everything you (and everyone) have offered me so far.
Peter Leow 7-Apr-17 22:30pm
The purpose of foreign key constraint is to enforce referential integrity rule of the database. That includes not allowing a foreign key value in the children tables (rep and contact) without it first exists as primary key value in the parent table (company).

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900