Wednesday , January 17 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

Find all rows count and columns count in single database in sql server

In General for any database it contains lot of tables, if we want to find …

%d bloggers like this: