SA0048B : The table is created without a a primary key

The topic describes the SA0048B analysis rule.

Message

The table is created without a a primary key

Description

The rule checks all the CREATE TABLE statements for not having a primary key defined using a table constraint or separate ALTER TABLE statement.

Note Note

If the primary key constraint is created separately, the ALTER TABLE statement which adds the constraint should reside in the same batch as the CREATE TABLE statement.
This is required for the rule to be able to connect the constraint with the created table.

Example:

SQL
Copy
 1CREATE TABLE Example.Table1
 2(
 3 Id Id NOT NULL,
 4 Column1 varchar(30) NOT NULL,
 5);
 6
 7CREATE TABLE Example.Table2
 8(
 9 Id int NOT NULL PRIMARY KEY,
10 Column1 varchar(30) NOT NULL,
11);
12
13CREATE TABLE Example.Table3
14(
15 Id int NOT NULL PRIMARY KEY,
16 Column1 varchar(30) NOT NULL,
17);
18
19ALTER TABLE Example.Table1 ADD CONSTRAINT PK_Table3_Id PRIMARY KEY (Id);

How to fix

Create a primary key for the reported table.

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 SQL Connection. If there is no connection provided, the rule will be skipped during analysis.

Effort To Fix
20 minutes per issue.
Categories

Performance Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1CREATE TABLE Table_WithOut_Pk
 2(
 3 CustomerID int,
 4 Company varchar(30) NOT NULL,
 5 ContactName varchar(60) NOT NULL, 
 6)
 7
 8CREATE TABLE Table_With_PK
 9(
10 CustomerID int PRIMARY KEY,
11 Company varchar(30) NOT NULL,
12 ContactName varchar(60) NOT NULL, 
13)
14
15CREATE TABLE MySchema.Table_With_Added_Column_PK
16(
17 Company varchar(30) NOT NULL,
18 ContactName varchar(60) NOT NULL, 
19)
20
21ALTER TABLE MySchema.Table_With_Added_Column_PK ADD CustomerID Int not null not null constraint PK_Table_With_Added_PK primary key clustered;
22
23
24CREATE TABLE MySchema.Table_With_Added_PK
25(
26 CustomerID int NOT NULL,
27 Company varchar(30) NOT NULL,
28 ContactName varchar(60) NOT NULL, 
29)
30
31ALTER 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
See Also

Other Resources

Analysis Rules