Wednesday , September 19 2018
Home / DataBase / SQL SERVER / Create Primary Key Constraint In Sql Server

Create Primary Key Constraint In Sql Server

PRIMARY KEY:-

                       A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have a unique identity which helps to find a particular record in a table more easily and quickly.   A table can have only one primary key.

 In Sql Server, primary key can be created graphically or using a query .

1.Graphically and 

2.using a query 

1.Graphically :-

By using  Graphically we can create Primary key two Ways:-

a) After Creating Table

b) At the time of Create a table

 

 a) After Creating Table:-

By using Graphically:-

Expand Databases -> Expand Database(What ever you created) -> Expand Tables->Select table Which Table You Want to  create a primary key-> Right Click on table Select Design->Select column Which column You Want to create a primary key ->then Right click on Column Name ->Set Primary Key.

 

    Or By using Alter Statement:

             syntax to create a primary key using the ALTER TABLE statement in SQL Server Shown Below Using One Column in a Table:

 

Syntax:-

 Example:-

Let’s look at an example of how to create a primary key using the ALTER TABLE statement in SQL Server

For example:

The above example, we’ve created a primary key on the existing emp table called emp. It consists of the field called emp_id.

Note:-  emp_id must already be defined as a NOT NULL field for this ALTER TABLE statement to succeed.

If the emp_id column allows NULL values, the emp table will have to be dropped and recreated with emp_id defined as a NOT NULL
field for the primary key to be created.

 

syntax to create a primary key using the ALTER TABLE statement in SQL Server Shown Below by Using More than Column in a

Table:

Syntax:-

 Example:-

Let’s look at an example of how to create a primary key using the ALTER TABLE statement in SQL Server Using More Than One Column

For example:

Note:-  emp_id and mobileno  must already be defined as a NOT NULL field for this ALTER TABLE statement to succeed.

If the emp_id or mobileno column allows NULL values, the emp table will have to be dropped and recreated with emp_id and  mobileno defined as a NOT NULL field for the primary key to be created.

 

 

 b) At the time of Create a table:-

Expand Databases -> Expand Database(What ever you created) -> Right Click on Tables-> Select New Table…->Give  Columns Names in Column Name Column and Give Data Type to Appropriate Datatype to related column->Select column Which column You Want to create a primary key ->then Right click on Column Name ->Set Primary Key.

 

2.using a query:-

We can also Create a primary key using a Query, The SQL CREATE TABLE statement allows you to create and define a primary key.

primary key constraint defines a column or combination of columns which uniquely identifies each row in the table.

Syntax to define a Primary key at column level:- : limits only column data

       Syntax

The syntax to create a primary key using the CREATE TABLE statement at column level in SQL Server is:

 

 

Syntax to define a Primary key at table level:- limits whole table data

Syntax

The syntax to create a primary key using the CREATE TABLE at table level  statement in SQL Server is:

Example

Let’s look at an example of how to create a primary key at column level  using the CREATE TABLE statement in SQL Server (Transact-SQL).

For example:

In this example, we’ve created a primary key on the employees table that is made up of only one field – the employee_id field.

We could have also created the primary key as follows:

Next, let’s look at how to create a primary key in SQL Server (Transact-SQL) where the primary key  is more than one field(at table level).

For example:

In this example, we have created a primary key that is made up of two columns, the last_name and the first_name columns. These two fields would uniquely define the records in the employees table.

 

 

 

 

 

 

 

 

Print Friendly, PDF & Email

Give us your valuable feedback

comments

About Samba

Check Also

sql server backup script

Backup all databases in SQL Instance with one script

Backup: A copy of SQL Server data that can be used to restore and recover the …