Thread: 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
On Dec 20, 2007 4:40 AM, Wolfgang Keller <wolfgang.keller.privat@gmx.de> wrote: > I'm not sure whether I am violating some copyright, so I didn't want to > post the SQL script here. But the script is publicly downloadable at > www.mimosa.org, and I only need a part of it to explain the basic > concept. So this is the "complex" schema. > > CREATE TABLE enterprise_type( > ent_db_site cris_string16_type NOT NULL, > ent_db_id cris_uint_type NOT NULL, > ent_type_code cris_uint_type NOT NULL, > name cris_string254_type NOT NULL, > user_tag_ident cris_string254_type, > gmt_last_updated cris_datetime_type, > last_upd_db_site cris_string16_type, > last_upd_db_id cris_uint_type, > rstat_type_code cris_ushort_type, > PRIMARY KEY (ent_db_site, ent_db_id, ent_type_code) > ) [snip] I have general suggestions here. First of all, I do not advise using domains for every table type automatically. This is actually not terrible, but domains have some downsides, for example they are not usable directly in arrays...this can byte you down the line. The best case for domains is when you have a constraint that needs to be applied across many tables (like validating a well formed email address)...basically a light weight trigger. Just be aware that modifying domains in such a way that requires dropping them first can be a nightmare, plan accordingly. Also, the domain names seem unnecessarily verbose, and over specialized. 'cris_string254_type' can probably be defined as 'text' with no ill effects. Secondly, you did not provide foreign keys...this makes it hard to figure out the relationships which ISTM is the heart of the question. Some of the primary keys look suspicious, but it's hard to tell without knowing more (I didn't follow the link). I think designs using composite, natural keys are generally good and I encourage you to go with it...just be aware this is probably the #1 most controversial topic in database design. Nevertheless, the main advantage of natural key designs is it encourages good key selection. Hard to say if you are leveraging that here.... merlin