On Tue, Sep 6, 2011 at 10:47 PM, Rami Ojares <[email protected]> wrote:
> **
>
> create table basket_impossible (
> customer varchar (48) not null
> references customer(name),
> ord integer as
> (select count(*) from basket_impossible as self
> where basket_impossible.customer = self.customer),
> -- other columns
> primary key (customer, ord)
> )
>
>
> Let's see how this table would work
>
> Insert into basket_impossible(customer) values(1);
> (1, 0);
> Insert into basket_impossible(customer) values(1);
> (1, 0), (1, 1)
> update basket_impossible set customer = 2 where customer = 1 and ord = 0;
> (2, 0), (1, 1)
> Insert into basket_impossible(customer) values(1);
> (2, 0), (1, 1), (1, 1)
> which produces primary key violation.
>
>
Which is a good thing. The proposed solution is not every table. When I
wrote the snippet above I was thinking about an append-only table.
Maybe a better model would have
create table thread (
user varchar(48) not null
num_comments integer
as (count(user)) -- pseudocode obviously
comment varchar not null,
edited boolean not null
default false
primary key (user, num_comments)
)
now the key here is natural both its components have a clear meaning. And
modifiing the comment may make sense, but not user or num_comments.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
