Re: Feature Proposal: Constant Values in Columns or Foreign Keys

Hi,
how about inheritance in postgres?

CREATE TABLE "tblBase"
(
id serial NOT NULL, -- serial type is my assumption.
"SomeData" integer,
CONSTRAINT "tblBase_pkey" PRIMARY KEY (id )
)
WITH (
OIDS=FALSE
);

CREATE TABLE "tblDerived1"
(
-- Inherited from table "tblBase": id integer NOT NULL DEFAULT
nextval('"tblBase_id_seq"'::regclass),
-- Inherited from table "tblBase": "SomeData" integer,
"Data1" integer,
CONSTRAINT "tblDerived1_pkey" PRIMARY KEY (id )
)
INHERITS ("tblBase")
WITH (
OIDS=FALSE
);

CREATE TABLE "tblDerived2"
(
-- Inherited from table "tblBase": id integer NOT NULL DEFAULT
nextval('"tblBase_id_seq"'::regclass),
-- Inherited from table "tblBase": "SomeData" integer,
"Data2" text,
CONSTRAINT "tblDerived2_pkey" PRIMARY KEY (id )
)
INHERITS ("tblBase")
WITH (
OIDS=FALSE
);

inheritance is described in doc here:
http://www.postgresql.org/docs/9.1/static/ddl-inherit.html

With this approach all IDs will use the same sequence so there will not
be duplicated PKs in inherited tables.

This could be also modeled with "standard" SQL approach
without redundant information. Solution depends on requirements.

Regards,
Bartek

2012/4/17 Nils Gösche <cartan(at)cartan(dot)de>

> Hi!
>
> I have a little feature proposal. Let me try to explain the motivation
> behind it.
>
> Suppose our application has two types of objects, looking somewhat like
> this:
>
> abstract class Base
> {
> public int Id;
> public int SomeData;
> }
>
> class Derived1 : Base
> {
> public int Data1;
> }
>
> class Derived2 : Base
> {
> public string Data2;
> }
>
> There are many ways of modeling this in a relational database. I am
> interested in this one:
>
> CREATE TYPE derived_type AS ENUM ('derived1', 'derived2);
>
> CREATE TABLE base (
> id int PRIMARY KEY,
> some_data int NOT NULL,
> type derived_type NOT NULL
> );
>
> CREATE UNIQUE INDEX base_derived_type_index ON base (id, derived_type);
>
> CREATE TABLE derived1 (
> id int PRIMARY KEY,
> data1 int NOT NULL,
> type derived_type NOT NULL CHECK (type = 'derived1'),
> FOREIGN KEY (id, type) REFERENCES base (id, type) ON DELETE CASCADE
> )
>
> CREATE TABLE derived2 (
> id int PRIMARY KEY,
> data2 text NOT NULL,
> type derived_type NOT NULL CHECK (type = 'derived2'),
> FOREIGN KEY (id, type) REFERENCES base (id, type) ON DELETE CASCADE
> )
>
> Note that the type column in derived1 and derived2 ensures that there is at
> most one row in either derived1 or derived2 which refers to a given row in
> "base".
>
> This works fine, actually. What bugs me, though, is the redundant data in
> the type columns of derived1 and derived2. It would be nice if I could
> either declare the columns as constant (so the data wouldn't be stored on
> disk anymore), or (even better) use a constant value in the foreign keys,
> as
> in
>
> FOREIGN KEY (id, 'derived2') REFERENCES base (id, type) ON DELETE
> CASCADE
>
> In the latter case, I could omit the type column of derived1 and derived2
> altogether.
>
> I suspect that it wouldn't be terribly hard to implement this. What do you
> think? Wouldn't this be nice to have?
>
> Regards,
> --
> Nils Gösche
> "Don't ask for whom the <CTRL-G> tolls."
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>