Implementing complicated constraints

[Agoston Bejo]

Hi, I'm having trouble with implementing some constraints on the database
level. An example:

--Table A(AID, BID ref. B.BID, ATXT)
--Table B(BID, CID ref. C.CID)
--Table C(CID)

upon insertion into or updating in A I would like to force that ATXT is
unique with respect to CID, i.e.

SELECT COUNT(*) FROM A,B,C
WHERE A.BID = B.BID
AND B.CID = CID
AND CID = fn_get_cid_for_bid(:new.BID)
AND A.ATXT = :new.ATXT
AND A.AID <> :new.AID

should be 0.

This I cannot force with a check constraint since it is not allowed to
contain subqueries.
If I try to write a trigger that checks the above condition and raises an
application error, then I always stumble upon the "table is mutating,
trigger/function may not see it" -type error, since it involves a select on
the table that is being changed at the time.

However, it would be better to implement such a constraint on the database
level, rather than scatter checks throughout the application.

Is there a standard way solve this type of problem?

[Mark C. Stock]

"Agoston Bejo" <gu***@freemail.hu> wrote in message
news:cj**********@news.caesar.elte.hu...
| Hi, I'm having trouble with implementing some constraints on the database
| level. An example:
|
| --Table A(AID, BID ref. B.BID, ATXT)
| --Table B(BID, CID ref. C.CID)
| --Table C(CID)
|
| upon insertion into or updating in A I would like to force that ATXT is
| unique with respect to CID, i.e.
|
| SELECT COUNT(*) FROM A,B,C
| WHERE A.BID = B.BID
| AND B.CID = CID
| AND CID = fn_get_cid_for_bid(:new.BID)
| AND A.ATXT = :new.ATXT
| AND A.AID <> :new.AID
|
| should be 0.
|
| This I cannot force with a check constraint since it is not allowed to
| contain subqueries.
| If I try to write a trigger that checks the above condition and raises an
| application error, then I always stumble upon the "table is mutating,
| trigger/function may not see it" -type error, since it involves a select
on
| the table that is being changed at the time.
|
| However, it would be better to implement such a constraint on the database
| level, rather than scatter checks throughout the application.
|
| Is there a standard way solve this type of problem?
|
|
|

hopefully i'm understanding the problem correctly....

there's no standard way, since your uniqueness constraint is based on
multiple tables (the way you have it currently designed)

however, if A.TXT is NOT NULL, then you could denormalize the table
implementation and include CID in A and put a unique constraint on (A.CID,
A.TXT). if A.TXT is NULL-able, you may want to denormalize the table
implementation and have a table that includes just the PK of A (AID?), CID,
and TXT, storing actual TXT entries out-of-line from table A, so that a
unique constraint can be put on CID and TXT in that table. the reason
NULL/NOT NULL comes into play is that oracle would not prevent multiple NULL
values in a one-column unique index, but would prevent multiple TXT nulls in
this case, since the CID value would be repeated

++ mcs

[wario]

--Table A(AID, BID ref. B.BID, CID ref. C.CID, ATXT)
--Table B(BID, CID ref. C.CID)
--Table C(CID)

1. Add CID key to table A.
2. Create unique index on CID and ATXT.

Wario

[Mark D Powell]

"Agoston Bejo" <gu***@freemail.hu> wrote in message news:<cj**********@news.caesar.elte.hu>...

Hi, I'm having trouble with implementing some constraints on the database
level. An example:

--Table A(AID, BID ref. B.BID, ATXT)
--Table B(BID, CID ref. C.CID)
--Table C(CID)

upon insertion into or updating in A I would like to force that ATXT is
unique with respect to CID, i.e.

SELECT COUNT(*) FROM A,B,C
WHERE A.BID = B.BID
AND B.CID = CID
AND CID = fn_get_cid_for_bid(:new.BID)
AND A.ATXT = :new.ATXT
AND A.AID <> :new.AID

should be 0.

This I cannot force with a check constraint since it is not allowed to
contain subqueries.
If I try to write a trigger that checks the above condition and raises an
application error, then I always stumble upon the "table is mutating,
trigger/function may not see it" -type error, since it involves a select on
the table that is being changed at the time.

