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?

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,

I think easy solution could be:

create baseTable with just one column as PK

TableA inhertis baseTable, it will have inherited Column + additianl tables
for TableA
TableB inherits baseTable, it will aslo have inherited Column + additianl
tables for TableB

Insert in any table TableA or TableB will automatically insert row in
baseTable what will fail if value already exist...

Kind Regards,

Misa

2011/6/20 Tarlika Elisabeth Schmitz <postgresql4@numerixtechnology.de>

Misa,

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.

David J.

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Misa Simic
Sent: Wednesday, June 22, 2011 8:49 AM
To: Tarlika Elisabeth Schmitz
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] unique across two tables

Hi Tarlika,

I think easy solution could be:

create baseTable with just one column as PK

TableA inhertis baseTable, it will have inherited Column + additianl tables for TableA

TableB inherits baseTable, it will aslo have inherited Column + additianl tables for TableB

Insert in any table TableA or TableB will automatically insert row in baseTable what will fail if value already exist...

true :(

My applogize to all - my BIG fault - i haven't tested that before sent
suggestion

Kind Regards,

Misa

2011/6/22 David Johnston <polobo@yahoo.com>

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.