SA0249 : Specify default value for columns added with NOT NULL constraint |
 |
The topic describes the SA0249 analysis rule.
MessageSpecify default value for columns added with NOT NULL constraint
DescriptionThe 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.
How to fixSpecify default value for the column added with NOT NULL constraint.
ScopeThe rule has a Batch scope and is applied only on the SQL script.
ParametersRule has no parameters.
RemarksThe rule does not need Analysis Context or SQL Connection.
Effort To Fix8 minutes per issue.
CategoriesDesign Rules, Code Smells
Additional InformationThere is no additional info for this rule.
Example Test SQL|
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| Message | Line | Column | |
|---|---|---|---|
| 1 | SA0249 : The column Kind2 is added as NOT NULL, but without a default value. | 7 | 30 |
See Also