ALTER TABLE question

[Richard Hollenbeck]

I made a table this way:

mysql>create table tblCourses (courseCode char(30) primary key,
courseDescripti on 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, courseDescripti on 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, courseDescripti on not null)' at line 1

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

[2metre]

Re: ALTER TABLE question

Richard Hollenbeck wrote:[color=blue]
> I made a table this way:
>
> mysql>create table tblCourses (courseCode char(30) primary key,
> courseDescripti on 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, courseDescripti on 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, courseDescripti on not null)' at line 1
>
> Do I need to drop the table and start over? Or how could I add this?
>[/color]

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

[Bill Karwin]

Re: ALTER TABLE question

Richard Hollenbeck wrote:
[color=blue]
> mysql>create table tblCourses (courseCode char(30) primary key,
> courseDescripti on char(50));
>
> Now I realize that I forgot to enforce that both fields will not be null.[/color]

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 courseDescripti on 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.