CODEWITHSUNDEEP
sqliteschemaprimary-key
Kyle
Kyle

Reputation: 17687

SQLite query to find primary keys

In SQLite I can run the following query to get a list of columns in a table:

PRAGMA table_info(myTable)

This gives me the columns but no information about what the primary keys may be. Additionally, I can run the following two queries for finding indexes and foreign keys:

PRAGMA index_list(myTable)
PRAGMA foreign_key_list(myTable)

But I cannot seem to figure out how to view the primary keys. Does anyone know how I can go about doing this?

Note: I also know that I can do:

select * from sqlite_master where type = 'table' and name ='myTable';

And it will give the the create table statement which shows the primary keys. But I am looking for a way to do this without parsing the create statement.

Upvotes: 20

Views: 29833

Answers (3)

Delark
Delark

Reputation: 1323

For the ones trying to retrieve a pk name in android, and while using the ROOM library.

@Oogway101's answer was throwing an error: "no such column [your_table_name] ... etc.. etc...

my way of query submition was:

String pkSearch = "SELECT l.name FROM pragma_table_info(" + tableName + ") as l WHERE l.pk = 1;";
database.query(new SimpleSQLiteQuery(pkSearch)

I tried using the (") quotations and still error.

String pkSearch = "SELECT l.name FROM pragma_table_info(\"" + tableName + "\") as l WHERE l.pk = 1;";

So my solution was this:

    String pragmaInfo = "PRAGMA table_info(" + tableName + ");";

    Cursor c = database.query(new SimpleSQLiteQuery(pragmaInfo));
    String id = null;
    c.moveToFirst();
    do {
       if (c.getInt(5) == 1) {
          id = c.getString(1);
       }
    } while (c.moveToNext() && id == null);
    Log.println(Log.ASSERT, TAG, "AbstractDao: pk is: " + id);

The explanation is that:

A) PRAGMA table_info returns a cursor with various indices, the response is atleast of length 6... didnt check more...

B) index 1 has the column name.

C) index 5 has the "pk" value, either 0 if it is not a primary key, or 1 if its a pk.

You can define more than one pk so this will not bring an accurate result if your table has more than one (IMHO more than one is bad design and balloons the complexity of the database beyond human comprehension).

So how will this fit into the @Dao? (you may ask...)

When making the Dao "abstract" you have access to a default constructor which has the database in it:

from the docummentation:

An abstract @Dao class can optionally have a constructor that takes a Database as its only parameter.

this is the constructor that will grant you access to the query.

There is a catch though...

You may use the Dao during a database creation with the .addCallback() method:

instance = Room.databaseBuilder(context.getApplicationContext(),
                    AppDatabase2.class, "database")
                    .addCallback(
                       //You may use the Daos here.
                    )
                    .build();

If you run a query in the constructor of the Dao, the database will enter a feedback loop of infinite instantiation.

This means that the query MUST be used LAZILY (just at the moment the user needs something), and because the value will never change, it can be stored. and never re-queried.

Upvotes: 0

Oogway101
Oogway101

Reputation: 113

Hopefully this helps someone: After some research and pain the command that worked for me to find the primary key column name was:

SELECT l.name FROM pragma_table_info("Table_Name") as l WHERE l.pk = 1;

Upvotes: 11

Mattias Isegran Bergander
Mattias Isegran Bergander

Reputation: 11909

The table_info DOES give you a column named pk (last one) indicating if it is a primary key (if so the index of it in the key) or not (zero).

To clarify, from the documentation:

The "pk" column in the result set is zero for columns that are not part of the primary key, and is the index of the column in the primary key for columns that are part of the primary key.

Upvotes: 22

Related Questions