CODEWITHSUNDEEP
databaseh2
Lexus
Lexus

Reputation: 43

H2 Database Primary Key on Create

If I create a new tabel in my h2 database like this...

CREATE TABLE MAININCOMER(timestamp TIMESTAMP, value REAL);

It means that the ID will be hidden and auto increment when i write to the tabel, right? - (as far as I have learnt from the tutorial and features)

Will a Primary Key be assigned to the id automatically? and how important is the primary key anyway in the type of table with only 2 columns? Or should I as best practice create the table with Primary Key assigned to the timestamp?

CREATE TABLE MAININCOMER(timestamp TIMESTAMP PRIMARY KEY, value REAL);

Thanks, Alex

Upvotes: 3

Views: 8816

Answers (2)

Christian MICHON
Christian MICHON

Reputation: 2170

No: your table will not have an ID column, as it's not listed in your DDL CREATE TABLE.

The simplest way I know for adding such auto incrementing column to be used as a primary key is ID IDENTITY. Be careful as it's specific to H2 and not portable.

But it's so much shorter than with any other database :-D

Please change your DDL to this full line:

CREATE TABLE MAININCOMER(id IDENTITY, timestamp TIMESTAMP, value REAL);

You can test it inside H2 web console like this:

@loop 1000 insert into mainincomer (timestamp) values (now());

This will insert 1000 records and you'll see that ID is defined as a BIGINT(19) NOT NULL, there will be a PRIMARY KEY constraint assigned to it and it's autoincrementing nicely.

It's considered a bad practice to use timestamps as a primary key. It's hard to ensure unique constraints, as you could have event arriving at the same time (sort of, it depends on your time resolution). Plus integer are much easier to index and to find back.

Upvotes: 1

dharan
dharan

Reputation: 283

Try this out:

create table mainincomer(id bigint auto_increment,t timestamp,value real,primary key(id))

Upvotes: 2

Related Questions