Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2012
    Posts
    3

    Unanswered: Linking ID from one table to data in another table

    I have an assignment where I am to create two tables within a database. One of the tables have the name ContactPerson with the attributes; ID, Forename, Surname, Email, PhoneNumber. The other table is called Company and has the attributes: ID, CompanyName.

    Now my problem is that I have to link a ContactPerson to a specific company, but I can't have them in the same table.

    I understand that I can use the join statement to show both tables in one query but I need the database to know which person is linked to which company when I implement this databse into my asp.net project.

    How do I do this?

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    You have a clear description of your system.
    You have Companies, that will be stored in the Company table and you have People, stored in the Person table. And you have a relation between those two with the label ContactPerson.

    You can now start to think about how to implement this relation in your data model.
    - How many Contactpersons can one Company have?
    - For how many Companies can a Person be the Contactperson?

    If a Company can only have one Contactperson, where would you store the FK? in the Contactpersons or Company table?
    Same question if a Person can only be the Contactperson of one Company. (one-to-many relations)

    When a Company can have only one Contactperson at a time and a Person can be the Contactperson of only one Companie, where would you store the FK? (one-to-one relation)

    When a Company can have many Contactpersons and a Person can be the Contactperson of many Companies, you need an extra table to store those relations.(many-to-many relation)

    Later you will use the Foreign key (FK) - Primary Key (PK) relation to JOIN the Company with it's Contactperson(s).

    We don't do homework on this forum. But if you can show what you have came up with yourself, we will help you further.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Mar 2012
    Posts
    3
    Thanks for your response.

    Every company should be able to have several contact persons but a person can only be the contact person of one company. So I guess it's a one-to-many relatation where the company table holds the PK.

    Now I used this piece of code which I got from another website to create the FK.

    ALTER TABLE database.dbo.ContactPerson
    ADD CONSTRAINT [FK_ContactPerson_Company]
    FOREIGN KEY (CompanyID)
    REFERENCES Company (ID)

    Now I get the error Msg 1776;

    "There are no primary or candidate keys in the referenced table 'Company' that match the referencing column list in the foreign key 'FK_ContactPerson_Company'."

    This isn't really a homework by the way. I'm about to do a work sample for a company and to prepare they have given me some exercises (which I won't hand in).

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This isn't really a homework by the way.
    OK
    "There are no primary or candidate keys in the referenced table 'Company' that match the referencing column list in the foreign key 'FK_ContactPerson_Company'."
    Can you post the CREATE TABLE scripts for both tables? I guess Company.id is not defined as the primary key (nor UNIQUE).
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Mar 2012
    Posts
    3
    I'm not very faimiliar with keys at all so could you go through the steps to make the FK work?

    I just created two tables with the attributes (not allowing NULL), I didn't specify any primay keys or anything like that.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •