Linking an identifier from one table to data in another table
I have an assignment where I have to create two tables in the database. The tables look like this:
ContactPerson (ID, First Name, Last Name, Email Address, Phone Number)
Company (ID, CompanyName)
Now my problem is that I need to associate ContactPerson with a specific company, but I cannot have them in the same table.
I understand that I can use the join operator to display both tables in one query, but I need a database to find out which people are associated with which company when I implement this data file in my asp.net project.
How to do it?
source to share
You said "specific" company, so I am assuming you have one company per person.
Place a column in a custom table named CompanyID ...
ALTER TABLE ContactPerson
ADD CompanyID int
(assuming your ids are ints) and then create the following foreign key:
ALTER TABLE [dbo].ContactPerson
ADD CONSTRAINT [FK_ContactPerson_Company]
FOREIGN KEY (CompanyID)
REFERENCES Company (ID)
Shark is right if you want a lot of relationships.
To get all the people in the company:
SELECT * FROM Contact person WHERE CompanyID = x
You don't need to enforce a foreign key constraint, but if you don't, you might accidentally enter invalid data. All "Constraint" makes a rule for you for you, in other words "make sure sql knows what people are in the company," as your question suggests what you need to do.
The above query will work without a foreign key constraint, but then your database is not "aware" of the relationship.
.. and if I try to insert a person with companyid that doesn't exist, SQL throws an error (that's good).
source to share
Since this is a one-to-many relationship, I used to put this data into a table ContactPerson
. But since you explicitly say you can't, just create a join table:
create table ContactPersonCompany
(
ContactPersonID int not null foreign key references ContactPerson(ID),
CompanyID int not null foreign key references Company(ID)
)
You now have a relationship between ContactPerson
and Company
.
Example: Select all people from a specific company.
select
cp.Surname,
cp.Forename
from ContactPerson cp
inner join ContactPersonCompany cpc
on cp.ID = cpc.ContactPersonID
inner join Company c
on cpc.CompanyID = c.ID
where c.CompanyName = 'Some Company'