Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Development Tools Database Foundations Creating Tables and Manipulating Data with SQL Creating a Table

Mayur Pande
PLUS
Mayur Pande
Courses Plus Student 11,711 Points
Posted by Mayur Pande
Mayur Pande
Courses Plus Student 11,711 Points

How do I go about changing a column from NULL to NOT NULL?

I have tried to use:

ALTER TABLE documentaries ALTER COLUMN production_credit TEXT NOT NULL

But it says I have error code 1064 syntax error

1 Answer

Jason Anello
PLUS
Jason Anello
Courses Plus Student 94,610 Points
Jason Anello
Jason Anello
Courses Plus Student 94,610 Points

Hi Mayur,

The ALTER keyword is used for setting a new default value or removing the existing one.

MODIFY should be what you're looking for. The COLUMN keyword is optional.

ALTER TABLE documentaries MODIFY production_credit TEXT NOT NULL;

It's important that you specify any previous attributes that may have been set in addition to NOT NULL. Otherwise, you will lose the previous attributes you set when the table was created.

Give that a try and post back if it's still not working.

Mayur Pande
Mayur Pande
Courses Plus Student 11,711 Points
Mayur Pande
Mayur Pande
Courses Plus Student 11,711 Points

Thanks Jason,

With regards to setting the previous attributes do you mean TEXT in this situation?

Jason Anello
Jason Anello
Courses Plus Student 94,610 Points

What follows after the column name is the column_definition.

This consists of the data type (TEXT) along with any attributes for that column.

The attributes are things like NOT NULL, AUTO_INCREMENT, UNIQUE KEY, COMMENT 'A comment', etc...

If you previously set any of those other attributes then you have to specify all of them again in addition to adding NOT NULL, otherwise you'll lose the other ones.

Suppose that when the table was created the column definition was TEXT COMMENT 'Production Credits'

Now you've decided that you want to add NOT NULL to this. If you run the command as shown in my answer then you will lose the comment attribute that was previously set.

The correct statement would be 

ALTER TABLE documentaries MODIFY production_credit TEXT NOT NULL COMMENT 'Production Credits';

If you're not sure, then the safest thing to do is run SHOW CREATE TABLE documentaries and this will show you all your column definitions. You can copy the column definition for production_credit and then paste it at the end of your ALTER statement. Then change NULL or DEFAULT NULL to NOT NULL.

That way you can be sure you are preserving all previously set attributes.

Does that make more sense?