List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:August 16 1999 6:00pm
Subject:Re: Is there a way to make a column not null AFTER creating it WITHOUT mysqldump and importing it again
View as plain text  
On Mon, 1999-08-16 09:08:51 -0700, Jann Linder wrote:
> Is there a way to make a colum not null after the table is
> created...maybe with the alter table command?

Bingo! :)

  ALTER TABLE yourtable MODIFY yourcolumn younewdefinition;

So let's see a mini example:
  CREATE TABLE tmp ( word CHAR(20) );
  EXPLAIN tmp;
    +-------+----------+------+-----+---------+-------+
    | Field | Type     | Null | Key | Default | Extra |
    +-------+----------+------+-----+---------+-------+
    | word  | char(20) | YES  |     | NULL    |       |
    +-------+----------+------+-----+---------+-------+
  INSERT INTO tmp VALUES ('friend'),(NULL),('enemy');
  SELECT * FROM tmp;
    +--------+
    | word   |
    +--------+
    | friend |
    | NULL   |
    | enemy  |
    +--------+
  ALTER TABLE tmp MODIFY word CHAR(20) NOT NULL;
  EXPLAIN tmp;
    +-------+----------+------+-----+---------+-------+
    | Field | Type     | Null | Key | Default | Extra |
    +-------+----------+------+-----+---------+-------+
    | word  | char(20) |      |     |         |       |
    +-------+----------+------+-----+---------+-------+
  SELECT * FROM tmp;
    +--------+
    | word   |
    +--------+
    | friend |
    |        |  <-- that's now an empty string ''
    | enemy  |
    +--------+

> The DB is LIVE and i do not wish to take the chance of corruption by
> exporting all the data, recreating the tables in re-importing the
> data.

With ALTER everything will stay consistent, but it works internally by
copying the table so it may need some time in which access to this table
will be blocked (though continued without loss afterwards).

> Just as a side note, the column which ALLOWS NULL at this time,
> currently does NOT have any null entries.

Just in case, it had NULL values, these would be changed into the
default value.

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
Is there a way to make a column not null AFTER creating it WITHOUT mysqldump and importing it againJann Linder16 Aug
  • Re: Is there a way to make a column not null AFTER creating it WITHOUT mysqldump and importing it againMartin Ramsch16 Aug
  • Re: Is there a way to make a column not null AFTER creating it WITHOUT mysqldump and importing it againChristian Mack16 Aug
    • RE: [CGI] Re: Is there a way to make a column not null AFTER creating it WITHOUT mysqldump and importing it againJann Linder16 Aug
      • RE: [CGI] Re: Is there a way to make a column not null AFTER creating it WITHOUT mysqldump and importing it againUlf18 Aug
  • Re: [CGI] Is there a way to make a column not null AFTER creating it WITHOUT mysqldump and importing it againUlf18 Aug