SQL Prompt is a useful SQL syntax prompt tool.It automatically retrieves object names, syntax, and code snippets based on the database, providing users with appropriate code choices.Automatic script settings make the code easy to read - especially if the developer is not familiar with scripts.The SQL Prompt installation is ready to use and can greatly improve encoding efficiency.In addition, users can customize it to work the way they want.

This article explains the problems you might encounter when adding non-nullable columns to an existing table or changing columns that contain nullable values to non-nullable.Demonstrates a migration script that can safely deploy such changes.

This is a common ritual when designing a database to add or remove NULL constraints, but there are issues that can be painful when you make changes to a populated table.This can happen when you try to add a new column that cannot accept NULL values or change an existing nullable column to a NOT NULL column.If SQL Prompt detects code that will attempt to add a NOT NULL column to an existing table, it will warn you (EI028) without specifying a default value.

I'll demonstrate these issues and show you how to develop build scripts that apply these types of changes.I'll show you how this works, whether you're building a new version of the table from scratch, making changes, or if you need to update an existing table so that it contains those changes.

Add NOT NULL column to populated table

We have a table where CountingWords records the number of words used by the old Wales to calculate.Our goal is to count to 20, but we currently only know how to count to 10.

/* we create a table. Just for our example, we create the words
used to count from one to ten in old Welsh.
(Na, nid wyf yn siaradwr Cymraeg ond rwy'n hoffi gwneud 
rhywfaint o ymchwil ieithyddol gyda'r nos)
I'd like to do from eleven to twenty as well eventually
so I'll add them and leave them NULL.
Here is the initial build script, with guard clauses
of course. We'll re-create every time it is run. 
With a few modifications we can make it so it only runs once 
which is safer if you have reckless colleagues in your shop.
 */
IF Object_Id('dbo.CountingWords') IS NOT NULL DROP TABLE dbo.CountingWords;
--we script version 1 of our table of counting words
CREATE TABLE dbo.CountingWords
  (
  TheValue INT NOT NULL,
  Word NVARCHAR(30) NULL,
  CONSTRAINT CountingWordsPK PRIMARY KEY (TheValue)
  );
GO
INSERT INTO dbo.CountingWords (TheValue, Word)
VALUES
  (1, 'Un'),  (2, 'Dau'),  (3, 'Tri'),  (4, 'Pedwar'),
  (5, 'Pump'),  (6, 'Chwech'),  (7, 'Saith'),  (8, 'Wyth'),
  (9, 'Naw'),  (10, 'Deg'),  (11, NULL),  (12, NULL),
  (13, NULL),  (14, NULL),  (15, NULL),  (16, NULL),
  (17, NULL),  (18, NULL),  (19, NULL),  (20, NULL);
GO

Listing 1: Un, Dau, Tri - CountingWords Table Version 1

After publishing the first version of this table, we quickly realized that we really should record the name of the language, so we changed the design of the table to add a column that TheLanguage could not accept NULLs.

ALTER TABLE dbo.CountingWords ADD TheLanguage NVARCHAR(100) NOT NULL;

SQL Prompt immediately warns us of the danger:

If we ignore the warning from SQL Prompt and do this, we will get an error.

Message 4901, Level 16, Status 1, Line 34

ALTER TABLE only allows adding columns that can contain null values, or specifies a DEFAULT definition, or adds columns that are identity or timestamp columns, or that must be empty if no previous column condition meets the table to allow adding this column.Column'TheLanguage'cannot be added to non-empty table'CountingWords' because it does not satisfy these conditions.

The error message is explicit and can be easily fixed by defining a DEFAULT constraint so that SQL Server can insert the default value for this new column for each row.

ALTER TABLE dbo.CountingWords ADD TheLanguage NVARCHAR(100) NOT NULL
          DEFAULT 'Old Welsh';

Listing 2: Specify default values when adding NOT NULL columns

In short, if you want to add columns and you don't allow NULL values, you must provide a value to put each row.Because we only have one language,'Old Welsh', it's not too difficult.

Of course, we also want to record how other languages are calculated, such as Manx, Cornish, or Cumbrian, so to enforce some data integrity, we need to create a parent table called Location Defines Each Language, and their initial record.

Let's get it out.For this table, we cannot just discard and recreate tables without data loss, and once our CountingWords table references it through the FOREIGN KEY constraint, we get an error.We need to take precautions.If I rerun the code, I'll use a simple but rather strange technique to make sure there's no damage to the data loss.This script must be run as multiple batches because the CREATE TABLE statement must be at the beginning of the batch and it is difficult to conditionally execute code across batches.

IF Object_Id('dbo.Location') IS NOT NULL SET NOEXEC ON;
 --cunning way of only executing a section
--of code on a condition. until the next SET NOEXEC OFF
--we script version 1 of our table of counting words
GO
--sadly the create table statement has to be at the start of a batch
CREATE TABLE dbo.Location
  (
  TheLanguage NVARCHAR(30) NOT NULL,
  Description VARCHAR(100) NOT NULL DEFAULT '',
  CONSTRAINT LanguageKey PRIMARY KEY (TheLanguage)
  );
--now we insert the row we need for our existing data
INSERT INTO dbo.Location (TheLanguage) VALUES ('Old Welsh');
GO
SET NOEXEC OFF;

Listing 3: Version 1 of the Location table

Of course, now we need to modify the CountingWords table so that its TheLanguage column is a FOREIGN KEY and refers to the new Location table, but we'll work on it later.

Change nullable columns so they are not nullable

Soon, we decided to allow NULL values in columns as design errors we wanted to resolve.We have learned that if a table contains data, SQL Server will not allow us to make a column not nullable unless we provide it with a default value, in which case it is just an empty string.

ALTER TABLE CountingWords ADD CONSTRAINT WordConstraint DEFAULT '' FOR Word;
ALTER TABLE CountingWords ALTER COLUMN Word NVARCHAR(30) NOT NULL;

Message 515, Level 16, Status 2, Line 58

Value NULL cannot be inserted into the Word column, table'PhilFactor.dbo.CountingWords'; null values are not allowed in columns.Update failed.

The statement has been terminated.

Listing 4: Attempting to fail the Word column NOT NULL

AIEE!We still can't make a column nullable, even if we tell SQL Server what to insert for a NULL column!First, we must explicitly delete any existing NULL values by updating all rows with default values:

UPDATE CountingWords SET Word = DEFAULT WHERE Word IS NULL;
ALTER TABLE CountingWords ALTER COLUMN Word NVARCHAR(30) NOT NULL;

The content of this tutorial is not yet complete. Please look forward to the following ~