Re: Implementing complicated constraints

"Tony Andrews" <andrewst_at_onetel.com> wrote in message news:<1096384303.973082.269160_at_k17g2000odb.googlegroups.com>...
> Mark D Powell wrote:
> > 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.
>
> I don't agree with that. It is just a constraint of a complexity that
> Oracle cannot handle via constraints. If Oracle implemented the ANSI
> SQL assertion syntax then the constraint could be implemented without
> having to have the CID denormalised into the A table.
>
> Of course, having the CID in the A table is a viable work-around, but
> that doesn't mean there was a relational design flaw before.

Tony, while based on the limited information in the original post it is remotely possible the tables are normalized the requirement for a column in table A to be unique in relation to a column in another table usually results from failure to properly normalize the data. The last time I ran into this I pointed this out to the developer and he told me the customer did not want to maintain three tables. I countered that the customer does not maintain the tables, the application does, and it presents the data as a single screen. The fact that the screen was build from one, two, or three tables does not matter to the customer, but it does matter to proper design. Unfortunately it was too late for buget reasons to change the design so the developer changed the FORM to update via a procedure I wrote for the developer which handles all the logic. When all updates come from one source I find the procedure method is quicker and easier to implement than the instead of trigger method I mentioned. The trigger method is a better solution when multiple update sources exist for the target. The multiple trigger mether is a pain that is only applicable in certain cases so I do not use it.

IMHO -- Mark D Powell -- Received on Thu Sep 30 2004 - 09:27:29 CDT