By: Haroon Ashraf | Updated: 2018-10-30 | Comments (1) | Related: > 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
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
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.
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.
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:
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):
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 );
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:
Add Primary Key
Right click the Student table and click View Designer to open the table in design mode:
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:
View tabular data to see the primary key column was added successfully:
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.
About the author
Haroon Ashraf's interests are Database-Centric Architectures and his expertise includes development, testing, implementation and migration along with Database Life Cycle Management (DLM).This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2018-10-30
