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

Le mercredi 18 avril 2012 à 00:06 +0200, Nils Gösche a écrit :
> Bartosz Dmytrak wrote:
>
> >
> The reason I like this particular way of modeling the data is that I have a guarantee that there won't be an entry in both derived tables at the same time for the same row in the base table; also, I can have further constraints and foreign keys from and to the base table.
>

use a trigger on each of the derived tables, that cancels any insert if
the same id already exists in the other table?

> Of course, I could also omit the type field and simply live with the possibility of having two rows in the derived tables referring to the same row of the base table. But it would be nice if I could rule that out with simple constraints.

You don't say how your data gets inserted, but considering how
complicated your preferred option looks, I have to ask why you can't use
something as simple as :

CREATE TABLE base (
id int PRIMARY KEY,
some_data int NOT NULL,
type integer NOT NULL DEFAULT 1
);

-- type 1 = derived1, type 2 = derived2

CREATE TABLE derived1 (
id int PRIMARY KEY,
data1 int NOT NULL,
FOREIGN KEY (id) REFERENCES base (id) ON DELETE CASCADE
);

CREATE TABLE derived2 (
id int PRIMARY KEY,
data2 text NOT NULL,
FOREIGN KEY (id) REFERENCES base (id) ON DELETE CASCADE
);

You'll have to build the queries according to the value of type, but
this should give you the features you mention?

--
Vincent Veyron
http://marica.fr/
Logiciel de gestion des sinistres assurances et des dossiers contentieux pour le service juridique