SA0188 : The NULL or NOT NULL constraint not explicitly specified in the table column definition

Introduction

Missing explicit NOT NULL constraint in table column definitions can lead to unintended nullability, affecting data integrity and application logic.

Description

In T-SQL code, when creating tables, it’s crucial to specify whether columns should allow NULL values. Failing to explicitly define a NOT NULL constraint results in SQL Server defaulting to allowing NULL values, which may not be the intended behavior.

For example:

-- Example of a table definition without an explicit NOT NULL constraint
CREATE TABLE Employees (
    EmployeeID INT,
    EmployeeName VARCHAR(100)
);

Without clearly specifying NOT NULL , columns like EmployeeName can inadvertently accept NULL values. This oversight might lead to inconsistent data, as columns intended to store essential information could end up with nulls, disrupting application logic and reporting.

Data integrity issues emerge when essential fields are left nullable unintentionally.
Application logic errors might occur if the code assumes certain fields are non-nullable, while they aren’t enforced in the database schema.

How to fix

Specify the nullability of columns explicitly when creating tables to avoid unintended nullability that affects data integrity and application logic.

Follow these steps to address the issue:

1.Review your table definitions to identify columns lacking explicit nullability specifications. Use INFORMATION_SCHEMA.COLUMNS to query and review existing schema details.

2.Decide whether each column should allow NULL values or not based on the data integrity needs and application requirements.

3.Modify the table definition to explicitly define the columns as NOT NULL if null values are not appropriate. This is typically done during table creation but can be altered using ALTER TABLE statements.

For example:

-- Example of corrected table definition
CREATE TABLE Employees (
    EmployeeID INT NOT NULL,
    EmployeeName VARCHAR(100) NOT NULL
);

Scope

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

Parameters

Name	Description	Default Value
CheckTableVariables	The parameter specifies whether to check table variable definitions and report missing NULL constraints.	yes

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

5 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

CREATE TABLE Test.Greeting
(
GreetingId INT IDENTITY (1,1) PRIMARY KEY,
Message nvarchar(255) NOT NULL,
)

ALTER TABLE Test.Greeting ADD Column2 INT, Column2 INT NULL

ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20), column_c INT NULL ;

ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL;

DECLARE @greetingsTable AS TABLE
(
  GreetingId INT IDENTITY (1,1) PRIMARY KEY,
  Message nvarchar(255) NOT NULL,
)

Analysis Results

	Message	Line	Column
1	SA0188 : The NULL or NOT NULL constraint not explicitly specified in the table column definition.	3	0
2	SA0188 : The NULL or NOT NULL constraint not explicitly specified in the table column definition.	7	30
3	SA0188 : The NULL or NOT NULL constraint not explicitly specified in the table column definition.	9	28
4	SA0188 : The NULL or NOT NULL constraint not explicitly specified in the table column definition.	15	1

Analysis Rules