Malphai Malphai - 1 year ago 91
SQL Question

SQL Foreign Key Relation

I am trying to create a contact database of contacts at various companies. Contact persons and businesses are going to be stored in two separate tables in the database. I want each contact to be connected to a specific company. Am I supposed to use Foreign Key for all columns in the entire table or how is it made?

Contact table has following attributes:


And company table has:


I do not want anymore columns!

Answer Source

This is too long for a comment. To address your comments on how the FK should look, this is the table structure you should be looking for:

Create Table Contact
    Id          Int Identity(1,1) Not Null Primary Key Clustered,
    Firstname   Varchar (20),
    Lastname    Varchar (20),
    Email       Varchar (50),
    Phone       Varchar (15),
    CompanyId   Int Not Null

Create Table Company
    Id          Int Identity(1,1) Not Null Primary Key Clustered,
    Name        Varchar (100)

Alter Table Contact 
Add Constraint FK_Contact_Company 
Foreign Key (CompanyId) References Company (Id)

You need to add an Identity column to your tables to act as their Primary Keys.

Next, you need to add a CompanyId column to your Contact table to hold the relationship.

And finally, you need to create the actual Foreign Key Constraint.

Your datatypes are surely different from what I've guessed at, but this is the concept you should be using for your Foreign Key relationships.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download