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

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:

SQL
Copy
1-- Example of a table definition without an explicit NOT NULL constraint
2CREATE TABLE Employees (
3    EmployeeID INT,
4    EmployeeName VARCHAR(100)
5);

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:

SQL
Copy
1-- Example of corrected table definition
2CREATE TABLE Employees (
3    EmployeeID INT NOT NULL,
4    EmployeeName VARCHAR(100) NOT NULL
5);

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.
Categories

Design Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1CREATE TABLE Test.Greeting
 2(
 3GreetingId INT IDENTITY (1,1) PRIMARY KEY,
 4Message nvarchar(255) NOT NULL,
 5)
 6
 7ALTER TABLE Test.Greeting ADD Column2 INT, Column2 INT NULL
 8
 9ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20), column_c INT NULL ;  
10
11ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL; 
12
13DECLARE @greetingsTable AS TABLE 
14(
15        GreetingId INT IDENTITY (1,1) PRIMARY KEY,
16        Message nvarchar(255) NOT NULL,
17)
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
See Also

Other Resources

Analysis Rules