From: Ann W. Harrison
Sent: Saturday, June 05, 2004 5:34 PM

>
> At 08:29 PM 6/4/2004, Jerome Bouvattier wrote:

[..]

> >This would really help when you want integrity constraints that can't be
> >formulated with decl constraints or when you explicitly don't want to use
> >decl constraints (e.g you don't want to introduce a poorly selective
> index).
>
> At some point we need to disentangle indexes and foreign key constraints.
> For reasons most unclear to me, the internals of the engine know that
> there is an index where there is a foreign key. Having the creation
> of a constraint build an index is OK, I guess, but the user should be
> allowed to delete it.

Indeed, that would be great. Ability to specify a compound and more
selective index would be great too.

Regarding the FK and low selectivity indexes issue, it is commonly advocated
to drop the declarative FK and write custom RI in triggers. But at the same,
as you say yourself, custom RI isn't 100% safe.
It's a bit misleading.

To make things more safe when I write custom RI triggers, I grant deletion
rights on the referenced table only to a special maintenance_user that would
gain exclusive access before doing anything to the db.

Is there anything better to do to achieve 100% safe custom RI ?

> What constraints can you not express in declarative language?

Here is one, but I have more should you need it.
(Sorry, it's a bit long)

I record specifications for products. (Metadata extract follows)
A spec references a product and a feature.
Features are of two kinds (FeatureType): Discrete or quantity
For discrete features, the possible values are stored in the FeatureValue
table.
When a Spec record refers to a "Quantity" feature, it stores the value in
the field QuantityValue.
When it refers to a discrete feature, it stores a FK on FeatureValue in
FeatureValueID.

CREATE TABLE Feature
(
ID INTEGER NOT NULL,
FeatureType D_FEATURE_TYPE NOT NULL,
DefaultUnitID INTEGER,
UnTransName VARCHAR(40) NOT NULL,
IdentSize INTEGER NOT NULL,
[..]
CONSTRAINT PK_Feature PRIMARY KEY (ID)
)
^
CREATE TABLE FeatureValue
(
ID INTEGER NOT NULL,
FeatureID INTEGER NOT NULL,
Ident VARCHAR(10) NOT NULL,
UnTransName VARCHAR(50) NOT NULL,
Ord INTEGER NOT NULL,
[..]
CONSTRAINT PK_FeatureValue PRIMARY KEY (ID)
)
^
CREATE TABLE Product
(
ID INTEGER NOT NULL,
BaseProductGroupID INTEGER NOT NULL,
BrandID INTEGER NOT NULL,
ModelName VARCHAR(25) NOT NULL,
[..]
CONSTRAINT PK_Product PRIMARY KEY (ID)
)
^
CREATE TABLE Specs
(
ID INTEGER NOT NULL,
ProductID INTEGER NOT NULL,
FeatureID INTEGER NOT NULL,
QuantityValue NUMERIC(9,2),
UnitID INTEGER,
FeatureValueID INTEGER,
[..]
CONSTRAINT PK_Specs PRIMARY KEY (ID)
)
^
ALTER TABLE FeatureValue
ADD CONSTRAINT Relation_209 FOREIGN KEY
( FeatureID )
REFERENCES Feature
( ID )
ON DELETE CASCADE
^
ALTER TABLE Specs
ADD CONSTRAINT Relation_272 FOREIGN KEY
( ProductID )
REFERENCES Product
( ID )
ON DELETE CASCADE
^
ALTER TABLE Specs
ADD CONSTRAINT Relation_274 FOREIGN KEY
( FeatureValueID )
REFERENCES FeatureValue
( ID )
^
ALTER TABLE Specs
ADD CONSTRAINT Relation_282 FOREIGN KEY
( FeatureID )
REFERENCES Feature
( ID )
^

Now come the constraints I cannot express in declarative language.

- "Specs' fields QuantityValue & FeatureValueID must be filled in accordance
with the feature's type" (QuantityValue must be null if we deal with a
discrete feature and vice versa)

Looking at the original problem, It's safe *because* I forbid any change to
the feature's type (thru a trigger again)

- "Specs' FeatureValueID, if not null, must comply with the range of
possibilities related to the FeatureID"
Something like that :

if <we deal with a discrete feat.> then begin
if (not exists(Select 1 from featurevalue
where ID = NEW.FeatureValueID and FEATUREID = NEW.FEATUREID))
then
exception E_SPECS_INVALID_FEAT_VAL;
end

From the isolation pov, this is also safe I think *because* I forbid
reassigning a FeatureValue to a different feature.

- "The actual stringLength of FeatureValue.Ident field cannot exceed the
parent feature's IdentSize"

Regarding isolation, this one is a problem since I can't prevent a Feature's
IdentSize from being changed.
Furtunately, those tables are quite static.

> As a
> general rule, declarative constraints allow the engine more latitude
> to choose the optimal execution method than procedural constraints.

I do prefer declarative constraints ... whenever I can use them.

> >AFAIU, currently, writing custom constraints in triggers that reads data
> >outside the modified record isn't 100% safe since a trigger runs in the
> >client's transaction context (which could be in "snapshot" mode).
>
> That's right. And even if it were in inconsistent mode, you still
> wouldn't see uncommitted changes.

Hmmm. It's not that clear to me, but I already wrote too much ;-)


Thanks for your patience.

--
Jerome