CODEWITHSUNDEEP
sqlsql-serverforeign-keysrelational-databaseforeign-key-relationship
Malphai
Malphai

Reputation: 317

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:

Firstname
Lastname
Email
Phone

And company table has:

Name

I do not want anymore columns!

Upvotes: 1

Views: 441

Answers (1)

Siyual
Siyual

Reputation: 16917

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.

Upvotes: 1

Related Questions