adding a new column to an existing database

purple

I've created a table and populated it with test data, but now I've realised that I need a new column. Is there anyway to add in a new column, or do I need to create a whole new database? I'm using phpmyadmin.
Thanks.

laserlight

Yes, you can add a new column. Refer to the MySQL documentation on ALTER TABLE ADD COLUMN.

purple

i'm new to using mysql and Iim struggling to find out how to add a new column to an existing table. The new column needs to have values of either yes or no, with the default being no.

I've come up with this so far:

ALTER TABLE sitename ADD admin NOT NULL;

How do I add the default bit, and only allow a 2 values to be entered?

halojoy

only allow a 2 values to be entered?

Do you mean only 2 rows can be entered? Only have 2 admins?
This I do not know how.

You need to set the type, exeample: VARCHAR(20), INT, TINYINT etc, etc.
Some default value is easy

$sql = "ALTER TABLE sitename ADD 
admin INT NOT NULL DEFAULT 1";

$sql = "ALTER TABLE sitename ADD 
admin VARCHAR(20) NOT NULL DEFAULT 'Carl Peterson'";

laserlight

purple wrote:
How do I add the default bit, and only allow a 2 values to be entered?

I am not entirely sure of the syntax as I have rare occasion to use it, but it should be something like:

ALTER TABLE sitename ADD COLUMN admin ENUM('yes', 'no') NOT NULL DEFAULT 'no';

purple

sorry, i'll try and explain again. I mean that I only want to have the option to enter one of two values into that column, either yes or no. So you would be unable to enter anything else into this column. I could set the default value to say 1 or 0 and 1 would effectively be yes and 0 no.
My question is how do I set up the column to only allow 0 and 1 to be entered? Ie I dont want it to allow me to enter any other number, only 0 and 1.

Hope thinks makes more sense.

EDIT: I was just playing around with the type column (varchar(), date etc) in phpmyadmin and found ENUM. Does this allow me to enter only 1s and 0's if I set the type to ENUM('0', '1')?

laserlight

purple wrote:
I was just playing around with the type column (varchar(), date etc) in phpmyadmin and found ENUM. Does this allow me to enter only 1s and 0's if I set the type to ENUM('0', '1')?

It should. Did you read my post #5?

purple

no, sorry. i'd already clicked on the reply link before you'd posted so I didnt see it.
that is what i meant though, thanks.

bradgrafelman

Sounds like you want to store a simple boolean TRUE or FALSE (e.g. 1 or 0). If this is the case, I'd recommend using the BOOL, BOOLEAN column type.

While this is presently just an alias for TINYINT(1), the MySQL manual does state that the develops intend to provide full BOOLEAN support in future releases.