I want to update a column with a default value. I do that like so:
ALTER TABLE `db`.`tbl` ALTER COLUMN `some_timestamp` SET DEFAULT current_timestamp();
that works fine. I also want to add NOT NULL. How do I do that?
-
find solution from this page , how to add not null stackoverflow.com/questions/6305225/…prashant shah– prashant shah2022-02-09 12:59:52 +00:00Commented Feb 9, 2022 at 12:59
-
check this page for adding not null constrain stackoverflow.com/questions/6305225/…prashant shah– prashant shah2022-02-09 13:01:09 +00:00Commented Feb 9, 2022 at 13:01
-
So I can't simply add this not null constraint. I have to rewrite the entire column definition? Ok - thx.Lieuwe– Lieuwe2022-02-09 13:05:18 +00:00Commented Feb 9, 2022 at 13:05
Add a comment
|
2 Answers
Like so:
ALTER TABLE `db`.`tbl` MODIFY `some_timestamp` TIMESTAMP NOT NULL DEFAULT current_timestamp();
ALTER COLUMN is only used for setting defaults in MySQL; MODIFY <colname> <colspec> modifies a column spec but doesn't rename it. To rename also, use CHANGE <oldname> <newname> <colspec> and specify oldname newname
Comments
you can add multiple constrains by following way also ALTER TABLE MEMBER ADD CONSTRAINT U_MEMBERID UNIQUE(MEMBER_ID), primary key (MEMBER_ID), CONSTRAINT Sys_date DEFAULT GETDATE() FOR Sys_date;
