ALTER TABLE question

[Richard Hollenbeck]

I made a table this way:

mysql>create table tblCourses (courseCode char(30) primary key,
courseDescription char(50));

Now I realize that I forgot to enforce that both fields will not be null.
So I tried this:

mysql> alter table tblCourses (courseCode not null, courseDescription not
null);

but I got this result:

ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use near
'(courseCodenot null, courseDescription not null)' at line 1

Do I need to drop the table and start over? Or how could I add this?

[2metre]

Richard Hollenbeck wrote:

I made a table this way:

mysql>create table tblCourses (courseCode char(30) primary key,
courseDescription char(50));

Now I realize that I forgot to enforce that both fields will not be null.
So I tried this:

mysql> alter table tblCourses (courseCode not null, courseDescription not
null);

but I got this result:

ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use near
'(courseCodenot null, courseDescription not null)' at line 1

Do I need to drop the table and start over? Or how could I add this?

Check the manual that
corresponds to your MySQL server version for the right syntax

[Bill Karwin]

Richard Hollenbeck wrote:

mysql>create table tblCourses (courseCode char(30) primary key,
courseDescription char(50));

Now I realize that I forgot to enforce that both fields will not be null.

It turns out that you can't alter simply the nullable/not nullable
attribute of a column. To change this attribute, you must list the
whole column definition, including name, datatype, etc.

alter table tblCourses
modify column courseCode char(30) not null primary key,
modify column courseDescription char(50) not null;

See http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html

I'm not sure what the reason is for this limitation, since there is
syntax to alter certain other attributes, such as the default. But
that's the way it is at least in current versions of MySQL.

Regards,
Bill K.