On 10/18/15 4:32 AM, Karsten Hilbert wrote:
>> Can you tell us more about the database (ignoring nit-picking!)?
> Sure. All the clinical tables inherit from clin.clin_root_item
> which itself has a primary key (pk_item) and which, in turn,
> inherits from audit.audit_fields, which, again, has a primary
> key of its own (pk_audit). That is what makes several tables
> appear to have more than one primary key.
FWIW, I intentionally break some of my conventions when using
inheritance. For example, I normally use a single sequence to drive ALL
surrogate keys for the tables, and child tables keep the same surrogate
key as the parent (which means naming doesn't match). IMHO it makes more
sense to have inheritance do the work for you on this stuff.
BTW, if there's any interest, I have code that sets up a non-inheritance
table specifically for doing foreign keys to the inheritance parent. It
doesn't support updates right now, but should be fully safe from a FK
standpoint.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com