⬅ Forum: Building VoltDB Applications

Post: Question about primary keys & default for timestamp

Question about primary keys & default for timestamp

Wyvern

Mar 12, 2013

Hello,

There are 2 questions I'd like to ask that I've developed lately (didn't find answers elsewhere - maybe I didn't search well enough though):
1) Is there a way to set a default value for a TIMESTAMP field, eg. do something like this:

CREATE TABLE Customer(
reg_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
);

?
I saw this being done in the 'Auctions' example that ships with VoltDB (field BIDTIME of table BID), however it does not seem to work (When executing a ad-hoc query, I get the message 'Column REG_TIME has no default and is not nullable.').

2) What happens to a PRIMARY KEY field when the partitioning key does not coincide with it? Suppose I have:

CREATE TABLE Customer(
customer_ID NOT NULL,
e_mail NOT NULL,
CONSTRAINT pk_Customer PRIMARY KEY(customer_ID)
);
PARTITION TABLE Customer ON COLUMN e_mail;

Will it be the same as having a unique index on a column that is not the partitioning key of the partitioned table (ergo values of the column only unique within the partition) or are there any special circumstances to consider?

Thanks in advance,
Priidu

vtkstef

Mar 12, 2013

Priidu,

Q1. Instead of using default current_timestamp, we recommend you get the time stamp value by invoking getTransactionTime() from VoltProcedure

Q2. We cannot guarantee uniqueness on partitioned tables for fields that are not the partitioned column. For example

insert into customer (customer_id, email) values (1, 'jon@doe.net');
insert into customer (customer_id, email) values (1, 'jane@doe.net');

would not likely result in a constraint violation (depends on the chance that jon and jane are hashed to the same partition)

Stefano