pgsql-hackers-object_relational_design_with_postgresql_methods_of_ensuring_uniqueness_with_UNIQUE

This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.



To: pgsql-hackers@postgresql.org
From: Oliver Elphick <olly@lfix.co.uk>
Subject: Re: [HACKERS] UNIQUE not unique with inheritance (workaround?)
Date: 08 Feb 2003 22:12:01 +0000

Samuel Sieb wrote:
> Is there any workaround for this problem?  I'm getting involved in a 
> project where inheritance is an incredibly useful feature, but the 
> non-unique issue could be a serious stumbling block.  Is there any way 
> to work around it with a trigger or something?

Give each table in the hierarchy a foreign key reference to another
table which holds a unique list of the primary keys and a column that
says which table they are in.  Use triggers to update this other table
and to prevent duplications in the hierarchy.

===

To: pgsql-hackers@postgresql.org
From: Bruce Momjian <pgman@candle.pha.pa.us>
Subject: Re: [HACKERS] UNIQUE not unique with inheritance (workaround?)
Date: Wed, 12 Feb 2003 13:33:35 -0500 (EST)

Oliver Elphick wrote:
> Samuel Sieb wrote:
> > Is there any workaround for this problem?  I'm getting involved in a 
> > project where inheritance is an incredibly useful feature, but the 
> > non-unique issue could be a serious stumbling block.  Is there any way 
> > to work around it with a trigger or something?
> 
> Give each table in the hierarchy a foreign key reference to another
> table which holds a unique list of the primary keys and a column that
> says which table they are in.  Use triggers to update this other table
> and to prevent duplications in the hierarchy.

Or create a sequence, and make each table in the inheritance hierarchy
use the same sequence as it's default value.

===

To: pgsql-general@postgresql.org
From: "Felipe Schnack" <felipes@ritterdosreis.br>
Subject: [GENERAL] inheritance
Date: Thu, 13 Feb 2003 11:04:02 -0100

  Hi all...
  I'm starting to play with pgsql inheritance features and I just wondered a 
specific situation where I wouldn't have any idea of how to use it. Let's say 
I create these tables:
  create table persons (id serial primary key);
  create table students (number int) inherits persons;
  create table emplyees (anothernumber int) inherits persons;
  Let say I can have a person that is a student and is a employee of the 
company... how I would insert its data in my database??

===

To: pgsql-general@postgresql.org
From: Oliver Elphick <olly@lfix.co.uk>
Subject: Re: [GENERAL] inheritance
Date: 16 Feb 2003 20:47:25 +0000

On Thu, 2003-02-13 at 12:04, Felipe Schnack wrote:
>   Hi all...
>   I'm starting to play with pgsql inheritance features and I just wondered a 
> specific situation where I wouldn't have any idea of how to use it. Let's say 
> I create these tables:
>   create table persons (id serial primary key);
>   create table students (number int) inherits persons;
>   create table emplyees (anothernumber int) inherits persons;
>   Let say I can have a person that is a student and is a employee of the 
> company... how I would insert its data in my database??

You can create another table:
    create table student_employees (PRIMARY KEY (id)) inherits
(students, employees);

Beware, that you have no overall uniqueness check on the id field; and
the primary key constraint is not inherited - it must be repeated for
each table.

On the other hand there may be better ways of solving this particular
problem, such as using foreign keys instead.

===

the rest of The Pile (a partial mailing list archive)

doom@kzsu.stanford.edu