Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
SQL Server Transparent Data Encryption Alternative - Free Webinar
 

Adding a Primary Key to a Prepopulated Table using SQL Server Object Explorer in SSDT


By:   |   Last Updated: 2018-10-30   |   Comments (1)   |   Related Tips: More > Database Design

Problem

A SQL Server table was created without a primary key and is populated with data. I have been tasked to a add primary key to this table without worrying about removing duplicate rows in such a way that the Primary Key column becomes the first column of the table and there should be no data loss during this operation.

Solution

There are couple of ways to a add primary key to a pre-populated table which was previously created, provided data duplication is not a problem at this point.  This tip is focuses on using the SQL Server Object Explorer (SSOX) in connected mode in SQL Server Data Tools to add the primary key column as the first column of the table.

Overview: SQL Server Table Without Primary Key

The tables without primary keys are particularly useful in the following scenarios:

Insert Only Tables

Suppose there is a table which is only required for data inserts. Since the table does not have a primary key the inserts are very fast so we can create a table without the need for primary key and a clustered index.  However, getting rows from such a table using a SELECT is going to be drastically slow unless there are other indexes on the table. As long as the table is limited to inserts only it is fine without the need for primary key or a clustered index).

Backup Tables

A backup table is created by using SELECT * INTO. These are often created as temporary tables to store data, but sometimes they get used beyond their temporary life.

In order to get better understanding of how it works let's create a very simple Sale table using the following T-SQL code:

CREATE TABLE Sale (
  SaleId INT IDENTITY
 ,ProductId INT NULL
 ,SaleDate DATETIME2 NULL
 ,SaleAmount DECIMAL(10, 2) NULL
 ,CONSTRAINT PK_Sale_SaleId PRIMARY KEY CLUSTERED (SaleId)
)
GO

Insert data into the table as follows:

SET IDENTITY_INSERT [dbo].[Sale] ON
INSERT INTO [dbo].[Sale] ([SaleId], [ProductId], [SaleDate], [SaleAmount]) VALUES (1, 1, N'2017-10-02 21:35:55', CAST(300.50 AS Decimal(10, 2)))
INSERT INTO [dbo].[Sale] ([SaleId], [ProductId], [SaleDate], [SaleAmount]) VALUES (2, 1, N'2017-10-11 21:36:30', CAST(400.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Sale] ([SaleId], [ProductId], [SaleDate], [SaleAmount]) VALUES (3, 2, N'2017-10-13 21:36:55', CAST(350.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Sale] ([SaleId], [ProductId], [SaleDate], [SaleAmount]) VALUES (4, 3, N'2017-10-16 21:37:13', CAST(500.50 AS Decimal(10, 2)))
INSERT INTO [dbo].[Sale] ([SaleId], [ProductId], [SaleDate], [SaleAmount]) VALUES (5, 4, N'2017-11-14 21:37:32', CAST(460.00 AS Decimal(10, 2)))
SET IDENTITY_INSERT [dbo].[Sale] OFF

View the table:

-- Viewing Sale table
SELECT  s.SaleId
       ,s.ProductId
       ,s.SaleDate
       ,s.SaleAmount  FROM Sale s
query results

Now create a copy of the table named SaleBackup using the following script:

-- Creating backup table SaleBackup from Sale table
SELECT * INTO SaleBackup From Sale

Retrieve data from the table as follows:

-- View SaleBackup table created from Sale table
SELECT SaleId
      ,ProductId
      ,SaleDate
      ,SaleAmount FROM SaleBackup
query results

If you look at the design of the backup table SaleBackup you are going to notice it does not have any key despite the fact that it has same data as original Sale table which had a primary key.

If we comparing both tables in SQL Server Object Explorer (SSOX) or SSMS (SQL Server Management Studio). We can see the differences of the two tables below.

table structures

Staging Tables

Another example of tables without primary keys are staging tables used in data warehouse business intelligence solutions or other data movement projects. As the name indicates staging tables are meant to stage data from live data sources so that further data processing does not need to depend on the production database. Staging tables can be created without any key constraint, however, that is not always the case.

staging table

Adding a SQL Server Primary Key Using the Traditional Approach

Let us go through a traditional method of adding a primary key to a prepopulated table. Please refer to the SaleBackup table which was created in the earlier example of this tip. This table was created as a backup table without a primary key.

A traditional method is to alter the table and add primary key with an identity column as follows:

-- drop existing column that was the key
ALTER TABLE dbo.SaleBackup
Drop Column SaleID
 
-- Adding Primary Key column with identity increment 
ALTER TABLE dbo.SaleBackup
ADD SaleBackupId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED

View the table:

query results

The requirements are not completely met although we did add a primary key successfully to the table. The primary key column is not the first column in the table which is one of the main requirements.

Adding Primary Key Using SQL Server Object Explorer (SSOX)

Open SQL Server Object Explorer (SSOX) in SSDT and create a sample database.

Right click Databasesnode and click Add New Databases in SQL Server Object Explorer (SSOX):

add database

Name the database as University or alternatively create a new query against system database write the following code:

-- Creating Sample Database University
CREATE DATABASE University

Create table Student without any primary key in the University database as follows:

CREATE TABLE [dbo].[Student] (
   [Name]     VARCHAR (30)  NULL,
   [Course]   VARCHAR (30)  NULL,
   [Marks]    INT           NULL,
   [ExamDate] DATETIME2 (7) NULL
);
table structure

Populate the table using New Query in SQL Server Object Explorer (SSOX) as follows:

-- Adding data to Student table
INSERT INTO [dbo].[Student] ([Name], [Course], [Marks], [ExamDate]) VALUES (N'Asif', N'Database Management System', 80, N'2016-01-01 00:00:00')
INSERT INTO [dbo].[Student] ([Name], [Course], [Marks], [ExamDate]) VALUES (N'Peter', N'Database Management System', 85, N'2016-01-01 00:00:00')
INSERT INTO [dbo].[Student] ([Name], [Course], [Marks], [ExamDate]) VALUES (N'Sam', N'Database Management System', 85, N'2016-01-01 00:00:00')
INSERT INTO [dbo].[Student] ([Name], [Course], [Marks], [ExamDate]) VALUES (N'Adil', N'Database Management System', 85, N'2016-01-01 00:00:00')
INSERT INTO [dbo].[Student] ([Name], [Course], [Marks], [ExamDate]) VALUES (N'Naveed', N'Database Management System', 90, N'2016-01-01 00:00:00')

Right click the Student table and click View Data:

table data

Add Primary Key

Right click the Student table and click View Designer to open the table in design mode:

view designer

Go to the T-SQL Code pane and replace the existing code with the following code to add the primary key column:

-- Adding primary key to the student table
CREATE TABLE [dbo].[Student] (
    [StudentId] INT NOT NULL IDENTITY(1,1),
    [Name]     VARCHAR (30)  NULL,
    [Course]   VARCHAR (30)  NULL,
    [Marks]    INT           NULL,
    [ExamDate] DATETIME2 (7) NULL, 
    CONSTRAINT [PK_Student] PRIMARY KEY ([StudentId])
);

Press Shift+Alt+U shortcut key or click Update to update the database:

update table
update table

View tabular data to see the primary key column was added successfully:

saved table data

Congratulations! You have successfully added a primary key as the first column to a prepopulated table in such a way that it not only meets the requirements, but also has been created as the first column in the table.

Next Steps

  • Please try populating a sample table with more data using data generators and try implementing both traditional and SQL Server Object Explorer (SSOX) approach to add primary key.
  • Please try adding a Course table and link it with the Student table through CourseId and then try implementing both solutions.
  • Please refer to my previous tip Developing Similar Structured Multi-Customer Databases with SQL Server Data Tools (SSDT) and try to create Client, Service and ServiceOrder tables without primary and foreign keys and then populate these tables and finally add primary and foreign keys using the SQL Server Object Explorer (SSOX) method in this tip.
  • Please refer to my previous tip SQL Server Reference Data Best Practices - Part 1 and try creating reference tables CarMake and CarTypewithout primary keys followed by inserting data into these tables followed by adding primary keys to them.
  • Please refer to my previous tip mentioned above and practice creating and populating Car table along with CarMake and CarTypewithout primary and foreign keys and then add these keys using the method mentioned in this tip.


Last Updated: 2018-10-30


next webcast button


next tip button



About the author
MSSQLTips author Haroon Ashraf

Haroon Ashraf's interests are Database-Centric Architectures and his expertise includes development, testing, implementation and migration along with Database Life Cycle Management (DLM).

View all my tips






Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Saturday, November 03, 2018 - 8:55:40 AM - Adeela Ashraf Back To Top

A very well explained elaborating article that gives each and every detail to handle the problem  

 


Learn more about SQL Server tools