Skip site navigation (1) Skip section navigation (2)

help with inheritance

From: Michael Case <mecase(at)ucdavis(dot)edu>
To: pgsql-novice <pgsql-novice(at)postgresql(dot)org>
Subject: help with inheritance
Date: 2001-06-15 17:21:04
Message-ID: 3B2A4400.C57A9A3A@ucdhep.ucdavis.edu (view raw or flat)
Thread:
Lists: pgsql-novice
Hi,

I tried making something like the following script (simplified).  The
idea is that T1 and T2 are BaseT's, but T2's have components (T2Comp)
made up of T1's.  I want the names of T1's and T2's to be unique.  I'm
not sure I'm using inheritance properly.

1) The unique and primary key constraints on BaseT were not enforced on
T1 or T2.
2) If I copy the three constraint lines below name to T1 and T2, then
indeed, T1 and T2 enforce those constraints.
3) However, it still does not enforce uniqueness I want in BaseT.  In
other words adding the name 'fred' to T1 puts a 'fred' in BaseT, adding
'fred' in T2 adds another 'fred' to BaseT.

How can I make sure that a 'fred' in T1 prevents a 'fred' in T2?  I was
GUESSING that INHERITANCE would behave like old-fashioned 'automatic
masters' only better!

Any help would be appreciated in explaining INHERITANCE, or just in  how
to do what I want to do.  I know another way, but it requires that every
time you INSERT a T1 OR T2 you first need to INSERT into BaseT.  Maybe I
need to write functions that ON INSERT or ON UPDATE (on T1 and T2) make
sure that BaseT is updated first (not use inheritance, but use BaseT as
a common name repository)... Thanks in advance.

-- mike

===================================
CREATE TABLE BaseT
(
   mbid           SERIAL,
   name           VARCHAR(64),
   UNIQUE (mbid),
   UNIQUE (name),
   PRIMARY KEY (mbid)
);

CREATE TABLE T1
(
   v1              DOUBLE PRECISION DEFAULT 0.0,
   v2              DOUBLE PRECISION DEFAULT 0.0,
   v3              DOUBLE PRECISION DEFAULT 0.0
)
  INHERITS BaseT
;

CREATE TABLE T2
(
   v4 INT DEFAULT 0
)
  INHERITS BaseT  
;

CREATE TABLE T2Comp
(
   mcbid        INT,
   mbid         INT,
   v5           INT

   PRIMARY KEY (mcbid, mbid),
   FOREIGN KEY (mcbid) REFERENCES BaseT(mbid),
   FOREIGN KEY (mbid) REFERENCES BaseT(mbid),
   CHECK ((aProportion > 0 OR fractionMass > 0) AND (aProportion = 0 OR
fractionMass = 0)),
   CHECK (cmbid != mbid)
);

-- 
Michael E. Case
UC Davis
case(at)ucdhep(dot)ucdavis(dot)edu
(530) 754-7226

pgsql-novice by date

Next:From: Frank HilliardDate: 2001-06-15 21:29:58
Subject: Tuple size too big in 7.0
Previous:From: Willie NorthwayDate: 2001-06-15 14:27:36
Subject: Re: configuring question?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group