Is it possible to structure a SQL query to automatically create a table if it doesn't already exist?

Question

I'm a complete SQL novice, and I'm trying to accomplish the following (pseudo-code) with a single query:

if (a table named "config_data" does not exist)
{ create a table named "config_data" }

if ("config_data" has no rows)
{ add a row to "config_data" with some default data }

return all rows in "config_data"

How would I go about it? Can it be done with a single query? I'm using SQLite3, if that helps.

So far, I know how to create a table, and insert data into it:

CREATE TABLE config_data (key TEXT NOT NULL, value TEXT);
INSERT INTO config_data VALUES ("key_1", "value_1");
INSERT INTO config_data VALUES ("key_2", "value_2");

And also how to retreive all rows:

SELECT * FROM config_data;

It's the fancy combinations that elude me :)

Dean Harding · Accepted Answer · 2010-07-19 06:11:42Z

5

To create a table if it does not already exist, you can use IF NOT EXISTS:

CREATE TABLE IF NOT EXISTS config_data (
    key TEXT NOT NULL, value TEXT,
    CONSTRAINT key_unique UNIQUE(key)
)

For your second part, you can use INSERT OR IGNORE (more info here) to insert a value, assuming you have a unique constraint that would otherwise be violated by inserting the value twice (for example, you might have a unqiue constraint on your key column):

INSERT OR IGNORE INTO config_data VALUES ("key_1", "def_value_1")

edited Jul 19, 2010 at 6:11

answered Jul 19, 2010 at 5:51

Dean Harding

72.9k13 gold badges151 silver badges180 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user106514 Over a year ago

Also, you can specify a default value per column in the CONSTRAINT clause. CREATE TABLE IF NOT EXISTS config_data ('someinteger' INTEGER, 'sometext' TEXT, CONSTRAINT sometext DEFAULT 'should be text but isn't yet')

Leniel Maccaferri · Accepted Answer · 2010-07-19 05:51:02Z

2

You can use CREATE TABLE IF NOT EXISTS...

Take a look here:

http://cookbooks.adobe.com/post_Easy_way_to_create_a_SQLite_connection-12846.html

You can also use what is shown at the end of this page:

http://php.net/manual/en/book.sqlite.php

answered Jul 19, 2010 at 5:51

Leniel Maccaferri

103k48 gold badges381 silver badges495 bronze badges

Comments

user106514 · Accepted Answer · 2010-07-19 14:14:52Z

1

Also, you can specify a default value per column in the CONSTRAINT clause.

CREATE TABLE IF NOT EXISTS config_data 
('someinteger' INTEGER, 'sometext' TEXT, CONSTRAINT sometext DEFAULT 'should be text but isn't yet')

answered Jul 19, 2010 at 14:14

user106514

1596 bronze badges

Collectives™ on Stack Overflow

Is it possible to structure a SQL query to automatically create a table if it doesn't already exist?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related