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.
CREATE TABLE BaseT
PRIMARY KEY (mbid)
CREATE TABLE T1
v1 DOUBLE PRECISION DEFAULT 0.0,
v2 DOUBLE PRECISION DEFAULT 0.0,
v3 DOUBLE PRECISION DEFAULT 0.0
CREATE TABLE T2
v4 INT DEFAULT 0
CREATE TABLE T2Comp
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
pgsql-novice by date
|Next:||From: Frank Hilliard||Date: 2001-06-15 21:29:58|
|Subject: Tuple size too big in 7.0|
|Previous:||From: Willie Northway||Date: 2001-06-15 14:27:36|
|Subject: Re: configuring question?|