Adding a Boolean column into an existing table

Question

I'm trying to add a boolean column into an existing table

alter table chatuser add activerecord bool;
alter table chatuser add activerecord boolean;

where activerecord is my boolean column

Neither of these queries are working. How can I add a boolean column to an existing table?

Try to be more clear about which database software you're using--don't just use a tag. — Ben Wheeler, Aug 27 '17 at 16:33
[Here is the solution](https://stackoverflow.com/a/55304942/2253682) using Android Room's SQLite library. — AdamHurwitz, Mar 22 '19 at 17:34

score 52 · Accepted Answer · answered Aug 20 '12 at 06:31

52

You have to define what you add - a column:

alter table chatuser  add column activerecord bool;

answered Aug 20 '12 at 06:31

juergen d

201,996
37
293
362

John Woo · Answer 2 · 2012-08-20T08:02:46.547

17

Lack COLUMN keyword

ALTER TABLE ChatUser ADD COLUMN ActiveRecord TinyInt(1)

edited Aug 20 '12 at 08:02

answered Aug 20 '12 at 06:32

John Woo

258,903
69
498
492

3

There is a bool data type. http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html. These types are synonyms for `TINYINT(1)` – juergen d Aug 20 '12 at 06:32
9

BOOL and BOOLEAN are synonyms for TINYINT(1) in mysql. – xdazz Aug 20 '12 at 06:33
1

`smallint` is actually kind of a waste. `tinyint` is smaller and totally sufficient for `bool` – juergen d Aug 20 '12 at 06:43
@xdazz I was not aware of that. Thanks for the the info – Arefe Mar 28 '18 at 06:47

score 14 · Answer 3 · answered Feb 10 '18 at 08:22

14

Add with default value

ALTER TABLE my_table ADD COLUMN new_field TinyInt(1) DEFAULT 0;

answered Feb 10 '18 at 08:22

Sandeep Sherpur

2,418
25
27

score 2 · Answer 4 · edited Aug 15 '13 at 22:27

2

ALTER TABLE chatuser ADD activerecord BOOLEAN

No need of word 'column'

Your second query is perfectly all right (at least) in mysql.

Try:

select * from chatuser;

If you are unable to see results, check your mysql server or other things, not the query and, if above select query works, and you do not have activerecord named column already, I bet your query will work.

edited Aug 15 '13 at 22:27

TankorSmash

12,186
6
68
106

answered Aug 20 '12 at 07:05

Sami

8,168
9
66
99

score 2 · Answer 5 · answered Feb 19 '14 at 21:06

2

I found that on Microsoft SQL the following was invalid:

ALTER TABLE meTable ADD COLUMN someBoolCol TinyInt;

Omitting the "column" keyword worked:

ALTER TABLE meTable ADD someBoolCol TinyInt;

answered Feb 19 '14 at 21:06

Alan B. Dee

5,490
4
34
29

Just so people are clear, OP asked about MySQL, not SQL in general. (Though they could have been more clear.) – Ben Wheeler Aug 27 '17 at 16:33

Adding a Boolean column into an existing table

5 Answers5