MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Adding NOT NULL constraint

with 3 comments

Somebody wanted to know if you could add a NOT NULL column constraint in MySQL. That’s a great question and the answer is yes. The following example shows you how to do it.

  1. Create a sample table without a NOT NULL constraint on a column that should have one. After creating this table, describe it and you’ll see that the testing_text column is
CREATE TABLE testing
( testing_id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
, testing_text VARCHAR(10));
  1. Change the column definition from null allowed to not null for the TESTING_TEXT column. The only problem with this syntax is that it only works when there are no null values in the table or there are no rows in the table.
ALTER TABLE testing
CHANGE testing_text testing_text VARCHAR(10) NOT NULL;
  1. Change the column definition from not null constrained to null allowed for the TESTING_TEXT column.
ALTER TABLE testing
CHANGE testing_text testing_text VARCHAR(10);

As always, I hope this helps.

Written by maclochlainn

March 26th, 2011 at 9:58 pm

Posted in MySQL,PSM,sql

3 Responses to 'Adding NOT NULL constraint'

Subscribe to comments with RSS or TrackBack to 'Adding NOT NULL constraint'.

  1. Was helpful. Thanks.

    Manav

    11 Mar 13 at 1:12 pm

  2. Very nice…

    Alter table table_name change column_name column_name datatype(length) definition

    This is the query for change definition not null to null.
    I recommend also this website.

    guru

    3 Feb 14 at 12:42 am

Leave a Reply