Menu
I have to add a column whose default value is not null by default to the table after particular column using Alter table.
ALTER TABLE tblechecklistrevision ADD COLUMN IWorkFlowOrder INT(10) DEFAULT NOT NULL AFTER fState;
When I Execute the query I will get the below error
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT NULL AFTER fState' at line 1
667
You can add a not null column at the time of table creation or you can use it for an existing table. In the above table, we have declared Id as int type that does not take NULL value. If you insert NULL value, you will get an error. Here is the query to add a not null column in an existing table using alter command.
Code Inspection: Insert NULL into NOT NULL column You cannot insert NULL values in col1 and col2 because they are defined as NOT NULL. If you run the script as is, you will receive an error. To fix this code, replace NULL in the VALUES part with some values (for example, 42 and 'bird' ).
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.
ALTER TABLE SomeTable ADD SomeCol Bit NULL --Or NOT NULL. CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated. DEFAULT (0)--Optional Default-Constraint. WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.
You should remove DEFAULT:
ALTER TABLE tblechecklistrevision
ADD COLUMN IWorkFlowOrder INT(10) NOT NULL AFTER fState;
DEFAULT is for setting initial value to new rows where a value for that column isn't specified, when you write ...INT(10) NOT NULL what you mean is actually that that column can never contain a NULL, not only at initialization time.
97
If you want the default value not to equal NULL (example 0) you can do:
ALTER TABLE tblechecklistrevision
ADD COLUMN IWorkFlowOrder INT(10) NOT NULL DEFAULT 0 AFTER fState
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
