[Gnumed-devel] Re: Postgres meta data

[Andreas Tille]

On Tue, 4 Mar 2003, Horst Herb wrote:

> Suppose you have a compound primary key spanning the first three columns -
You are guessing right:

CREATE TABLE tlkpEinsenderMeldetyp (
    IdEinsender int NOT NULL CONSTRAINT PK_Sender REFERENCES 
InfluenzaWebEinsender(IdEinsender) ,
    IdMeldeTyp  int NOT NULL CONSTRAINT CH_MeldHin
                             CHECK (test_property(IdMeldeTyp, 'IWMeldeTyp')) ,
               /* Meldetypen sind in tlkpcommon aufgeführt */
    IdHinRueck  int NOT NULL CONSTRAINT CH_MeldRueck
                             CHECK (test_property(IdHinRueck, 
'IWMeldeRichtung')) ,
               /* Melderichtung ist in tlkpcommon aufgeführt */
    Sort        int NOT NULL , /* Priorität des Meldetyps */
    CONSTRAINT PK_EinsenderMeldetyp PRIMARY KEY 
(IdEinsender,IdMeldeTyp,IdHinRueck)
) ;

CREATE TABLE KInz (
  IdSaison     int NOT NULL CONSTRAINT Ref_EISSSaison    REFERENCES 
SaisonStart(IdSaisonStart) ,
  IdAlter      int NOT NULL CONSTRAINT Ref_AltersGruppe  REFERENCES 
AltersGruppe(Id) ,
  IdBLand      int NOT NULL CONSTRAINT Ref_geoBundesland REFERENCES 
geoBundesland(IdBLand) ,
  MeldeWoche   int NOT NULL,
  KInz         int NOT NULL,
  ts           timestamp DEFAULT now(),
  CONSTRAINT PK_KInz PRIMARY KEY (IdSaison,IdAlter,IdBLand,MeldeWoche)
);

> currently, my metadata module assumes primary keys are based on a single
> column.
> Easy to fix though, although I would strongly advise against such compunt PKs.
> Nothing but trouble, and defies the purpose of a primary key in the first
> place.
Would you advice me to use an extra column as primary key here or just have
no primary key but only a simple constraint.

(Feel free to post in private because it becomes OT on this list ...)

Kind regards and thanks for the hint

        Andreas.