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

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`);

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

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;
    }