How to get primary key name on laravel query builder

Question

Is there a way to get a table's primary key name using the Laravel query builder? Can i do something like

$key = DB::table($someTable)->getPrimaryKey();

I've looked through the documentation and API but couldn't find any reference to it.

Thanks in advance.

TimmyG · Answer 1 · 2019-09-23T19:43:59.303

You can get an array of indexes for a table directly using the Schema manager:

$indexes = DB::connection()->getDoctrineSchemaManager()->listTableIndexes($table);

OR

$indexes = Schema::getConnection()->getDoctrineSchemaManager()->listTableIndexes($table);

And then you can get an array of the columns associated with that index key:

$columns = $indexes[ 'primary' ]->getColumns();

This function can be used to find the primary key(s) or unique key(s) for a table you know nothing about:

public function getTablePrimaryOrUniqueKey($table, $key='') {
    //get the array of table indexes
    $indexes = DB::connection()->getDoctrineSchemaManager()->listTableIndexes($table);

    //abort if there are no indexes
    if(!is_array($indexes)) return false;

    //set up the default array of index keys
    $keys = ['primary', 'unique'];

    //if a key was passed and it is valid, use it...but cast as an array
    if($key!='' && in_array($key, $keys)) $keys = (array) $key;

    //loop through the keys array
    foreach ( $keys as $key ) {
        //keep it honest
        if ( array_key_exists( $key, $indexes ) ) {
            //get the columns for this key
            $columns = $indexes[ $key ]->getColumns();

            //if we have an array of columns, we have what we need
            if ( is_array( $columns ) ) {
                return $columns;
            }
        }
    }

    //if you got here, you did not get find what you were looking for
    return false;
}

score 2 · Answer 2 · answered Apr 16 '18 at 16:07

2

The Illuminate/Database/Eloquent/Model class has a getKeyName function which is public.

Inside your model class, you can access the primary key using $this->getKeyName().

answered Apr 16 '18 at 16:07

Phiter

14,570
14
50
84

I'm not using an Eloquent model. I want to do it through the query builder. – Gonçalo Marrafa Apr 16 '18 at 16:13
In this case I think you'll need to use some SQL. Laravel seems not to have this out of the box. – Phiter Apr 16 '18 at 16:15
The question was based on the query builder, note any database model! – Thomas Krcal Feb 13 '20 at 07:05

score 2 · Answer 3 · answered Dec 20 '18 at 17:34

2

my approach is :

$result = DB::select(DB::raw("SHOW KEYS FROM {$table} WHERE Key_name = 'PRIMARY'"));
$primaryKey = $result[0]->Column_name;

and i use mysql

answered Dec 20 '18 at 17:34

AXE

815
9
14

score 1 · Answer 4 · answered Apr 16 '18 at 16:08

1

You can use ->getKey() on a model:

$someModel->getKey();

Or if you're just trying to get the name of the column, without having an instance of the model:

app(App\Model::class)->getKeyName()

answered Apr 16 '18 at 16:08

DevK

9,597
2
26
48

getKey actually returns the value of the primary key – Phiter Apr 16 '18 at 16:10
Are you trying to get the name of the column? Use `->getKeyName()` – DevK Apr 16 '18 at 16:10
I'm not using an Eloquent model. I want to do it through the query builder. – Gonçalo Marrafa Apr 16 '18 at 16:13
That's not possible. You could make a [SQL query](https://stackoverflow.com/questions/3930338/sql-server-get-table-primary-key-using-sql-query) to get it maybe. – DevK Apr 16 '18 at 16:15
Thanks for this. I wanted the key itself, and this question is one of the top results on Google. – Joel Mellon Apr 22 '22 at 17:50

score 0 · Answer 5 · answered Oct 08 '18 at 17:35

$primary_key = app($builder->getModel())->getKeyName();

if you referenced the model when you initialized that Query Builder instance you can retrieve the model reference then use it to get the keyName. but if your just directly called a new Query Builder there isn't a way to see the primary key without doing a select against the table keys, maybe in a subselect?

SHOW KEYS FROM table WHERE Key_name = 'PRIMARY'

How to get primary key name on laravel query builder

5 Answers5