Click or drag to resize

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:

 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);

Scope

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

Parameters
NameDescriptionDefault Value
RuleSuppressMark

Suppress rule reporting a rule violation in case a comment containing the value of this parameter is found next to the source of violation.

IGNORE:SA0048B

Remarks

The rule requires SQL Connection. If there is no connection provided, the rule will be skipped during analysis.

Categories

Performance Rules

Additional Information

There is no additional info for this rule.

Example Test Script
 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

 MessageLineColumn
1SA0048B : The table Table_WithOut_Pk is being created without having a primary key defined.113
See Also

Other Resources