Menu
Adding a new column to my database with a NOT NULL constraint but Redshift is alerting me that I should define a default value. I thought the point of NOT NULL was to force a definition?
ALTER TABLE users add column
name varchar(255) NOT NULL
;
Results in:
[Amazon](500310) Invalid operation: ALTER TABLE ADD COLUMN defined as NOT NULL must have a non-null default expression;
Is there a way to enforce a column must not be null?
768
By default, a column can hold NULL values. The NOT NULL constraint enforces a column to NOT accept NULL values. This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.
If a column is defined as NOT NULL WITH DEFAULT or if you do not specify NOT NULL, Db2 stores a default value for a column whenever an insert or load does not provide a value for that column. If a column is defined as NOT NULL, Db2 does not supply a default value.
The DEFAULT constraint is used to set a default value for a column. The default value will be added to all new records, if no other value is specified.
The DEFAULT value constraint only applies if the column does not have a value specified in the INSERT statement. You can still insert a NULL into an optional (nullable) column by explicitly inserting NULL.
NOT NULL is the correct way to ensure a column contains a value.
However, because your table already exists, the rows which are already in it won't have a value for your new name column, therefore they would breach the NOT NULL constraint.
By specifying a default value, you are ensuring that any existing rows are assigned that value, thereby complying with your NOT NULL constraint.
85
If the column is NOT NULL, it has to have a value, right?
Why not add: DEFAULT default_expr
22
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
