Composite types for composite primary/foreign keys?

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

Hello,

for some reason, I couldn't get an answer on the novice-list, so if
this is the wrong place to ask or if it's just in the manual, excuse me
and just drop me a hint to the place where I have to look for it...

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 ld like to save keystrokes and
thus I had the (maybe pathetic)idea to use composite types for the
composite primary (and foreign) keys. No luck 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. Because it would make the whole schema much more readable
for a clueless moron like me. ;-) And it would also simplify mapping it
to a logical model in Python quite a bit.

TIA,

Sincerely,

Wolfgang Keller

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martijn van Oosterhout 2007-11-16 09:22:55 Re: getting the number of rows affected by a query
Previous Message Trevor Talbot 2007-11-16 09:07:21 Re: Variable LIMIT and OFFSET in SELECTs