On 8/6/19 3:26 AM, Ibrar Ahmed wrote: > - Why we are not allowing any other datatype other than ranges in the > primary key. Without that there is no purpose of a primary key.
A temporal primary key always has at least one ordinary column (of any type), so it is just a traditional primary key *plus* a PERIOD and/or range column to indicate when the record was true.
> - Thinking about some special token to differentiate between normal > primary key and temporal primary key
There is already some extra syntax. For the time part of a PK, you say `WITHOUT OVERLAPS`, like this:
CONSTRAINT pk_on_t PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
In this example `id` is an ordinary column, and `valid_at` is either a Postgres range or a SQL:2011 PERIOD. (The latter is not yet implemented in my patch but there are some placeholder comments.)
Similarly a foreign key has one or more traditional columns *plus* a range/PERIOD. It needs to have a range/PERIOD on both sides. It too has some special syntax, but instead of `WITHOUT OVERLAPS` it is `PERIOD`. (Don't blame me, I didn't write the standard.... :-) So here is an example:
CONSTRAINT fk_t2_to_t FOREIGN KEY (id, PERIOD valid_at) REFERENCES t (id, PERIOD valid_at)
You should be able to see my changes to gram.y to support this new syntax.
I hope this clears up how it works! I'm happy to answer more questions if you have any. Also if you want to read more:
- This paper by Kulkarni & Michels is a 10-page overview of SQL:2011: