Thread: Inheritance
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Is there a way to set it up so that tables B and C are both children of
table A, but where if you add records to B or C, they will show up in A as
well (albiet without the extra fields provided by B and C)?
In case that wasn't thouroughly confusing, allow me to try another
analogy:
CREATE TABLE Entities (num serial PRIMARY KEY);
CREATE TABLE Corporations (name char(40));
CREATE TABLE Players (name char(40),
corp integer REFERENCES Corporations
) INHERITS Entities;
Now, if I insert something into players, like so:
INSERT INTO Players (name) VALUES ('John');
John WILL get a number pulled from the same set as the entity and
corporation tables, but John WILL NOT show up in the entities table. Is
there any way to get new entries in child tables to show up in the parent
table?
Ian
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.1 (GNU/Linux)
Comment: For info see http://www.gnupg.org
iD8DBQE5gZTFfn9ub9ZE1xoRAmx8AKDglWulL4eo4mvTYosaIGbsv/rj/QCgzd4H
jcD02dIs1JkM4U4uKkg7zr4=
=86pb
-----END PGP SIGNATURE-----
Ian Turner wrote:
>Is there a way to set it up so that tables B and C are both children of
>table A, but where if you add records to B or C, they will show up in A as
>well (albiet without the extra fields provided by B and C)?
>
>In case that wasn't thouroughly confusing, allow me to try another
>analogy:
>
>CREATE TABLE Entities (num serial PRIMARY KEY);
>CREATE TABLE Corporations (name char(40));
>CREATE TABLE Players (name char(40),
> corp integer REFERENCES Corporations
>) INHERITS Entities;
>
>Now, if I insert something into players, like so:
>INSERT INTO Players (name) VALUES ('John');
>
>John WILL get a number pulled from the same set as the entity and
>corporation tables, but John WILL NOT show up in the entities table. Is
>there any way to get new entries in child tables to show up in the parent
>table?
`SELECT * FROM Entities*;'
up to and including 7.0.x.
In 7.1, I believe a select on the parent will automatically show the
children and you will need to do something like `SELECT * FROM ONLY Entities'
to exclude descendant tables.
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"And Samuel said, Hath the LORD as great delight in
burnt offerings and sacrifices, as in obeying the
voice of the LORD? Behold, to obey is better than
sacrifice, and to hearken than the fat of rams."
I Samuel 15:22
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > `SELECT * FROM Entities*;' > > up to and including 7.0.x. > > In 7.1, I believe a select on the parent will automatically show the > children and you will need to do something like `SELECT * FROM ONLY Entities' > to exclude descendant tables. Yes, but what about referential integrity? Can I have a table column reference a column from Entities*? In my experimentation, this is not the case. Ian -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.1 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE5g87bfn9ub9ZE1xoRAmKzAJwLgpj/eutM7WyWBfiaukLOeA3SmQCeIYbY pa8M8FA18fnimk5DDF4UlEg= =W2iE -----END PGP SIGNATURE-----
Ian Turner wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> > `SELECT * FROM Entities*;'
> >
> > up to and including 7.0.x.
> >
> > In 7.1, I believe a select on the parent will automatically show the
> > children and you will need to do something like `SELECT * FROM ONLY Entities'
> > to exclude descendant tables.
>
> Yes, but what about referential integrity? Can I have a table column
> reference a column from Entities*? In my experimentation, this is not the
> case.
Referential integrity doesn't work with inheritance, and will
not in 7.1.
It isn't possible to put a unique constraint on a column of
Entities* (AFAIK). So that a SELECT pkey FROM Entities will
never return any duplicates? The RI implementation of
PostgreSQL doesn't insist on such a unique constraint to
exist up to now, but it is required by the SQL specs and thus
we'll do so someday.
pgsql=# create table t1 (pkey integer primary key);
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 't1_pkey' for table 't1'
CREATE
pgsql=# create table t2 (val integer) inherits (t1);
CREATE
pgsql=# insert into t1 values (1);
INSERT 21274 1
pgsql=# insert into t2 values (1, 11);
INSERT 21275 1
pgsql=# select * from t1;
pkey
------
1
1
(2 rows)
Am I missing something here?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
Jan Wieck wrote: >> Yes, but what about referential integrity? Can I have a table column >> reference a column from Entities*? In my experimentation, this is not the >> case. > > Referential integrity doesn't work with inheritance, and will > not in 7.1. > > It isn't possible to put a unique constraint on a column of > Entities* (AFAIK). So that a SELECT pkey FROM Entities will > never return any duplicates? The RI implementation of > PostgreSQL doesn't insist on such a unique constraint to > exist up to now, but it is required by the SQL specs and thus > we'll do so someday. The corollary of using RI on an inheritance tree is that there should be a unique index on the primary keys of the whole tree. If constraints could be inherited, this would become available. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Have not I commanded thee? Be strong and of a good courage; be not afraid, neither be thou dismayed; for the LORD thy God is with thee whithersoever thou goest." Joshua 1:9
Jan Wieck wrote:
>Oliver Elphick wrote:
>> Jan Wieck wrote:
>> >> Yes, but what about referential integrity? Can I have a table column
>> >> reference a column from Entities*? In my experimentation, this is not
> the
>> >> case.
>> >
>> > Referential integrity doesn't work with inheritance, and will
>> > not in 7.1.
>> >
>> > It isn't possible to put a unique constraint on a column of
>> > Entities* (AFAIK). So that a SELECT pkey FROM Entities will
>> > never return any duplicates? The RI implementation of
>> > PostgreSQL doesn't insist on such a unique constraint to
>> > exist up to now, but it is required by the SQL specs and thus
>> > we'll do so someday.
>>
>> The corollary of using RI on an inheritance tree is that there should be a
>> unique index on the primary keys of the whole tree. If constraints could
>be
>> inherited, this would become available.
>
> The UNIQUE constraint is implemented as a UNIQUE INDEX in
> PostgreSQL. And exactly what you say: "ONE" index on the
> whole tree would be required to do it.
>
> What you're asking for is index inheritance instead of RI
> trigger inheritance first - right? So that an index will
> contain tuples for all the keys present in it's tables
> subclasses, for beeing able to detect a dupkey.
Yes, at least as an option. In many cases of inheritance the id should be
unique in the whole tree, and so a unique index on the whole tree is
needed. If this index is present, the tree can be available for RI use.
There may be cases where a unique index is not wanted, but in that case the
cost is that RI becomes unavailable except on individual tables.
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Have not I commanded thee? Be strong and of a good
courage; be not afraid, neither be thou dismayed; for
the LORD thy God is with thee whithersoever thou
goest." Joshua 1:9