unique_key vs multiple_key

Hi there,

Was wondering if someone knew why, when a MySQL field is marked as
Unique in phpMyAdmin, it shows up as a multiple_key in my application?

If I need to post more code, or the table structure, please just let me
know. I need to identify if a field is Unique, but don't feel I can
assume that a multiple_key means it is so.

Thanks.

Keith D Commiskey
http://kdcinfo.com | http://giftsforyou.biz

$getDataQueryQ = mysql_query("SELECT * FROM ".$tablename) or
die("Select Query failed: ".mysql_error());
$getDataQueryT1 = "";
$getDataQueryT2 = "";
while ($row=mysql_fetch_array($getDataQueryQ))
{ $icnt=0;
while ($icnt < mysql_num_fields($getDataQueryQ))
{ if ((strpos(mysql_field_flags($getDataQueryQ, $icnt),
"auto_increment")===false)) // Not a primary field
{ $flagIt = mysql_fetch_field($getDataQueryQ, $icnt);
if (!$flagIt)
{ echo "No information available
\n"; }
else { echo "Field " . $icnt . " : ";
echo $flagIt->name . " / P:";
echo $flagIt->primary_key . " / U:";
echo $flagIt->unique_key . " / M:";
echo $flagIt->multiple_key . " / Max:";
echo $flagIt->max_length . "
";
}
$getDataQueryT1 .= mysql_field_name($getDataQueryQ,
$icnt).", ";
$getDataQueryT2 .= $row[$icnt]."', '";
}
else
{ echo "Field " . $icnt . " : ";
print("Primary:
".mysql_field_name($getDataQueryQ, $icnt)."
");
}
$icnt++;
} }

Re: unique_key vs multiple_key

Update:

I upgraded MySQL from 4.0.17 to 4.1.19

MySQL command line shows field as MUL. So my question then would be,
why does phpMyAdmin show it as UNIQUE? Perhaps its time for me to move
this question over to the phpMyAdmin forum on SourceForge.Net. That is,
unless someone here knows why...

Thanks.

Keith D Commiskey
http://kdcinfo.com | http://giftsforyou.biz

Re: unique_key vs multiple_key

Turns out the real culprit is my lack of understanding of what MUL is,
and how to get past that to see where the Unique value is. When I
assign a field to be Unique, MySQL displays it as MUL instead.

I posted a message in the MySQL Newbie forum asking if anyone can
explain what the MUL field attribute is.

Thanks.

Keith D Commiskey


CREATE TABLE `loaner_condition` (`ConditionID` int(11) NOT NULL
auto_increment, `Condition` varchar(50) default '', PRIMARY KEY
(`ConditionID`), UNIQUE KEY `Condition` (`Condition`)) ENGINE=MyISAM
DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

==

mysql> describe loaner_condition2;
+-------------+-------------+------+-----+---------+-------- --------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------- --------+
| ConditionID | int(11) | | PRI | NULL | auto_increment |
| Condition | varchar(50) | YES | MUL | | |
+-------------+-------------+------+-----+---------+-------- --------+
2 rows in set (0.01 sec)

Re: unique_key vs multiple_key

Turns out, it's a bug:
http://bugs.mysql.com/bug.php?id=11227

I have to either only allow those with MySQL 5.0.11 the ability to use
two aspects of my script that rely on identifying this field attribute,
or figure out how phpMyAdmin determines the field is UNIQUE.

Keith D Commiskey
http://kdcinfo.com | http://giftsforyou.biz

Re: unique_key vs multiple_key

Post removed (X-No-Archive: yes)

Re: unique_key vs multiple_key

Hi Ralphie,

Thanks for your input. However, it doesn't matter how many fields you
set as Unique, if the field is set to allow Null values, it will show
as MUL.

My problem is that if I have a field set as Unique, I need to know its
Unique. But I can't determine this if it is set to allow Null values.

Good news is that somehow, some way, phpMyAdmin "is" able to determine
this (they must have found a way around the bug). So, I need to figure
out how they did it...

Thanks again.

Keith D Commiskey
http://kdcinfo.com | http://giftsforyou.biz