Introduction
Modifying table structure
Adding NOT NULL constraints
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;
