Click or drag to resize

SA0249 : Specify default value for columns added with NOT NULL constraint

The topic describes the SA0249 analysis rule.

Message

Specify default value for columns added with NOT NULL constraint

Description

The rule checks T-SQL code for ALTER TABLE statements adding a NOT NULL column without providing a default value.

In case the table contains data, the statement will fail as there won't be a value to set in the column for the existing rows.

To resolve the rule violation provide a DEFAULT value for the added NOT NULL column.

Scope

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

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Design Rules, New Rules

Additional Information

There is no additional info for this rule.

Example Test Script
SQL
1CREATE TABLE Test.Greeting
2(
3GreetingId INT IDENTITY (1,1) PRIMARY KEY,
4Message nvarchar(255)
5)
6ALTER TABLE Test.Greeting ADD Kind int NOT NULL DEFAULT (5)
7ALTER TABLE Test.Greeting ADD Kind2 int NOT NULL

Analysis Results

 MessageLineColumn
1SA0249 : The column Kind2 is added as NOT NULL, but without a default value.730
See Also

Other Resources