Feature Proposal: Constant Values in Columns or Foreign Keys - Mailing list pgsql-general

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."