Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

The Basics of Creating Tables in SQL

Introduction
Modifying table structure
Adding NOT NULL constraints
10. Adding NOT NULL columns – part 2
Summary

Instruction

If you guessed "no," then you guessed correctly. The query has failed! When we add a new column to a table with existing rows, its value is set to NULL for all rows. Therefore, we can't add the NOT NULL constraint; instead, we must add the new column without the constraint, fill in the missing data, and then (and only then) add the constraint. For instance:

ALTER TABLE store ADD COLUMN description varchar(128);
UPDATE store SET description = 'no desc available';
ALTER TABLE store ALTER COLUMN description SET NOT NULL;

First, we added a new column named description. Next, we updated the table by providing a default value in the new column for all existing rows. Finally, we added the NOT NULL constraint to the new column using SET NOT NULL.

Exercise

Add a new column no_employees (integer) to table store. It should have a value of 0 for all existing rows. Add a NOT NULL constraint to it.

Stuck? Here's a hint!

Type:

ALTER TABLE store ADD COLUMN no_employees integer;
UPDATE store SET no_employees = 0;
ALTER TABLE store ALTER COLUMN no_employees SET NOT NULL;