The Schema\Builder class has a hasTable() and hasColumn() methods to check the existence of a table and a column, respectively.
Is there any method or way to check if an index key (such as a unique key) exists?
Add a comment
|
4 Answers
While Laravel doesn't provide any method to check the existence of a key, you could use any of the available queries in MySQL and then use DB::select().
For instance:
$keyExists = DB::select(
DB::raw(
'SHOW KEYS
FROM your_table_name
WHERE Key_name=\'your_key_name\''
)
);
Just replace your_table_name and your_key_name for the correct values.
4 Comments
ceejayoz
Laravel uses Doctrine in spots (especially Schema), so
doctrine/dbal's listTableIndexes() might be a better solution than a DB::raw call that's MySQL-specific. doctrine-dbal.readthedocs.org/en/latest/reference/…
shrimpwagon
@ceejayoz To get the Doctrine Manager in Laravel:
$conn = Schema::getConnection()->getDoctrineSchemaManager()
dipenparmar12
@ceejayoz link no longer exists [doctrine-dbal.readthedocs.io/en/latest/reference/…
trane77
I think that it's not working anymore, I'm getting an exception:
PDO::prepare(): Argument #1 ($query) must be of t ype string, Illuminate\Database\Query\Expression givenIf you are using Laravel then most likely you will have access to an ORM like Eloquent. Assuming you are using Eloquent, you might be able to do something like this:
try {
Schema::table(
'the_name_of_your_table',
function (Blueprint $table) {
$sm = Schema::getConnection()->getDoctrineSchemaManager();
$indexesFound = $sm->listTableIndexes('the_name_of_your_table');
$indexesToCheck = [
'index_name_1',
'index_name_2',
'index_name_3',
'index_name_4'
];
foreach ($indexesToCheck as $currentIndex) {
if (array_key_exists($currentIndex, $indexesFound)) {
// The current index exists in the table, do something here :)
}
}
}
);
} catch (Exception $e) {
}
Comments
To get all keys details of a specific table
$keys = DB::select(DB::raw('SHOW KEYS from users'));
foreach($keys as $item)
{
echo $item->Key_name;
}
If you don't know the unique key name but know the table and the column name then here is a example code.
$keys = DB::select(DB::raw('SHOW KEYS from users'));
foreach($keys as $item)
{
if($item->Column_name == 'email')
{
Schema::table('users', function (Blueprint $table) use($item) {
$table->dropUnique($item->Key_name);
});
}
}
Comments
All solution listed here are probably OK, but apparently most recent versions of Laravel don't support their syntax anymore, I'm always getting this exception:
PDO::prepare(): Argument #1 ($query) must be of type string, Illuminate\\Database\\Query\\Expression`
I'm on Laravel 10.x-dev (yeah, is not a stable version, but it's not a problem in this case).
A little research lead me to this answer: get all indexed column names from one specific table in laravel
Basically DB::raw() must be removed, you can run the SQL directly inside the select method.
In case you will need it, I wrote a little function to check if one or more columns have an index, and if yes, ensures that all columns are linked to the same index (multicolumn index):
public function tableHasIndexes(string $tableName, ...$columns): ?array
{
$tableKeys = DB::select("SHOW KEYS FROM $tableName");
if (empty($indexes = Arr::except($tableKeys, 'PRIMARY'))) {
return null; // nessun index tanne la primary key :/
}
$matches = [];
foreach ( $indexes as $index ) {
if (in_array($index->Column_name, $columns)) {
$matches[] = $index->Key_name;
}
}
if (( $n_match = count($matches) ) !== ( $n_cols = count($columns) )) {
Log::warning("Only $n_match over $n_cols have an index!");
return null;
}
if (( $n_values = count($values = array_unique($matches)) ) !== ( $single = 1 )) {
Log::warning('Selected columns are linked to different indexes: ' . implode(', ', $values));
return null;
}
return $values;
}
It's a bit concise, sorry for that :-)
