Post
Ask Question

How to Create Table in SQL Server with Auto Increment, Primary Key and Foreign Key.

Introduction

SQL is a standard language for storing, manipulating, and retrieving data in databases. SQL stands for Structured Query language, pronounced as “S-Q-L”. It is a programming language that has a standardized database and is used for modification of the database and retrieving the information from tables.

Modification of database tables, along with retrieving information subsets, can be done through SQL. Here we will learn how to create and alter tables in an SQL server using SQL queries. We also create here the primary key, the foreign key.

Preconditions

We cover the below things,

  • Create SQL table
  • Alter SQL table
  • Primary key
  • Foreign Key

Let's start with SQL

Firstly we have to create a database for using a table.

Use the following command to create the database.

Create database MySQLDemoDb

Here "MySQLDemoDb" is our database name.

Now step by step, we learn about the SQL table and constraint.

Step 1. Here, we will create two table(Parent and Child). We will create "tblMyEmployee" as the primary table and "tblMyDepartment" as the sub-table.

Sub table is used to reduce redundancy and complexity. It is helpful to divide the large database table into smaller tables and link them using relationships.

For creating a new table in the database, we use the "Create table" statement.

First, we will learn how to create the table in an SQL server. Run the below query for creating Employee and department table.

Create table tblMyDepartment (
  Id int primary key identity(1, 1), 
  DepartmentName varchar(50), 
  IsActive bit
)

Create table tblMyEmployee (
  Id int primary key identity(1, 1), 
  EmpName varchar(20), 
  EmpAddress varchar(max), 
  JoiningDate datetime, 
  CreateDate datetime default(
    getdate()
  ), 
  Salary decimal(5, 2), 
  PhoneNumber varchar(10), 
  DepartmentId int foreign key references tblMyDepartment(Id), 
  IsActive bit
)

We add dome dummy data using insert Insert statement.

  • The column parameters specify the names of the columns of the table.
  • Here we see "varchar, int, decimal, etc." these are the datatype. In SQL, datatype defines the type of data that column can hold (e.g. varchar, int, decimal, date, etc.).

We create Id as the "Primary key".

  •  It is used for identifying each record of a table uniquely.
  •  The primary key column can't be duplicated or null.
  • We can only take one primary key in a table; this primary key can consist of single or multiple columns (ex., Primary key(ID) or  Primary key(ID, EmpName, EmpAddress)).

Here DepartmentId we created as "Foreign Key". 

  • It is a field or collection of fields in one table that refers to the "Primary Key" in another table. It is used to prevent activities that would destroy links between tables.

We used the "Identity" keyword to perform an auto-increment feature.

  • The Identity keyword is used for auto-increment for records at the time of insertion in the SQL table.
  • We used identity as "Identity(1,1)"; this means records start with 1, and it will increment by 1 after inserting every new record.

Step 2

The ALTER TABLE statement is used to add, delete, or modify columns or constraints in an existing table.

Here we are trying to add the EmailAddress column of datatype "varchar" with a size "50" charactor in the "tblMyEmployee" table.

To add a column in a table, use the following syntax,

Alter table tblMyEmployee

Add EmailAddress varchar(50)

Now we are deleting the "PhoneNumber" column from "tblMyEmployee".

To delete a column in a table, use the following syntax

Alter table tblMyEmployee

Drop column PhoneNumber

If we want to change the data type of the existing column, then we use the below query.

Here we are changing the datatype of  "PhoneNumber" to "nvarchar" with size "10".

Alter table tblMyEmployee

Alter PhoneNumber nvarchar(10)

Summary

This article teaches us how to create a SQL table with different datatype and constraints. We also learned how to alter a table and add, modify, and delete columns in an existing table of SQL Server.


Similar Articles