SQL Server Primary key is a single field or combination of fields that is used to uniquely define a record. Any field of a primary key cannot contain a null value. A table can have only one primary key.

You can define a primary key either in a CREATE TABLE statement or an ALTER TABLE statement.

Create Primary Key Using CREATE TABLE Statement

Syntax:

CREATE TABLE table_name
(  column1 datatype [ NULL | NOT NULL ] [ PRIMARY KEY ]
  column2 datatype [ NULL | NOT NULL ],  ...);

CREATE TABLE table_name(
column1 datatype [ NULL | NOT NULL ],
column2 datatype [ NULL | NOT NULL ],
...CONSTRAINT constraint_name 
PRIMARY KEY (column1, column2, ... column_n));

Example:

Create a table “cricketers” where “cricketer_id” is a primary key.

CREATE TABLE cricketers
( cricketer_id INT PRIMARY KEY,
last_name VARCHAR(50) NOT NULL,
first_name VARCHAR(50) NOT NULL,
salary MONEY
);

Output:

You can verify that created table by using SELECT command:

SELECT *
FROM [tecklearn].[dbo].[cricketers]

Output:

Here cricketer_id is the primary key.

You can also create a table with primary key by using the second syntax:

Example:

CREATE TABLE cricketers2
( cricketer_id INT,
last_name VARCHAR(50) NOT NULL,
first_name VARCHAR(50) NOT NULL,
salary MONEY,
CONSTRAINT cricketers2_pk PRIMARY KEY (cricketer_id)
);

Output:

You can verify that created table by using SELECT command:

SELECT * FROM [tecklearn].[dbo].[cricketers2]

Output:

Create a primary key in SQL Server where the primary key is more than one field:

Example:

CREATE TABLE cricketers3
( last_name VARCHAR(50) NOT NULL,
first_name VARCHAR(50) NOT NULL,
salary MONEY,
CONSTRAINT cricketers3_pk PRIMARY KEY (last_name, first_name)
);

Output:

You can verify that created table by using SELECT command:

SELECT *
FROM [tecklearn].[dbo].[cricketers3]

Output:

Here, last_name and first_name both is primary key.

Create Primary Key Using ALTER TABLE statement

You can use an ALTER TABLE statement to create a primary key only on the column or columns that are already defined as NOT NULL.

If a column of a table has NULL values, you cannot add a primary key without dropping and recreating the table.

Syntax:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n);

Example:

Create a primary key “cricketer_id” in the table “cricketers2”.

ALTER TABLE [tecklearn].[dbo].[cricketers2]
ADD CONSTRAINT cricketers2_pk PRIMARY KEY (cricketer_id);

Output:

It will create a primary key “cricketer_id” in the “cricketers2” table.

Enable Primary Key

ALTER INDEX statement is used to enable a primary key in SQL Server database.

Syntax:

ALTER INDEX constraint_name ON table_name
REBUILD;

Example:

Enable the primary key “customer_id” on the table “customers”.

ALTER INDEX cricketers2_pk ON [tecklearn].[dbo].[cricketers2]
REBUILD;

Output:

Now primary key “customer_id” is enabled in table “customers”.

Disable Primary key

ALTER INDEX statement is used to disable a primary key in SQL Server database.

Syntax:

ALTER INDEX constraint_name ON table_name
DISABLE;

Example:

Disable the primary key “customer_id” on the table “customers”.

ALTER INDEX cricketers2_pk ON [tecklearn].[dbo].[cricketers2]
DISABLE;

Output:

Drop Primary Key

ALTER TABLE statement is used to drop a primary key in SQL Server.

Syntax:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Example:

Let’s drop a primary key using the ALTER TABLE statement in SQL Server. Here we take “cricketers2” table which has “cricketer_id” as a primary key and “cricketers2_pk” as constraint name.

ALTER TABLE [tecklearn].[dbo].[cricketers2]
DROP CONSTRAINT cricketers2_pk ;

Output:

So, this brings us to the end of blog. This Tecklearn ‘Concept of Primary Key in SQL Server’ blog helps you with commonly asked questions if you are looking out for a job in MS-SQL Server. If you wish to learn MS-SQL Server and build a career in Database domain, then check out our interactive, MS-SQL Server Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/ms-sql-server-training-and-certification-course/

MS-SQL Server Training

About the Course

Microsoft SQL Server training course is an online classroom training meant for developers to master the descriptive language to work for relational databases. Important topics included in this SQL course are SQL introduction, relational databases, queries, subqueries, joins, and unions. After completing this course, you will also become proficient in the theory of optimization, transactions, indexing, installing SQL Server, database administration, clustering, implementing programmability objects, designing and implementing database objects, managing database concurrency, and optimizing SQL infrastructure and database objects.

Why Should you take MS-SQL Server Training?

The average salary for Sql server developer ranges from approximately $88,666 per year for Database Developer to $115,811 per year for Senior SQL Developer. – Indeed.com
Wells Fargo, UPS, US Bank, Perspecta, Silicon Valley Bank, Mindtree Wipro, Infosys & many other MNC’s worldwide use MS SQL Server for their Database deployments.
According to Gartner, MS SQL Server is the market leader in the Relational Database with 18.5% market share globally.

What you will Learn in this Course?

Introduction to SQL

Various types of databases
Introduction to SQL
Installation Steps of MS-SQL Server
Overview of Microsoft SQL Server Management Studio
SQL architecture, client/server relation
Database types

Introduction to relational databases, basic concepts of relational tables

Working with rows and columns
Various operators used like logical and relational
Constraints, Primary Key and Foreign Key
Insert, Update, Delete, Alter

Working with SQL: Join, Tables, and Variables

SQL Operators and Queries
SQL functions
Creation of Table
Retrieval of Data from tables
Combining rows from tables using joins
Operators such as intersect, except, union, temporary table creation, set operator rules, table variables etc

Deep Dive into SQL Functions

Data Retrieval Language
Functions
Operators
Clauses
Sub-Queries
Correlated Sub-Queries

SQL Views, Functions

Sub-queries
Views
Indexes

Managing Data with Transact-SQL

Transact-SQL queries
Implementing functions and aggregating data
Determining the results of DDL statements on supplied tables and data

Cursor Management and Triggers

Cursors
Triggers
Types of Triggers

Stored Procedures and User Defined functions

Understanding of Stored Procedures
Key benefits of Stored Procedures
Studying user-defined functions

Deep Dive into User-defined Functions

Detailed study of user-defined functions
Various types of UDFs like Scalar, Inline Table Value, multi-statement Table
What is Rank Function? Triggers, when to execute Triggers?

SQL Optimization and Performance

Concepts of Concurrency and Locking behaviour
Usage of memory-optimized tables to solve issues
Examining and Troubleshooting of query plans
Performance management of Database instances
SQL server Performance Monitoring

Got a question for us? Please mention it in the comments section and we will get back to you.

526