SQL Server table Columns with NULL and NOT NULL values

This SQL article explains the SQL Server column property that has NULL or NOT NULL keywords specified in the CREATE OR ALTER table statement.

In SQL, you should explicitly define a column's NULL value behavior in most situations. It is critical to ensure the appropriate settings for each column's datatype and specify the desired NULL value settings on all columns.

For example, let's look at the following T-SQL statement for the [DemoTable]

Create Table DemoTable
(
    ID INT,
    [FirstName] varchar(10) NOT NULL,
    [MiddleName] varchar(10)  NULL,
    [LastName] varchar(10) NOT NULL
)

• We did not specify the NULL OR NOT NULL property for the ID column.

• The [FirstName] and [LastName] columns does not allow NULL values.

• The [MiddleName] columns allows NULL values.

Let’s try to insert a few records into the DemoTable.

• In the first insert statement, we specified values for all columns. Therefore, the insert statement works fine.

Insert into DemoTable Values(1, 'A','B','C')

• The second statement specifies the NULL value for the [MiddleName] column. We allowed NULL value explicitly in this column. Therefore, the records get inserted successfully.

Insert into DemoTable Values(1, 'A1',NULL,'C1')

• The following statement tries to insert a NULL value in the [ID] column. We did not specify the NULL property for the ID column. Therefore, it uses the default configuration.

To check the default configuration, let’s generate the table script. Right-click on the table in SSMS and Choose Script Table As -> CREATE TO ->New query window.

As shown below, SQL Server automatically adds a NULL clause for the ID column.

Therefore the following script successfully inserts data into DemoTable.

Insert into DemoTable Values(NULL, 'A1',NULL,'C1')

• What happens if you try to insert a NULL value into a NOT NULL column. Well, SQL Server raises the following error message.

Insert into DemoTable Values(1, NULL,'B','C')

Note: If you define a primary key on a column, it does not allow NULL values. It highlights the error if you add the keyword – PRIMARY KEY for the NULL allowed column below.

If you execute the above query, it raises an error – Cannot define a primary key constraint on the column that allows NULL values.

It would help not rely on the default configurations for the NULL property in a column table. It is always recommended to specify the NULL property using NULL or NOT NULL keyword to override any default configuration.

ALTER TABLE ADD COLUMN

Suppose you run an ALTER TABLE command to add a new column in an existing table. In that case, you get the following error message -

ALTER TABLE DEMOTABLE ADD [NewColumn] INT NOT NULL

As per the error message, you can only add a NULL column in the existing table. You can specify the NULL clause or use a default value, as shown below.

ALTER TABLE DEMOTABLE ADD
    [NewColumn] INT NULL
        CONSTRAINT df_DefaultTest_Nullable DEFAULT ''
        WITH VALUES;

OR

ALTER TABLE DEMOTABLE ADD STAGE INT NOT NULL DEFAULT '0'