Thread: unique across two tables

I have two tables, town and townalias, the latter containing alternative
town names.
I would like to ensure that a town name is unique per
country-region across the two tables.

Can I do this with a constraint ot do I need to implement the logic via
trigger?



=========
PostgreSQl 8.4

CREATE TABLE town
(
  country_fk character varying(3) NOT NULL,
  region_fk character varying(3) NOT NULL,
  id serial NOT NULL,
  "name" character varying(50) NOT NULL
)

CREATE TABLE townalias
(
  country_fk character varying(3) NOT NULL,
  region_fk character varying(3) NOT NULL,
  town_fk integer NOT NULL,
  id serial NOT NULL,
  "name" character varying(50) NOT NULL,
  CONSTRAINT town_townalias_fk FOREIGN KEY (country_fk, region_fk,
town_fk) REFERENCES town (country_fk, region_fk, id)
)

--

Best Regards,
Tarlika Elisabeth Schmitz

> I have two tables, town and townalias, the latter containing alternative
> town names.
> I would like to ensure that a town name is unique per
> country-region across the two tables.
>
> Can I do this with a constraint ot do I need to implement the logic via
> trigger?

You can't have a constraint spreading multiple tables. And fixing this
using a trigger is harder than it looks, because a trigger does not see
uncommited changes made by other sessions.

So for example this will be hard to catch:

Session A: INSERT INTO town(name) VALUES ('Chicago');
Session B: INSERT INTO town(name) VALUES ('Chicago');
Session A: COMMIT;
Session B: COMMIT;

What I'd do is I'd keep the primary name in the 'townalias' table too,
maybe with a 'primary=true' flag. That way you can use traditional UNIQUE
constraint.

regards
Tomas

On 20 Jun 2011, at 17:03, Tarlika Elisabeth Schmitz wrote:

> I have two tables, town and townalias, the latter containing alternative
> town names.
> I would like to ensure that a town name is unique per
> country-region across the two tables.
>
> Can I do this with a constraint ot do I need to implement the logic via
> trigger?


This is very similar to what was recently discussed in the topic "Constraint to ensure value does NOT exist in another
table?"in this list. 

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4dff868612099297279502!

On Mon, 20 Jun 2011 19:42:20 +0200
Alban Hertroys <dalroi@solfertje.student.utwente.nl> wrote:

>On 20 Jun 2011, at 17:03, Tarlika Elisabeth Schmitz wrote:
>
>> I have two tables, town and townalias, the latter containing
>> alternative town names.
>> I would like to ensure that a town name is unique per
>> country-region across the two tables.
>>
>> Can I do this with a constraint ot do I need to implement the logic
>> via trigger?
>
>
>This is very similar to what was recently discussed in the topic
>"Constraint to ensure value does NOT exist in another table?" in this
>list.

Thanks - found it:
http://postgresql.1045698.n5.nabble.com/Constraint-to-ensure-value-does-NOT-exist-in-another-table-td4493651.html


--

Best Regards,
Tarlika Elisabeth Schmitz

Hi Tarlika,

I hope this approach is of interest.

This is how I would design a database to solve the problem - unfortunately, this
may not be appropriate for your particular situation.

This design ensures that: names of towns are unique within a given country and
region. plus it can support all the information that the original design could
provide.  I have run this sql using psql in pg 9.1beta2, without any errors being reported.

Note you will still need business logic, in a trigger or some such, to ensure
that only one town within a given country and region is marked as the name of
the town rather than as an alias.


CREATE TABLE country
(
    id      character varying(3) PRIMARY KEY,
    name    character varying(50) NOT NULL
);

CREATE TABLE region
(
    id      character varying(3) PRIMARY KEY,
    name    character varying(50) NOT NULL
);


CREATE TABLE country_region
(
    id          serial PRIMARY KEY,
    country_fk  character varying(3) REFERENCES country (id),
    region_fk   character varying(3) REFERENCES region (id)
);

CREATE TABLE town
(
    id                  serial PRIMARY KEY,
    country_region_fk   integer REFERENCES country_region (id),
    is_alias            boolean DEFAULT true NOT NULL,
    "name"              character varying(50) NOT NULL,
    
    UNIQUE (country_region_fk, "name")
);


Cheers,
Gavin Flower

Hi Tarlika,

true :(

On Wed, Jun 22, 2011 at 8:34 AM, David Johnston <polobo@yahoo.com> wrote:
> Your statement is utterly false simply by virtue of the documentation.
>
>
>
> Inserts never propagate to other tables in a hierarchy
>
> Indexes/Constraints only apply to individual tables
>
>
>
> Since inserts do not propagate the base table never sees the records and
> thus the index on the base table cannot enforce uniqueness.
>
>
>
> Other caveats and restrictions to the current inheritance implementation can
> be found in section 5.8 (Inheritance) of the documentation.  It is in the
> “Data Definition” chapter.

yeah -- postgresql table inheritance is completely useless for doing
what most people instinctively want it to do...it's a half implemented
kludge that dates back forever.  however, it does have a few quirky
neat uses and also underpins the table inheritance feature so we're
stuck with the current behavior.

merlin

> yeah -- postgresql table inheritance [...] underpins the table inheritance
feature [...]

Thank you Master of the Obvious  ;)

I do think you meant to say it underpins the "Table Partitioning Feature"
which, through the use of INSERT triggers to at least propagate the inserts
to the proper tables, it indeed does.

The only (obvious to me) way to really solve the problem - invisibly - is to
allow for table-less unique indexes that multiple tables can share and that
have a pointer to the "source" table for any particular entry in the index.
The other method being discussed effectively uses a physical table to
implement this behavior.

David J.

On Wed, Jun 22, 2011 at 11:30 AM, David Johnston <polobo@yahoo.com> wrote:
>> yeah -- postgresql table inheritance [...] underpins the table inheritance
> feature [...]
>
> Thank you Master of the Obvious  ;)
>
> I do think you meant to say it underpins the "Table Partitioning Feature"
> which, through the use of INSERT triggers to at least propagate the inserts
> to the proper tables, it indeed does.

you are correct sir :-).

merlin

Hello Gavin,

On Wed, 22 Jun 2011 20:53:19 +1200
Gavin Flower <GavinFlower@archidevsys.co.nz> wrote:

>  [...]
>This design ensures that: names of towns are unique within a given
>country and >region.
>Note you will still need business logic, in a trigger or some such, to
>ensure that only one town within a given country and region is marked
>as the name of >the town rather than as an alias.
>
>[...]
>CREATE TABLE town
>(
>     id                  serial PRIMARY KEY,
>     country_region_fk   integer REFERENCES country_region (id),
>     is_alias            boolean DEFAULT true NOT NULL,
>     "name"              character varying(50) NOT NULL,
>
>     UNIQUE (country_region_fk, "name")

Many thanks, also to David, Misa and Merlin for taking the time to post.

The concept of having separate tables for country/region/town sprang
from another discussion how to derive this information from freeform
text. Therefore alias tables might contain common
abbreviations/misspellings (which I can't detect with soundex, etc.). I
even have a table of non-standard country codes and I'd find it messy
to store these invalid variations in my "clean" country/region tables.


For the time being I plumped for a solution found in a thread Alban
Hertroys had pointed out:
http://postgresql.1045698.n5.nabble.com/Constraint-to-ensure-value-does-NOT-exist-in-another-table-td4493651.html

I created a function townname_exists (countryfk,regionfk,name), which I
use in conjunction with a check constraint. The constraint operates on
the alias table and the function searches the main table.

The downside is that I need to mirror the logic for both tables and
therefore need two separate functions (one checking town and one
townalias).



--

Best Regards,
Tarlika Elisabeth Schmitz

On 22/06/11 18.30, David Johnston wrote:
> The only (obvious to me) way to really solve the problem - invisibly - is to
> allow for table-less unique indexes that multiple tables can share and that
> have a pointer to the "source" table for any particular entry in the index.
> The other method being discussed effectively uses a physical table to
> implement this behavior.

I Have the same problem: one ID must be unique.
Three tables inherits from the same parent table the id column, the ID
is is defined as:
id bigint DEFAULT nextval('sequence_name')
in the parent table.

Can I assume that a sequence ensures uniqueness?

And... I did some search on Google but can't figure "table-less unique
indexes that multiple tables can share" where can I find further
information regarding this thing?

thank you
Edoardo

Dne 23.6.2011 20:39, Edoardo Panfili napsal(a):
> I Have the same problem: one ID must be unique.
> Three tables inherits from the same parent table the id column, the ID
> is is defined as:
> id bigint DEFAULT nextval('sequence_name')
> in the parent table.
>
> Can I assume that a sequence ensures uniqueness?

Well, not really. A sequence may be reset (so it will generate some
values again) and some users (developers/DBAs) might use a value that
did not come from the sequence (again, a duplicity).

If you can somehow enforce that the sequence is never reset and that
it's the only source of values, then it's probably safe. But the only
way how to enforce that is to e-mail all the developers and DBAs with a
threat that everyone who does not follow this rule will be executed ...

> And... I did some search on Google but can't figure "table-less unique
> indexes that multiple tables can share" where can I find further
> information regarding this thing?

Nowhere, I guess? At least in PostgreSQL, the index is always built on
top of a single table. So there's nothing like multi-column index or an
index without a table. And UNIQUE constraint requires an index.

But there's a possible solution I guess - you can create a separate
table with a single column (ID) with a UNIQUE constraint. And you can
create AFTER trigger that attempts to update the table. That should
provide exactly the same protection. It's elegant, it's reliable and I
doubt you can implement a faster solution on your own.

regards
Tomas

> On 22/06/11 18.30, David Johnston wrote:
> > The only (obvious to me) way to really solve the problem - invisibly -
> > is to allow for table-less unique indexes that multiple tables can
> > share and that have a pointer to the "source" table for any particular
entry
> in the index.
> > The other method being discussed effectively uses a physical table to
> > implement this behavior.
>
> I Have the same problem: one ID must be unique.
> Three tables inherits from the same parent table the id column, the ID is
is
> defined as:
> id bigint DEFAULT nextval('sequence_name') in the parent table.
>
> Can I assume that a sequence ensures uniqueness?
>
> And... I did some search on Google but can't figure "table-less unique
> indexes that multiple tables can share" where can I find further
information
> regarding this thing?
>
> thank you
> Edoardo

A sequence simply maintains a "next number to issue" variable.  Using
"nextval('seq_name') you can get that value AND have the value incremented
by 1.  However, you can directly change that value and it will happily
continue on as normal.  If you "reset" the sequence to a number less-than
the current "next number to issue" you end up having a "potential" duplicate
- what matters in determining if you "actually" have a duplicate is how you
then use that value.  In your case you would end up with different records
sharing the same ID unless they happen to fall onto the same table and a
UNIQUE index prevents the insert.

You could wrap the call to nextval('seq_name') in a SECURITY DEFINER
function and lock down the sequence to normal users but unless PostgreSQL
has some built-in mechanism to make a sequence "FORWARD-ONLY" what you
suggest is not without risk.

"table-less unique indexes..." are not a "thing" that I know of - it is more
of a description on my part.  PostgreSQL requires that an index be attached
to a single table and thus the table to which the index pointer "points" is
inferred from that relationship.  A "table-less" index would not have such
an implicit relationship and would need to store the table to which indexed
"record" belongs.  While the concept sounds good to me I have no idea how a
traditional index is technically coded and functions...

David J.

On 23/06/11 22.39, Tomas Vondra wrote:
> Dne 23.6.2011 20:39, Edoardo Panfili napsal(a):
>> I Have the same problem: one ID must be unique.
>> Three tables inherits from the same parent table the id column, the ID
>> is is defined as:
>> id bigint DEFAULT nextval('sequence_name')
>> in the parent table.
>>
>> Can I assume that a sequence ensures uniqueness?
>
> Well, not really. A sequence may be reset (so it will generate some
> values again) and some users (developers/DBAs) might use a value that
> did not come from the sequence (again, a duplicity).

> If you can somehow enforce that the sequence is never reset and that
> it's the only source of values, then it's probably safe. But the only
> way how to enforce that is to e-mail all the developers and DBAs with a
> threat that everyone who does not follow this rule will be executed ...
I also think it is so, also regarding "probably" safe :-)

> But there's a possible solution I guess - you can create a separate
> table with a single column (ID) with a UNIQUE constraint. And you can
> create AFTER trigger that attempts to update the table. That should
> provide exactly the same protection. It's elegant, it's reliable and I
> doubt you can implement a faster solution on your own.
I will try this solution in my database.

thanks to you and also to David
Edoardo

On 23/06/11 23:28, Tarlika Elisabeth Schmitz wrote:
> Hello Gavin,
>
> On Wed, 22 Jun 2011 20:53:19 +1200
> Gavin Flower<GavinFlower@archidevsys.co.nz>  wrote:
>
>>   [...]
>> This design ensures that: names of towns are unique within a given
>> country and>region.
>> Note you will still need business logic, in a trigger or some such, to
>> ensure that only one town within a given country and region is marked
>> as the name of>the town rather than as an alias.
>>
>> [...]
>> CREATE TABLE town
>> (
>>      id                  serial PRIMARY KEY,
>>      country_region_fk   integer REFERENCES country_region (id),
>>      is_alias            boolean DEFAULT true NOT NULL,
>>      "name"              character varying(50) NOT NULL,
>>
>>      UNIQUE (country_region_fk, "name")
> Many thanks, also to David, Misa and Merlin for taking the time to post.
>
> The concept of having separate tables for country/region/town sprang
> from another discussion how to derive this information from freeform
> text. Therefore alias tables might contain common
> abbreviations/misspellings (which I can't detect with soundex, etc.). I
> even have a table of non-standard country codes and I'd find it messy
> to store these invalid variations in my "clean" country/region tables.
>
>
> For the time being I plumped for a solution found in a thread Alban
> Hertroys had pointed out:
> http://postgresql.1045698.n5.nabble.com/Constraint-to-ensure-value-does-NOT-exist-in-another-table-td4493651.html
>
> I created a function townname_exists (countryfk,regionfk,name), which I
> use in conjunction with a check constraint. The constraint operates on
> the alias table and the function searches the main table.
>
> The downside is that I need to mirror the logic for both tables and
> therefore need two separate functions (one checking town and one
> townalias).
>
>
I think ir is safer, and simpler,  to have a flag in one table
indicating the status as reliable or not - rather than have duplicate
logic that is a potential maintenance nightmare.