I came across an old table today with a datetime column called 'Created' which allows nulls. Now, I'd want to change this so that it is NOT NULL, and also include a constraint to add in a default value (getdate()).
So far I've got the following script, which works fine provided that i've cleaned up all the nulls beforehand:
ALTER TABLE dbo.MyTable ALTER COLUMN Created DATETIME NOT NULL
Is there any way to also specify the default value as well on the ALTER statement?
The Simple AnswerIf the column is nullable then it will create the column with a NULL value instead of the default value, however, if column is not nullable and there is a default value, SQL Server has to apply that value to column to avoid violating not null constraint.
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.
To enforce NOT NULL for a column in MySQL, you use the ALTER TABLE .... MODIFY command and restate the column definition, adding the NOT NULL attribute.
To add not null constraint to an existing column in MySQL, we will use the ALTER command. This is a type of validation to restrict the user from entering null values.
I think you will need to do this as three separate statements. I've been looking around and everything i've seen seems to suggest you can do it if you are adding a column, but not if you are altering one.
ALTER TABLE dbo.MyTable ADD CONSTRAINT my_Con DEFAULT GETDATE() for created UPDATE MyTable SET Created = GetDate() where Created IS NULL ALTER TABLE dbo.MyTable ALTER COLUMN Created DATETIME NOT NULL
You may have to first update all the records that are null to the default value then use the alter table statement.
Update dbo.TableName Set Created="01/01/2000" where Created is NULL
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