I'm working on exporting H2 databases and importing them. When building
the necessary create statements, I have code that adds the Primary Key
Index to the CREATE SQL statement. It will end up looking something like
this:
CREATE TABLE admit(
[rowkey] int identity NOT NULL,
[firstName] nvarchar(50) NULL,
[lastName] nvarchar(50) NULL,
[accountNumber] nvarchar(50) NULL,
[update_time] timestamp NULL,
CONSTRAINT [PK_admit] PRIMARY KEY (
[rowkey])
)
First I get the database metadata (java.sql.DatabaseMetaData) from the
connection(i.e. connection.getMetaData()). Then, I figure out the primary
key by calling metadata.getPrimaryKeys(catalog, schema, name).
Then I want to add the various indexes next, so that I end up with a
complete set of creation statements like:
CREATE TABLE admit(
[rowkey] int identity NOT NULL,
[firstName] nvarchar(50) NULL,
[lastName] nvarchar(50) NULL,
[accountNumber] nvarchar(50) NULL,
[update_time] timestamp NULL,
CONSTRAINT [PK_admit] PRIMARY KEY (
[rowkey])
)
GO
CREATE NONCLUSTERED INDEX [IX_admit_account_number] ON [admit] (
[accountNumber])
CREATE NONCLUSTERED INDEX [IX_admit_updatetime] ON [admit] (
[update_time])
This will correctly run in H2 creating the table with the correct indexes.
To find the indexes, I use metaData.getIndexInfo(catalog, schema, name,
false, false) and go through generating SQL statements for each index. Of
course, this includes the table's primary key, which I need to screen out
so that I don't end up trying to insert the primary key again. When this
code runs against MsSQL, I can check metaData.getPrimaryKeys(catalog,
schema, name).getString("PK_NAME") equals metaData.getIndexInfo(catalog,
schema, name, false, false).getString("INDEX_NAME") to determine which
index is the primary key.
However, the way H2 is written, these will almost certainly never be the
same name
(see
https://github.com/h2database/h2database/blob/master/h2/src/main/org/h2/jdbc/JdbcDatabaseMetaData.java).
The PK_NAME is either CONSTRAINT_NAME, INDEX_NAME depending on whether or
not CONSTRAINT_NAME is null. INDEX_NAME will always be INDEX_NAME.
Should they return the same thing? Is this a defect or was there a reason
it was done this way (i.e. JDBC specifications, etc.)?
If not, is there another (preferably database agnostic) way to determine
which index is the primary key?
As a work around, H2 always seems to start the CONSTRAINT_NAME with
"PRIMARY_KEY_", so I'm detecting that I'm running under H2 and then
checking for that prefix.
Is there a better way?
Thank you.
Sincerely,
Stephen McCants
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
