2

If I alter the table and add a new column will add the data be wiped off from that table. 

ALTER TABLE MyTable ADD COLUMN noOfDays integer default 0 NOT NULL

Will the above SQL command drop MyTable and then ALTER it with the noOfDays column or will it simply add the noOfDays column without dropping it.

john doe
  • 9,220
  • 23
  • 91
  • 167

1 Answers1

5

No, add just adds the column. Neither its data will be deleted nor the table be dropped.

p.s. from the documentation
"Note: also that when adding a CHECK constraint, the CHECK constraint is not tested against preexisting rows of the table." (check SQLite manual for more)

Trinimon
  • 13,839
  • 9
  • 44
  • 60
  • As far as I remember you have to update the default value to existing rows (couldn't find it in the documentation right now); check for instance http://stackoverflow.com/questions/3492947/insert-a-not-null-column-to-an-existing-table – Trinimon Mar 27 '13 at 19:51
  • I believe my SQL above will create a noOfDays column and insert 0 as the default value. – john doe Mar 27 '13 at 19:54
  • Why did you do this UPDATE MyTable SET noOfDays = 0; should it not default to 0 once the column is created! – john doe Mar 27 '13 at 19:56
  • Sorry for the confusion; your `ALTER` command works well; however, there is a restriction on check constraints - will add this as a note above – Trinimon Mar 27 '13 at 20:07