Check whether a field has the property `UNIQUE` in mysql and PHP
Solution 1
From the documentation of SHOW INDEX (found by @diEcho):
SHOW INDEX returns the following fields:
Non_unique -- 0 if the index cannot contain duplicates, 1 if it can.
Column_name -- The column name.
Try:
SHOW INDEXES
FROM $tablename
WHERE Column_name='$field'
AND NOT Non_unique
Note that this assumes that there is no UNIQUE index that spans multiple columns. If there can be, then you might want to exclude these with a subquery.
Also note disabled indexes also show in this query (the possibility of disabled indexes is mentioned in the documentation on the Comment
column). There doesn't seem to be a column reflecting this, so you might need to parse the Comment
column if you have disabled indexes.
There's no need to compare Non_unique
to a number - MySQL uses 0 and 1 for booleans anyways
Solution 2
Ok I found it thanks to @diEcho
public function isUniqueField($tablename, $field, $connection){
$query = $connection->query("SHOW INDEXES FROM $tablename WHERE Column_name='$field' AND Non_unique=0");
$query->execute();
if(!$query->fetchAll()){
return false;
}
return true;
}
Solution 3
You can check all indexed column with
if there is a UNIQUE index then it cant be duplicate value in same table BUT a UNIQUE index allows multiple NULL values for columns that can contain NULL
update
to create a UNIQUE constraint on a column(let email) use below query
ALTER TABLE `table_name` ADD UNIQUE (`email`);
Related videos on Youtube
Comments
-
Jonathan de M. over 1 year
How can I check whether a field from a table is set as UNIQUE?
For example I have a table named
users
with a fieldemail
set asUNIQUE
and a fieldpicture
not set asUNIQUE
, I want before selecting check whether the field is set set asUNIQUE
if not then don't do theSELECT
.I tried to
SELECT
then count the returned number of row, if more than 1 then it's notUNIQUE
,"SELECT * FROM table WHERE email='$email'" //...some mysql php line later if($count > 1){ //return nothing }
but it's not efficient, what if there is no duplicate.
What's the best way to check whether a field is set as
UNIQUE
inPHP
?Edit: no duplicate doesn't mean it has
UNIQUE
property-
Ja͢ck over 11 yearsYour code should already know whether a field has a UNIQUE constraint on it; anything else is just reverse engineering your own application.
-
-
Jonathan de M. over 11 yearsI need to return true or false in php
-
Jonathan de M. over 11 yearsI want it return unique even if the field is not duplicated, just if the field has been set as UNIQUE
-
Anton over 11 yearsI didn't get you JonathandeM.
-
Jonathan de M. over 11 yearsWhat i want is not a field not duplicated I want a field with the property
UNIQUE
-
John Dvorak over 11 yearsJust because there's an index doesn't mean there's a unique index. You need to check that as well.
-
John Dvorak over 11 years
SHOW INDEX returns the following fields:
Non_unique -- 0 if the index cannot contain duplicates, 1 if it can.
Key_name -- The name of the index. If the index is the primary key, the name is always PRIMARY.
Column_name -- The column name.
-
John Dvorak about 9 years@marc I'm pretty sure you did not mean to invert the logic? Without a word of explanation at least?
-
Marc-André Lafortune about 9 yearsArgh, my bad, sorry. I got confused with the
NOT
. My appologies^2