CODEWITHSUNDEEP
sqldatabaseh2create-table
Joonas Pulakka
Joonas Pulakka

Reputation: 36577

"Create table if not exists" - how to check the schema, too?

Is there a (more or less) standard way to check not only whether a table named mytable exists, but also whether its schema is similar to what it should be? I'm experimenting with H2 database, and

CREATE TABLE IF NOT EXISTS mytable (....)

statements apparently only check for the table´s name. I would expect to get an exception if there's a table with the given name, but different schema.

Upvotes: 14

Views: 21745

Answers (5)

Arsen Khachaturyan
Arsen Khachaturyan

Reputation: 8330

Simply:

IF NOT EXISTS (SELECT 0 
           FROM INFORMATION_SCHEMA.TABLES 
           WHERE TABLE_SCHEMA = 'name_of_schema' 
           AND TABLE_NAME = 'name_of_table')
 BEGIN
     CREATE TABLE [name_of_schema].[name_of_table]
     (
       ...
     )
 END

For more examples, check here.

Upvotes: 1

isapir
isapir

Reputation: 23562

SELECT  *
FROM    INFORMATION_SCHEMA.TABLES
WHERE   TABLE_NAME      = 'TableName'
    AND TABLE_SCHEMA    = 'public'

Upvotes: 6

Erwin Smout
Erwin Smout

Reputation: 18408

Twofold answer :

(a) The existence of a table is something that should be ensured by the installation procedure of an application, not by the application itself at run-time.

(b) If you really think you have a valid reason for deviating from (a), you could try and query the catalog, which is a database consisting of tables whose structure is, more or less, prescribed by the INFORMATION_SCHEMA of the SQL standard. Which tables exist, which columns they have, which data types those columns are, which keys are declared, etc. etc., it's all in there.

Upvotes: 3

Kru
Kru

Reputation: 4235

CREATE TABLE IF NOT EXISTS ... is not a standard SQL code.

The thing to do is to check if the table is already in the catalogue. For instance, in Java you may do something like: connection.getMetaData().getTables(connection.getCatalog(), null, null, null)

For more info see javadoc java.sql.Connection.

Upvotes: 4

Thilo
Thilo

Reputation: 262714

I am not aware of any database that has this feature natively.

Have not used it (rolled my own code to do this) but maybe Apache DdlUtils can help.

It is a tricky thing to do, especially if you want it to work with different database vendors. Also, there are probably different opinions about how similar the schema needs to be in order to pass. Column names, column order, column types, primary key definition: certainly. But how about constraints, the names of the constraints, table space definitions, and so on?

Upvotes: 1

Related Questions