Re: unique keys / foreign keys on two tables - Mailing list pgsql-sql

Hi Gary,

The most straightforward way to ensure that the two tables have unique
IDs would be to create one sequence called something like
"destination_seq" and have the id column in both tables default to
NEXTVAL('destination_seq').

As far as storing the destinations go, I'm guessing that you're looking
for a good way to tell what type of id it is without checking both
tables to see which one it exists in.  If you need to be able to extract
the destination type from the ID, you could go with something more
robust like:

CREATE OR REPLACE FUNCTION dest_nextval (dest_type int) RETURNS int4 AS
$$
BEGIN RETURN (nextval('destination_seq') << 1) | dest_type;
END;
$$LANGUAGE plpgsql;

That would use the same destination_seq value, but it it would shift the
number 1 bit to the left.  So you could use DEFAULT dest_nextval(0) for
Extensions and DEFAULT dest_nextval(1) for Groups.  Your IDs would still
be 100% unique, but you could test for the type by checking IF (id & 1)
= 1 or 0.

The | is a "bitwise" operator for "OR", and the & is the bitwise
operator for AND.  They're covered a bit in section 9.3 of the
documentation, but if you went this route you'd probably want to look up
a more thorough explanation of bitwise operations in general.

The same principle works with larger numbers if you needed more types. 
If you shifted two bits, you could | by 0, 1, 2, or 3.  Shifting 3 bits
would give you 8 possibilities and so on.  It's a pretty neat way to
"encode" other information into one field.

I hope that helps!

Best,

Wolfe
--  Wolfe Whalen wolfe@quios.net

On Thu, Nov 29, 2012, at 02:14 AM, Gary Stainburn wrote:
> I'm designing the schema to store a config from our switchboards.
> 
> As with all PBX's the key is the dialed number which is either an
> extension 
> number or a group (hunt/ring/pickup) number.
> 
> I have two tables, one for extensions and one for groups, basically
> 
> ext_id  int4 primary key
> ext_desc        text
> ....
> ....
> ....
> 
> and
> 
> 
> 
> grp_id  int4 primary key
> grp_desc        text
> .....
> .....
> .....
> 
> I now need to be able to ensure the id field is unique across both
> tables. 
> Presumably I can do this with a function and a constraint for each table. 
> Does anyone have examples of this?
> 
> 
> Next I have other tables that refer to *destinations* which will be an ID
> that 
> could be either an extension or a group. Examples are 'Direct Dial In' 
> numbers which could point to either.  How would I do that?
> 
> -- 
> Gary Stainburn
> Group I.T. Manager
> Ringways Garages
> http://www.ringways.co.uk 
> 
> 
> -- 
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql