H2 database: Information about primary key in INFORMATION_SCHEMA

I create the following table in H2:

CREATE TABLE TEST
(ID BIGINT NOT NULL PRIMARY KEY)

Then I look into INFORMATION_SCHEMA.TABLES table:

SELECT SQL 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'TEST'

Re开发者_如何学Pythonsult:

CREATE CACHED TABLE TEST(
    ID BIGINT NOT NULL
)

Then I look into INFORMATION_SCHEMA.CONSTRAINTS table:

SELECT SQL 
FROM INFORMATION_SCHEMA.CONSTRAINTS
WHERE TABLE_NAME = 'TEST'

Result:

ALTER TABLE TEST 
ADD CONSTRAINT CONSTRAINT_4C 
PRIMARY KEY(ID) 
INDEX PRIMARY_KEY_4C

These statements are not the ones which I have stated, therefore, the question is: Is the information in TABLES and CONSTRAINS reflects how real SQL which was executed in database?

1. In original CREATE TABLE statement there was no CACHED word. (not a problem)
2. I have never executed ALTER TABLE .. ADD CONSTRAINT statement.

The actual reason why I am asking the question is that I am not sure which statement should I execute in order to guarantee that primary key is used in a clustered index. If you look at my previous question H2 database: clustered index support then you may find in the answer of Thomas Mueller the following statement:

If a primary key is created after the table has been created then the primary key is stored in a new index b-tree.

Therefore, if the statements are executed as such they are shown in INFORMATION_SCHEMA, then primary key is created after the table is created and hence ID is not used in a clustered index (basically as a key in a data b-tree).

Is there a way how one can guarantee that primary key is used in a clustered index in H2?

---

Is the information in TABLES and CONSTRAINS reflects how real SQL which was executed in database?

Yes. Basically, those are the statements that are run when opening the database.

If you look at my previous question

The answer "If a primary key is created after the table has been created..." was incorrect, I fixed it now to "If a primary key is created after data has been inserted...".

Is there a way how one can guarantee that primary key is used as a clustered index in H2?

This is now better described in the H2 documentation at "How Data is Stored Internally": "If a single column primary key of type BIGINT, INT, SMALLINT, TINYINT is specified when creating the table (or just after creating the table, but before inserting any rows), then this column is used as the key of the data b-tree."