Composite types for composite primary/foreign keys?

From: Wolfgang Keller <wolfgang(dot)keller(dot)privat(at)gmx(dot)de>
To: pgsql-novice <pgsql-novice(at)postgresql(dot)org>
Subject: Composite types for composite primary/foreign keys?
Date: 2007-11-13 11:32:04
Message-ID: CEBEB9DB0345B885E8AFAD1B@[192.168.1.25]
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hello,

as far as I understand from the (excellent, btw.) PostgreSQL
documentation, inheritance of primary or foreign keys is not (yet?)
supported. That's a pity, because it makes inheritance essentially
useless in my case: A database schema with 300-400 tables and a
strongly hierarchically structured identification schema using
composite primary keys. And also bulkloads of corresponding foreign
keys criss-crossing all over the schema.

But I am pathetically lazy >;->, so I do want to save on keystrokes and
thus I had the idea to use composite types for the composite primary
(and foreign) keys. *Beep* again, it seems to me, as according to the
documentation: "since no constraints are associated with a composite
type, the constraints shown in the table definition do not apply to
values of the composite type outside the table".

I'm sorry, but I'm apparently too dump to actually figure out myself
whether this means that I can use a single composite type column as a
primary / foreign key or whether not...?

Obviously I can't define the constraint within the type definition ("no
constraints (such as NOT NULL) can presently be included"), but if I
define the (NOT NULL and PRIMARY/FOREIGN KEY) constraint on the single
(composite) key column in the table definition, will it work as I would
expect it from a composite primary/foreign key? If so, this would be
really great.

And yet another clueless newbie question: From the pgAdmin GUI it seems
to me as if foreign keys would work across schema "boundaries"...? If
so, this would allow using schemas as namespaces (almost) like modules
in Python.

TIA,

Sincerely,

Wolfgang Keller

Browse pgsql-novice by date

  From Date Subject
Next Message Wolfgang Keller 2007-11-13 11:45:39 Precision of time types
Previous Message Devrim GÜNDÜZ 2007-11-13 03:26:44 Re: postgresql 8.1.10-1PGDG (32 bit) installaion on RHEL 4