SA0048B : The table is created without a a primary key

Introduction

Ensure all tables have a primary key defined immediately to avoid integrity and performance issues.

Description

In SQL Server, tables ideally should have a primary key to enforce data integrity and optimize database operations. A primary key uniquely identifies each record in a table and assists in maintaining orderly data storage, retrieval, and relationships between tables.

For example:

-- Example of poor practice
CREATE TABLE Example.Table1
(
 Id int NOT NULL,
 Column1 varchar(30) NOT NULL
);

-- Example of best practice
CREATE TABLE Example.Table2
(
 Id int NOT NULL PRIMARY KEY,
 Column1 varchar(30) NOT NULL
);

If a primary key is not defined at table creation or added promptly via an ALTER TABLE statement in the same batch, several issues may arise.

Data integrity risk due to potential duplicate entries, as each record should be uniquely identifiable.
Performance degradation, as operations like indexing and queries rely on a well-defined primary key for optimization.
Difficulty in establishing relationships with other tables, hindering normalization and efficient data modeling.

How to fix

Define a primary key for tables missing one to enhance data integrity and performance.

Follow these steps to address the issue:

1.Identify the table lacking a primary key by reviewing the database schema or using SQL analysis tools.

2.Determine the appropriate column or combination of columns that uniquely identify each record in the table. These columns should not contain null values and should represent unique data.

3.If you are creating a new table, define the primary key within the CREATE TABLE statement.

4.For an existing table without a primary key, use the ALTER TABLE statement to add the primary key. Ensure that the column(s) chosen do not contain duplicate values or NULLs before performing this operation.

For example, when creating a new table or altering an existing one:

-- Creating a new table with a primary key
CREATE TABLE Example.Table1
(
    Id int NOT NULL PRIMARY KEY,
    Column1 varchar(30) NOT NULL
);

-- Adding a primary key to an existing table
ALTER TABLE Example.Table2
ADD CONSTRAINT PK_Table2_Id PRIMARY KEY (Id);

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Name	Description	Default Value
IgnoreTemporaryTables	Ignore temporary tables.	yes

Remarks

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Effort To Fix

20 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

CREATE TABLE Table_WithOut_Pk
(
 CustomerID int,
 Company varchar(30) NOT NULL,
 ContactName varchar(60) NOT NULL,
)

CREATE TABLE Table_With_PK
(
 CustomerID int PRIMARY KEY,
 Company varchar(30) NOT NULL,
 ContactName varchar(60) NOT NULL,
)

CREATE TABLE MySchema.Table_With_Added_Column_PK
(
 Company varchar(30) NOT NULL,
 ContactName varchar(60) NOT NULL,
)

ALTER TABLE MySchema.Table_With_Added_Column_PK ADD CustomerID Int not null not null constraint PK_Table_With_Added_PK primary key clustered;


CREATE TABLE MySchema.Table_With_Added_PK
(
 CustomerID int NOT NULL,
 Company varchar(30) NOT NULL,
 ContactName varchar(60) NOT NULL,
)

ALTER TABLE Table_With_Added_PK ADD CONSTRAINT PK_Table_With_Added_PK primary key (CustomerID)

Analysis Results

	Message	Line	Column
1	SA0048B : The table Table_WithOut_Pk is being created without having a primary key defined.	1	13

Analysis Rules