Recreating UNI & MUL indexes in MySQL

Question

I'm doing a MySQL differ, to help update my tables from dev to production but I'm having a little trouble with the indexes.

I'm using show full columns from table to get the indexes/keys of the tables and comparing them, then building the necessary queries to update the first table.

I get the primary keys, that's working. But I've been reading and I still don't fully understand the other two possibilities, MUL, and UNI.

I read the man page..

If Key is PRI, the column is a PRIMARY KEY or is one of the columns in a multiple-column PRIMARY KEY.

If Key is UNI, the column is the first column of a unique-valued index that cannot contain NULL values.

If Key is MUL, multiple occurrences of a given value are permitted within the column. The column is the first column of a nonunique index or a unique-valued index that can contain NULL values.

Here's what I've currently got, if it's somehow relevant.

        if($key1 != $key2){
            if($key1['PRI'] != $key2['PRI']){
                // remove any existing primary keys 
                $results[$table_name][] = "ALTER TABLE $table_name DROP PRIMARY KEY;";
                // add the new primary keys
                $results[$table_name][] = "ALTER TABLE $table_name ADD CONSTRAINT pk_$table_name PRIMARY KEY (".implode(",", $key2['PRI']).")";
            }
            if($key1['MUL'] != $key2['MUL']){
                // what to do here?
            }
            if($key1['UNI'] != $key2['UNI']){
                // what to do here?
            }
        }

I'm guessing I can do something like this for the UNI: ALTER TABLE table ADD CONSTRAINT uc_myKey UNIQUE (indexColumn) ..is that correct?

How about the MULtiple keys? What's the syntax for that?

What is the MySQL syntax to create a the MUL and UNI type keys?

Thank you.

You are right about the UNI for the multiple keys it is like: alter table blah add constraint pk_something primary key (field1, field2) — Jorge Campos
@JorgeCampos - Thank you, but that's the exact syntax I'm using for PRI type keys. Are they interchangeable or does my primary key query need adjustment? — I wrestled a bear once.

I wrestled a bear once. I wrestled a bear once. · Accepted Answer · 2015-10-16T13:52:50

Duh...

                    switch($type){
                        case "PRI":
                            $ret[] = $add ?
                                "ALTER TABLE `$tbl` ADD PRIMARY KEY(`$col`);" :
                                "ALTER TABLE `$tbl` DROP PRIMARY KEY;" ;
                            break;
                        case "MUL":
                            $ret[] = $add ?
                                "ALTER TABLE `$tbl` ADD UNIQUE (`$col`);" :
                                "ALTER TABLE `$tbl` DROP INDEX `$col`;" ;
                            break;
                        case "UNI":
                            $ret[] = $add ?
                                "ALTER TABLE `$tbl` ADD INDEX (`$col`);" :
                                "ALTER TABLE `$tbl` DROP INDEX `$col`;" ;
                            break;
                    }

Recreating UNI & MUL indexes in MySQL

1 Answers