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

Databases
1

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

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

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

4

So if I do something like

$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

that’ll work for many scenarios, but not all

do the SHOW CREATE TABLE i asked for, and i’ll show you