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@ucdhep.ucdavis.edu
(530) 754-7226
