SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot wh33t's Avatar
    Join Date
    Aug 2011
    Posts
    155
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    The correct way to add/alter a column from comand line

    Hey Site Point,

    I've built a tool that compares the tables and the table structure between two databases. I have managed to get the tool to the point where it will detect the differences of one table against another and now I'm trying to put the icing on the cake by making the tool also generate the SQL code to correct the difference. The two kinds of differences I'm having trouble with is the adding of a missing column and the altering of an existing one. I've googled for a while now and I'm confused as to what exactly I need to do as every example I've found has different syntaxes to add/alter columns. So what I would like to know is in which order do I put the following column attributes.

    Field, Type, Null, Key, Default, Extra

    I know the syntax is something like "alter table table_name add column Field, Not Null, Key, Default =, extra stuff here etc" but for the life of me I can't seem to figure it out. If you wouldn't mind throwing me a bone here I'd really appreciate it.

    Thanks guys.

  2. #2
    SitePoint Zealot wh33t's Avatar
    Join Date
    Aug 2011
    Posts
    155
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I should also add, my tool is reporting that a column with the name trans_id is differing because it's key is set to "MUL". I don't quite understand what this means or how to set it. I normally use PHPMYADMIN to adjust things like this and I can't find any documentation on how to set a key to MUL or remove MUL from a key. Oddly enough both columns appear to function completely fine.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by wh33t View Post
    So what I would like to know is in which order do I put the following column attributes.

    Field, Type, Null, Key, Default, Extra
    use that order, it's fine

    the "MUL" you were asking about is, i believe, a composite key -- a key consisting of more than one column

    you can't attack those on a single column basis

    just for fun, do a SHOW CREATE TABLE on both of the tables for which your tool showed a discrepancy

    i would not rely on phpmyadmin (*ptui*) to learn/understand a table's layout or characteristics
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot wh33t's Avatar
    Join Date
    Aug 2011
    Posts
    155
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    use that order, it's fine

    the "MUL" you were asking about is, i believe, a composite key -- a key consisting of more than one column

    you can't attack those on a single column basis

    just for fun, do a SHOW CREATE TABLE on both of the tables for which your tool showed a discrepancy

    i would not rely on phpmyadmin (*ptui*) to learn/understand a table's layout or characteristics
    So if I do something like

    PHP Code:
    $sql "ALTER TABLE table_name ADD $Field $Type $Null $Key $Default $Extra"
    It should work? Or is there more to the syntax then that?

    As for the MUL thing, I still don't get how it go to be set that way as I never set it that way. Is that something that the database decides on it's own?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by wh33t View Post
    So if I do something like

    PHP Code:
    $sql "ALTER TABLE table_name ADD $Field $Type $Null $Key $Default $Extra"
    It should work? Or is there more to the syntax then that?
    that'll work for many scenarios, but not all


    Quote Originally Posted by wh33t View Post
    As for the MUL thing, I still don't get how it go to be set that way as I never set it that way. Is that something that the database decides on it's own?
    do the SHOW CREATE TABLE i asked for, and i'll show you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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