0

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?

Lieuwe
  • 1,734
  • 2
  • 27
  • 41
  • find solution from this page , how to add not null https://stackoverflow.com/questions/6305225/how-to-add-not-null-constraint-to-existing-column-in-mysql – prashant shah Feb 09 '22 at 12:59
  • check this page for adding not null constrain https://stackoverflow.com/questions/6305225/how-to-add-not-null-constraint-to-existing-column-in-mysql – prashant shah Feb 09 '22 at 13:01
  • So I can't simply add this not null constraint. I have to rewrite the entire column definition? Ok - thx. – Lieuwe Feb 09 '22 at 13:05

2 Answers2

0

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

Caius Jard
  • 72,509
  • 5
  • 49
  • 80
0

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;

prashant shah
  • 11
  • 4