Composite Keys

Поиск
Список
Период
Сортировка
Искать

Composite Keys

От:
Bruce Elrick <belrick@home.com>
Дата:
I couldn't grok this out of the docs.  Does PostGreSQL support composite
keys?  I am aware of the current lack of foreign key support except through
refint.so and triggers.

In my case, I'd like to have a single address table enforcing a unique
(employeenum, addrtype) instead of a home_address, office_address, and
worksite_address table, each enforcing a unique (employeenum) (which is a
foreign key of the employee table).  In reading up on the unique constraint,
it is not obvious (to someone who is not a DBA) whether the uniqueness is
enforced on the combination of fields or on the fields separately.

That is, (empnum, addrtype)'s of the form (A, x), (A, y), (B, x), (C, x), (C,
z) are ok, where you see neither empnum nor addrtype are unique, only the
composition is.

I'm probably stating the obvious but I am unsure...

Bruce
--
Bruce Elrick, Ph.D.                       Saltus Technology Consulting Group
Personal: belrick@home.com                          IBM Certified Specialist
Business: belrick@saltus.ab.ca          ADSM, AIX Support, RS/6000 SP, HACMP

Re: Composite Keys

От:
Bruce Elrick <belrick@home.com>
Дата:
Bruce Elrick wrote:
> 
> I couldn't grok this out of the docs.  Does PostGreSQL support composite
> keys?  I am aware of the current lack of foreign key support except through
> refint.so and triggers.
> 
> In my case, I'd like to have a single address table enforcing a unique
> (employeenum, addrtype) instead of a home_address, office_address, and
> worksite_address table, each enforcing a unique (employeenum) (which is a
> foreign key of the employee table).  In reading up on the unique constraint,
> it is not obvious (to someone who is not a DBA) whether the uniqueness is
> enforced on the combination of fields or on the fields separately.
> 
> That is, (empnum, addrtype)'s of the form (A, x), (A, y), (B, x), (C, x), (C,
> z) are ok, where you see neither empnum nor addrtype are unique, only the
> composition is.
> 
> I'm probably stating the obvious but I am unsure...
> 

bad form to reply to one's own post...but I just saw
   CREATE TABLE films (
       code      CHAR(05),
       title     VARCHAR(40),
       did       DECIMAL(03),
       date_prod DATE,
       kind      CHAR(10),
       len       INTERVAL HOUR TO MINUTE,
       CONSTRAINT code_title PRIMARY KEY(code,title)
       );
(and two following) where I see that one can use a primary key _table_
constraint on multiple columns, thus giving composite keys....

Sorry for the wasted bandwidth...

On a related note, how close is foreign key support?  7.x?

Thanks....
Bruce
--
Bruce Elrick, Ph.D.                       Saltus Technology Consulting Group
Personal: belrick@home.com                          IBM Certified Specialist
Business: belrick@saltus.ab.ca          ADSM, AIX Support, RS/6000 SP, HACMP
FAQ