Menu
I have a table that has several nullable integer columns. This is undesirable for several reasons, so I am looking to update all nulls to 0 and then set these columns to NOT NULL. Aside from changing nulls to 0, data must be preserved.
I am looking for the specific SQL syntax to alter a column (call it ColumnA) to "not null". Assume the data has been updated to not contain nulls.
Using SQL server 2000.
536
All you need to do is to replace [Table] with the name of your table, [Col] with the name of your column and TYPE with the datatype of the column. Execute the command and you are allowed to use NULL values for the specified column. That is all it takes to switch between NULL and NOT NULL .
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.
Changing the data structure of a column in SQL Server from NULL to NOT NULL , thereby disallowing non-null values in that column, is generally performed using the relatively simple ALTER TABLE syntax to appropriately change the column in question.
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 had the same problem, but the field used to default to null, and now I want to default it to 0. That required adding one more line after mdb's solution:
ALTER TABLE [Table] ADD CONSTRAINT [Constraint] DEFAULT 0 FOR [Column]; First, make all current NULL values disappear:
UPDATE [Table] SET [Column]=0 WHERE [Column] IS NULL Then, update the table definition to disallow "NULLs":
ALTER TABLE [Table] ALTER COLUMN [Column] INTEGER NOT 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
