Composite types for composite primary/foreign keys?
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
On Nov 16, 2007, at 4:07 , Wolfgang Keller wrote:
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".
Note: "do not apply to values of the composite type outside of the
table". Both primary keys and foreign keys are defined within tables,
so AIUI this statement does not preclude you from using composite
types as primary and foreign keys.
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...?
What have you actually tried? You can learn a lot by a few minutes of
exploration at a psql prompt.
Michael Glaesemann
grzm seespotcode net
Hello,
and thanks for your reply.
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...?What have you actually tried?
I wanted to simplify the schema and make it more "readable" for
clueless morons like me. >;->
You can learn a lot by a few minutes of
exploration at a psql prompt.
Yes, I will have to get used to using the Postgres prompt just like I
do with the Python prompt. ;-)
Sincerely,
Wolfgang Keller
On Nov 19, 2007, at 6:17 , Wolfgang Keller wrote:
I wanted to simplify the schema and make it more "readable" for
clueless morons like me. >;->
Simplifying the schema is fine (and good!) as long as it exhibits the
same behavior as the more complex one: often in the course of
simplifying you find a solution yourself. However, we cannot help you
if you don't provide adequate information.
Michael Glaesemann
grzm seespotcode net