Learn SQL Database Programming

Adding or changing a column constraint

When you created this table with the earlier scripts, only playerID was set to NOT NULL. Let's say you wanted the schoolID and yearID values to be NOT NULL. You can create a NOT NULL constraint by executing the following query:

USE foraltering;ALTER TABLE tableforalteringCHANGE COLUMN schoolID schoolID VARCHAR(8) NOT NULL,CHANGE COLUMN yearID yearID SMALLINT NOT NULL;

The previous query fails with an Error Code: 1138. Invalid use of NULL value error. This error occurs because you have some NULL values in the table for schoolID and yearID. You have to fix them before proceeding to update the columns to NOT NULL, which you can do with the following query:

USE foraltering;UPDATE tableforalteringSET schoolID ...