Creating and Working with tables in Sql Server

Hi friends! In our last post we have seen How to create Database and perform Alter, Delete/ Drop operation on databases in SQL Server.

Today, We are going to learn how to create a Tables in SQL Server and implementing Foreign Key Constraint in our table, also the benefits of Foreign key in our Database.

The main aim of this post is to teach how to make Tables in SQL Server and establish a relationships between 2 tables using Primary Key and Foreign Key Relation.

Table Structure

There are 2 ways to make Tables as similar to Databases in SQL Server.
To know how to make Databases in SQL Server please go through my previous post

• Creating and Working with Database.

There are 2 ways to make Tables in SQL Server.

1. Graphically
2. using a Query

First Let's discuss how to make Table in SQL Server Graphically.

We need to follow some steps to make a Table.

1. Firstly connect to the SQL Server Management Studio(SSMS). Need help? click here
2. Expand Databases folder by clicking on "+" icon on the left side.
3. Now, Right click on Tables folder in Object explorer window and
4. Click on New Table option.
5. Fill Column Name, Data Type and Allow Nulls(if you want to pass NULL value as default), as shown below and save the table as tblPerson. 

Table Design of tblPerson

Following steps creates a table with the name tblPerson with 4 columns.
Now, Let’s make another table using a Query in SQL Server.
The general syntax to create a table in SQL Server is

Create Table TableName (Column1Name Datatype(size) Contraint if any, Column2Name Datatype(size), so on..)

So, Let’s make a table with the name tblGender, with 2 Columns ID and Gender respectively.
ID column is Primary Key Column.

The primary key is used to uniquely identify each row in a table.Primary key does not allow nulls.

Create Table tblGender(ID int Not Null Primary Key,Gender nvarchar(50))

Now, Let's add the reference between both the tables using Foreign Key relation.
In tblPerson table, GenderID is the foreign key referencing ID column in tblGenderTable.
Foreign key references also can be added in two ways i.e. graphically using SSMS and using a query as well.
To graphically add a foreign key reference in SQL Server we need to follow some steps as follows:

1. Right click on tblPerson table in Tables Folder and select Design option.
2. In the table design window, right click on GenderId column and select Relationships option.

   

   Selecting Relationship Option from Context Menu
3. In the Foreign Key Relationships window, click on Add button as shown in picture.

   

   Adding a New Relation

4. Now expand, in Tables and Column Specification row, by clicking the, ">" sign
   
5. Click on the elipses ("...") button, that is present in Tables and Column Specification row
   
6. From the Primary Key Table dropdown, select tblGender
   
7. Click on the row below, and select ID column
   
8. From the column on the right hand side, select GenderId
9. Click OK and then click close.
   
10. Finally save the table.
    
11. You will get a Confirmation Message if you create a Relationship after Table Creation, then click on Ok.
    

Here, is the another way to reference Foreign key Relation using a query.

Alter table tblPerson 
add constraint tblPerson_GenderId_FK FOREIGN KEY (GenderId) references tblGender(ID)

General Syntax of Foreign Key Constraint is

Alter table ForeignKeyTable add constraintForeignKeyTable_ForiegnKeyColumn_FK 
FOREIGN KEY (ForiegnKeyColumn) references PrimaryKeyTable (PrimaryKeyColumn)

Foreign keys are used to enforce database integrity. In layman's terms, A foreign key in one table references to a primary key in another table.

At last I need to discuss why is it so important to use Foreign Key in our Database? 

The foreign key constraint prevents invalid data form being inserted into the foreign key column. The values that you enter into the foreign key column, has to be one of the values contained in the table it points to.

Summary:
So, Guys this is all about Creating ad working with Tables in SQL Server.
I Hope in this post covered all the points about Table Creation and Foreign Key Reference which will be helpful to understand the clear concept of Foreign Key, Why do we use it?

Please share this post with your friends and colleagues.
For any queries please post a comment below.

Happy Coding 😉