Re: Unique Keys

Tony Andrews wrote:

>
> Mikito has given an answer that works, but as you say does not use a
> unique constraint (how could it?) But I think you are looking at this
> the wrong way round: a unique constraint isn't primitive, it is a
> shorthand for a constraint that could be more generally expressed as:
>

This is an interesting paragraph. I would not have said, "unique is shorthand", but it does actually express the idea, a declarative constraint invokes a lot of procedural code inside of the server tha we never see, we just see the effects. But anyway, unique constraints are declarative, and Mikito's is not, Mikito's stands as a *special* *case* that requires a programmer to code up some SQL instead of simply specifying the unique constraint in pure scalars.

> (select 0 from the_table t1, the_table t2
> where t1.primary_key != t2.primary_key
> and t1.unique_key = t2.unique_key)
> ={}
>
> This more general syntax is a lot more powerful, as Mikito's example
> shows.

I asked myself what would have to happen to make it a declarative constraint, examining carefully all definitions.

The answer I came up with, and that I am using, allows me to declaratively specify[1] the start/end combination as being a unique primary key, and more importantly, to define foreign keys into it that do not require the "run out". Because I use a code generator, this will generate code that is superficially close to Mikito's, but different in many respects (most importantly that no programmer has to write it). Probably the most crucial respect is the ability to present a foreign key that can be used without introducing ad-hoc code in every query.

The second stage of the answer is to put it into the engine of the DB server, which can only happen with the open source ones, which I'll be getting to when I retire :)

Finally, the idea I am using was inspired by a topic this ng was treating heavily just a couple of weeks or so ago.

[1] Insert Ken's usual rant about specifying things in data here. I

    use the term "declarative" to mean the same thing, since that is     the common usage in the ng.

[2] There is a posting on Usenet in 1995 that I stumbled upon where

    somebody wistfully requested this very feature, but that seems to     have been the alpha and omega for him on that.

-- 
Kenneth Downs
<?php $sig_block="Variable scope? What's that?";?>