00 Votes

SQLite: Check whether table exists

Info by SmartUser | 01.06.2014 at 16:43

Question: Before writing data into a table or reading data from a table, we would like to check whether the table generally exists to prevent reading of writing errors.

Solution: The names of all existing tables are listed in "sqlite_master" and can be retried from there. Here is an example of how to check the existence of the table "tab".

SELECT name FROM sqlite_master WHERE type='table' AND name='tab';

If the result of this query is empty, we know that the table does not exist.

Important: Temporary tables can not be retried from "sqlite_master" because they are listed in "sqlite_temp_master". If we are interested in temporary tables, we can just replace "sqlite_master" with "sqlite_temp_master" in the query above.

Alternative suggestion: For some applications, other solutions are easier to implement. For example, imagine that one of our programs needs some tables in our database. When starting the program, it is not sure whether the program has been started before and if the appropriate tables are already created. Therefore, we would like to check whether a specific table is already existing so that we can only create the new table if not.

CREATE TABLE IF NOT EXISTS 'tab' (...);

For this case, the statement "CREATE TABLE IF NOT EXISTS" can be the simpler way. This statement is equivalent to the "CREATE TABLE"-statement but it is supplemented by "IF NOT EXISTS". That means that the new table is only created if it is not existing yet. This saves you the detour of checking this before.

ReplyPositiveNegative

About the Author

AvatarThe author has not added a profile short description yet.
Show Profile

 

Related Topics

SQLite: Reset AUTOINCREMENT
Question | 1 Answer

Important Note

Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.

Participate

Ask your own question or write your own article on askingbox.com. That’s how it’s done.