However, it would be better to implement such a constraint on the database
level, rather than scatter checks throughout the application.

Is there a standard way solve this type of problem?

The fact that the ATXT column of A needs to be unique in relation to
the CID value which does not appear in A indicates a relational design
flaw in your tables.

Without changing the table design there are several options:
1- Perform the inserts/update via stored code that performs the
validation up front avoiding the mutating table error

2- substitute a view for the table and use instead of triggers to
perform the validation and redirect the DML

3- Use a combination of before and after, statement and row level
triggers to work around the mutating table error. There are notes on
this technique on metalink, but I have never managed to use the
technique successfully to allow us to do what we needed in cases
similar to what you want to do.

HTH -- Mark D Powell --

[Agoston Bejo]

I would like to do this so that the structure of the tables remain the same.
(Since in the real-life analogue of this example it is important that the
structures reflect the real-life concepts.)

Anyway, the question is rather about the implementation of more complicated
constraints on tables than this specific example (which actually led me to
pose this question in the newsgroup).
"wario" <wa********@insightbb.com> wrote in message
news:96************************@posting.google.com ...

--Table A(AID, BID ref. B.BID, CID ref. C.CID, ATXT)
--Table B(BID, CID ref. C.CID)
--Table C(CID)

1. Add CID key to table A.
2. Create unique index on CID and ATXT.

Wario

[Mark C. Stock]

"Agoston Bejo" <gu***@freemail.hu> wrote in message
news:cj**********@news.caesar.elte.hu...
| I would like to do this so that the structure of the tables remain the
same.
| (Since in the real-life analogue of this example it is important that the
| structures reflect the real-life concepts.)
|
| Anyway, the question is rather about the implementation of more
complicated
| constraints on tables than this specific example (which actually led me to
| pose this question in the newsgroup).
|
|
| "wario" <wa********@insightbb.com> wrote in message
| news:96************************@posting.google.com ...
| > --Table A(AID, BID ref. B.BID, CID ref. C.CID, ATXT)
| > --Table B(BID, CID ref. C.CID)
| > --Table C(CID)
| >
| > 1. Add CID key to table A.
| > 2. Create unique index on CID and ATXT.
| >
| > Wario
|
|

Typically, the conceptual data model should accurately reflect the business
reality -- but the actual table structure that implements the model will
usually be compromised (denormalized) to accommodate technology

keep in mind that if these are dependent tables, the natural key structure
would be:

C
CID
PK (CID )

B
CID
BID
FK(CID) ref C
PK(CID, BID)

A
CID
BID
AID
TXT
FK(CID, BID) ref B
PK(CID, BID, AID)

which then give you the option of adding the UK to table A -- but again, it
depends on factors that you've not yet stated

++ mcs

[Ed prochak]

"Agoston Bejo" <gu***@freemail.hu> wrote in message news:<cj**********@news.caesar.elte.hu>...

I would like to do this so that the structure of the tables remain the same.
(Since in the real-life analogue of this example it is important that the
structures reflect the real-life concepts.)

Anyway, the question is rather about the implementation of more complicated
constraints on tables than this specific example (which actually led me to
pose this question in the newsgroup).
"wario" <wa********@insightbb.com> wrote in message
news:96************************@posting.google.com ...

--Table A(AID, BID ref. B.BID, CID ref. C.CID, ATXT)
--Table B(BID, CID ref. C.CID)
--Table C(CID)

1. Add CID key to table A.
2. Create unique index on CID and ATXT.

Wario

More complicated constraints require more complicated datamodels.
Why do you fear adding columns to tables when it allows you to better
model the "real-life concepts"?

Sounds like a meta constraint that is holding you back.
("We can not change the existing database structure since it is
supplied by an outside vendor.") Layering a kludge on top will never
be as good as making the changes needed in the datamodel.

Good luck.
Ed

[Noons]

Ma*********@eds.com (Mark D Powell) wrote in message news:<26**************************@posting.google. com>...

The fact that the ATXT column of A needs to be unique in relation to
the CID value which does not appear in A indicates a relational design
flaw in your tables.
Bingo. In one.
Without changing the table design there are several options:
1- Perform the inserts/update via stored code that performs the
validation up front avoiding the mutating table error
This might have problems with apps that enforce "no changes
anywhere or else...".

2- substitute a view for the table and use instead of triggers to
perform the validation and redirect the DML
I've used this technique to implement complex relationships
- to do with subtyping and complex M-M - and even complete
new schemas, without having to change any original app code.
It works like a charm, is fast and is probably the most
transparent way of achieving this. Perfect solution IMHO.

3- Use a combination of before and after, statement and row level
triggers to work around the mutating table error. There are notes on
this technique on metalink, but I have never managed to use the
technique successfully to allow us to do what we needed in cases
similar to what you want to do.

Too much trouble. The instead of trigger with a view is perfect
for this.

I like Tom Kite's idea of a FBI, as proposed by HB. But I'm not
sure it won't introduce other problems. IIRC, Tom introduced
that technique at a time when instead of triggers were not yet
available. He probably would have used the triggers nowadays.

[Holger Baer]

Noons wrote:
[...]

Too much trouble. The instead of trigger with a view is perfect
for this.

I like Tom Kite's idea of a FBI, as proposed by HB. But I'm not
sure it won't introduce other problems. IIRC, Tom introduced
that technique at a time when instead of triggers were not yet
available. He probably would have used the triggers nowadays.

I'm not sure if the FBI solution introduces other problems, but
that's a different question.

However I'm sure instead of trigger where available in 8.0, whereas
FBI was introduced in 8i. So I'm not sure about the conclusion that
Tom wouldn't use this nowadays. What do we say in cases like this?
It depends....

Cheers, Holger

[Noons]

Holger Baer <ho*********@science-computing.de> wrote in message news:<cj**********@news.BelWue.DE>...

However I'm sure instead of trigger where available in 8.0, whereas

I don't think so.

[Holger Baer]

Noons wrote:

Holger Baer <ho*********@science-computing.de> wrote in message news:<cj**********@news.BelWue.DE>...

However I'm sure instead of trigger where available in 8.0, whereas

I don't think so.

This is an excerpt from the docs on my prehistoric 8.0.6 CD for Windows:
<quote>
-- INSTEAD OF Triggers
Use INSTEAD OF triggers to perform DELETE, UPDATE, or INSERT operations on views, which are not
inherently modifiable. "The View Query" for a list of constructs that prevent inserts, updates, or
deletes on a view. In the following example, customer data is stored in two tables. The object view
ALL_CUSTOMERS is created as a UNION of the two tables, CUSTOMERS_SJ and CUSTOMERS_PA. An INSTEAD OF
trigger is used to insert values:

CREATE TABLE customers_sj
( cust NUMBER(6),
address VARCHAR2(50),
credit NUMBER(9,2) );

CREATE TABLE customers_pa
( cust NUMBER(6),
address VARCHAR2(50),
credit NUMBER(9,2) );

CREATE TYPE customer_t AS OBJECT
( cust NUMBER(6),
address VARCHAR2(50),
credit NUMBER(9,2),
location VARCHAR2(20) );

CREATE VIEW all_customers (cust)
AS SELECT customer_t (cust, address, credit, 'SAN_JOSE')
FROM customers_sj
UNION ALL
SELECT customer_t(cust, address, credit, 'PALO_ALTO')
FROM customers_pa;

CREATE TRIGGER instrig INSTEAD OF INSERT ON all_customers
FOR EACH ROW
BEGIN
IF (:new.location = 'SAN_JOSE') THEN
INSERT INTO customers_sj
VALUES (:new.cust, :new.address, :new.credit);
ELSE
INSERT INTO customers_pa
VALUES (:new.cust, :new.address, :new.credit);
END IF;
END;

</quote>

And I remember them being available for sure because then I was a developer
at a small company and we weren't allowed to use any enterprise feature because
it would make the initial licence cost so much higher for our prospective
customers.

Plus, after I moved to my current employer, I started to support an application
that made heavily use of views with instead of triggers. They used 8.0.6 too.
And if I didn't force them, they would still ...
Cheers,

Holger

[Noons]

Holger Baer <ho*********@science-computing.de> wrote in message news:<cj**********@news.BelWue.DE>...

This is an excerpt from the docs on my prehistoric 8.0.6 CD for Windows:
Yeah, I believe you. Did a little "digging" myself and found
out. I'll have to understand why TK is going for FBI rather
than instead of. Doesn't make sense.
Plus, after I moved to my current employer, I started to support an application
that made heavily use of views with instead of triggers. They used 8.0.6 too.
And if I didn't force them, they would still ...

:) Takes all kinds, doesn't it?
My last 8.0.6 was Feb 03. After I sent them
a memo in early 01 explaining the dangers of staying
with old releases, they finally decided to upgrade in
Feb 03. Peoplesoft site. What else...

[Holger Baer]

Noons wrote:

Holger Baer <ho*********@science-computing.de> wrote in message news:<cj**********@news.BelWue.DE>...

This is an excerpt from the docs on my prehistoric 8.0.6 CD for Windows:

Yeah, I believe you. Did a little "digging" myself and found
out. I'll have to understand why TK is going for FBI rather
than instead of. Doesn't make sense.

I believe in his book (which I haven't got at hand right now) Tom explains why,
maybe I get the chance for a little digging myself. My rather clumsy recollection
is that this way you've declarative referential integrity and don't have to dig
through the code.

Plus, after I moved to my current employer, I started to support an application
that made heavily use of views with instead of triggers. They used 8.0.6 too.
And if I didn't force them, they would still ...

:) Takes all kinds, doesn't it?
My last 8.0.6 was Feb 03. After I sent them
a memo in early 01 explaining the dangers of staying
with old releases, they finally decided to upgrade in
Feb 03. Peoplesoft site. What else...

Take any customized software or worse, one of the famous home grown applications
the customer wrote himself (and the original developers as well as the documentation
are lost in the mist of time)... Oh, and in Feb 03 I could convince another customer
at least to move from 7.3.4 to 8.1.6 which got finally disposed this summer.

Did I already tell that today(!) I was called because at another site their hp-ux 10.20/ Oracle
8.0.4 server died? Lucky ol' us that we didn't support that one. Backups exist. But no
Hardware to fix or replace the old one....
Cheers,

Holger

[Noons]

Holger Baer <ho*********@science-computing.de> wrote in message news:<cj**********@news.BelWue.DE>...

Did I already tell that today(!) I was called because at another site their hp-ux 10.20/ Oracle
8.0.4 server died? Lucky ol' us that we didn't support that one. Backups exist. But no
Hardware to fix or replace the old one....

Nnow the feeling only too well. Had a customer for a few years on
7.3.4 NT4. They never bothered to upgrade even though their PS HR
app was carking everywhere with 16-bit code. Then they kicked us out:
apparently we were "too expensive" even though we were charging one
tenth what the competition does.

Then they had the nerve to call us last year to "come and
fix it" when the server (a Pentium 1!) finally went up in flames.
And they sort of expected us to do this under some presumed
"warranty"! Of course. Look, there goes another pig...

[Holger Baer]

Noons wrote:

Holger Baer <ho*********@science-computing.de> wrote in message news:<cj**********@news.BelWue.DE>...

This is an excerpt from the docs on my prehistoric 8.0.6 CD for Windows:

Yeah, I believe you. Did a little "digging" myself and found
out. I'll have to understand why TK is going for FBI rather
than instead of. Doesn't make sense.

I went through chapter 7 of Tom Kyte's book again. It makes perfect sense in
the cases he described, because with the trigger you either lock the
tables in question, or you end up with constraint violations:

Session A:
insert into view //trigger fires check constraints ok
Session B:
insert into view // trigger fires check
// constraints ok

Session A:
commit; // trigger does not reevalute constraints
Session B:
commit; // now we violated our constraint

I don't have the time at hand to check the proposed solution if it
behaves any different, but at least this is something to watch out.
And it's the reason why Tom chooses FBI over trigger.
Cheers,

Holger

[Noons]

Holger Baer <ho*********@science-computing.de> wrote in message news:<cj**********@news.BelWue.DE>...

I went through chapter 7 of Tom Kyte's book again. It makes perfect sense in

Thanks, going to re-read that one.