Check whether a field has the property `UNIQUE` in mysql and PHP
Asked Answered
O

3

14

How can I check whether a field from a table is set as UNIQUE?

For example I have a table named users with a field email set as UNIQUE and a field picture not set as UNIQUE, I want before selecting check whether the field is set set as UNIQUE if not then don't do the SELECT.

I tried to SELECT then count the returned number of row, if more than 1 then it's not UNIQUE,

"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 in PHP?

Edit: no duplicate doesn't mean it has UNIQUE property

Orientate answered 27/12, 2012 at 4:36 Comment(3)
alternatively you can check via phpmyadminReredos
Your code should already know whether a field has a UNIQUE constraint on it; anything else is just reverse engineering your own application.Microprint
Basically I have a function to search by and the result should be unique (kinda like a slug), like username=bob [email protected] but if the username is not unique then the user could not do a search by username and I'd throw an error.Orientate
T
16

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

Talc answered 27/12, 2012 at 5:8 Comment(2)
@marc I'm pretty sure you did not mean to invert the logic? Without a word of explanation at least?Talc
Argh, my bad, sorry. I got confused with the NOT. My appologies^2Diaper
O
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;
    }
Orientate answered 27/12, 2012 at 4:59 Comment(2)
Just because there's an index doesn't mean there's a unique index. You need to check that as well.Talc
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.Talc
R
1

You can check all indexed column with

SHOW INDEX

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`);
Reredos answered 27/12, 2012 at 4:39 Comment(6)
I need to return true or false in phpOrientate
I want it return unique even if the field is not duplicated, just if the field has been set as UNIQUEOrientate
I didn't get you JonathandeM.Reredos
What i want is not a field not duplicated I want a field with the property UNIQUEOrientate
okay so you want to create a UNIQUE constraint on a column? am i rightReredos
A don't want to create just check whether the field has the property UNIQUE, check my answerOrientate

© 2022 - 2024 — McMap. All rights reserved.