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? 15 4519
"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
--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
"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 --
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********@ins ightbb.com> wrote in message
news:96******** *************** *@posting.googl e.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
"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********@ins ightbb.com> wrote in message
| news:96******** *************** *@posting.googl e.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
"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********@ins ightbb.com> wrote in message news:96******** *************** *@posting.googl e.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 Ma*********@eds .com (Mark D Powell) wrote in message news:<26******* *************** ****@posting.go ogle.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.
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
Holger Baer <ho*********@sc ience-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 thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Paul |
last post by:
Hi:
I am not sure whether somebody can sort it out for me.
I am doing data transfer from one oracle database to another. Both of
them has the same structure (like same tables etc).the only thing i
need to do is data transfering.
i designed a vb program to do this, What i am worrying is when I run
this program it might fire some...
|
by: Dmitri Zhukov |
last post by:
Hello everyone,
I am implementing software which has a medium sized number of text strings
(max 100K) which are represented in a listbox.
I want user to be able to search the string by typing the first letters of
the record and showing coinciding record if any (similar to quickserach
functionality of Norton/Total commander).
So i guess I...
|
by: Dmitri |
last post by:
I just looked at a coworker's stored procedure and this person is
dropping 4 Foreign key constraints and then re-adding them after
processing the required logic (updating rows in the 4 tables in
question).
Is there *ANY* good reason to do this? What are the performance
implications of doing this - negative or otherwise?
I was furious...
|
by: DW |
last post by:
Greetings:
I have to do a one-off forceful change of some data in a database. I
need to disable some FK constraints, make the data change, and then
re-enable the constraints.
My process will be:
ALTER TABLE TABLE1 NOCHECK CONSTRAINT FK_TABLE1_TABLE2
UPDATE TABLE1 SET COLUMN1=2 WHERE COLUMN2='Problem row'
|
by: serge |
last post by:
I am doing a little research on Google about this topic and I ran into
this thread:
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/63cce060ff152dcc/1dc13d4ee6758966?lnk=st&q=difference+constraint+sql+defaults&rnum=14#1dc13d4ee6758966
I read SQL Server MVP Louis Davidson's post saying:
"Actually...
| |
by: BobTheDatabaseBoy |
last post by:
i've Googled some this morning, but to my surprise, i
don't find any offering (for fee or open source), which would
integrate with, say Jakarta Struts, to provide the UI
edits from cataloged constraints/triggers.
there have been some threads, even recently, dealing with
with the subject of where the "true" repository of data
integrity...
|
by: Ole Nielsby |
last post by:
First, bear with my xpost. This goes to
comp.lang.c++
comp.lang.functional
with follow-up to comp.lang.c++
- I want to discuss an aspect of using C++ to implement a
functional language, and I'd like the attention of fp as well
as C++ gurus if available.
The language I'm implementing - PILS - is dynamically
|
by: Daniel Kraft |
last post by:
Hi,
I usually program in C++, but for a special project I do have to code in
C (because it may be ported to embedded-like platforms where no C++
compiler exists).
I do want to realize some kind of polymorphic behaviour, like this:
I have some abstract base "class" Base, that is, a struct containing a
vtable of function-pointers and...
|
by: Agoston Bejo |
last post by:
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.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |