Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313

    Unanswered: updating COMMENT metadata for tables and columns

    I am looking for a way to edit the COMMENT metadata for a table or column after the table or column is already in existence. Anyone happen to know what the DDL for doing that would be? something like this perhaps (obviously these don't work, this is just to demonstrate what I would need):

    ALTER TABLE ADD COMMENT 'this table is for blah'
    ALTER TABLE MODIFY COLUMN ADD COMMENT 'this column is for blah'

    this page says it's possible but then doesn't give an example, and it's not clear to me from the syntax of ALTER TABLE that it's even possible: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

    Quote Originally Posted by dev.mysql.com
    ALTER TABLE enables you to change the structure of an existing
    table. For example, you can add or delete columns, create or destroy
    indexes, change the type of existing columns, or rename columns or
    the table itself. You can also change the comment for the table and
    type of the table.

  2. #2
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    ALTER TABLE <table> COMMENT = 'blah blah blah';
    ALTER TABLE <table> MODIFY COLUMN <column> <col params> COMMENT = 'blah blah blah';
    e.g.

    ALTER TABLE `sales` MODIFY COLUMN ID INT NOT NULL COMMENT = 'blah blah blah';

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Thanks!

    that's lame that you have to provide the entire column definition just to change the comment though - seems error prone.

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    I agree, however it's the only way I believe that it's possible.
    How does MySQL know if the alter table statement you are doing is supposed to change the column definition or not?
    ALTER TABLE <table> MODIFY COLUMN ID COMMENT='blah blah' is non-conformative, how would it differentiate between this
    ALTER TABLE <table> MODIFY COLUMN ID NOT NULL
    without a special case switch?
    Do you actually want to change it to NOT NULL? Or was it supposed to be VARCHAR NOT NULL ? It doesn't know. Hence the requirement for full syntax. It's anal but enforces restriction...

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by aschk
    ALTER TABLE <table> MODIFY COLUMN ID COMMENT='blah blah' is non-conformative
    what do you mean? are you referring to ANSI compliance?

    Quote Originally Posted by aschk
    how would it differentiate between this
    ALTER TABLE <table> MODIFY COLUMN ID NOT NULL without a special case switch?
    Do you actually want to change it to NOT NULL? Or was it supposed to be VARCHAR NOT NULL ? It doesn't know. Hence the requirement for full syntax. It's anal but enforces restriction...
    seems to me in the context of a full blown SQL parser it would be a simple matter to distinguish COMMENT from other keywords. but the point is moot since it obviously doesn't work the way I would have designed it.

    thanks again for the syntax.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •