Hi,

I would like to implement a referential integrity constraint. Sounds
like a good idea, doesn't it?

The Oracle system I am working on has 11 lines reserved for address
information and they are all VARCHAR2. Upon investigation, the address
turns out to be user configurable: there is a table called addr_type
that defines, for each type of address, a prompt, whether it is a
mandatory field etc. for every address line.
For example (this may wrap badly, poss copy&paste to fixed-width font
in a line editor!):
ADDR_TYPE DESCRIPTION LINE1_LABEL,
OPT_IND LINE2_LABEL, OPT_IND LINE3_LABEL, OPT_IND LINE4_LABEL,
OPT_IND etc.
--------- ------------------------------------------
------------------------ -------------------- --------------------
--------------------
OS-POSTAL OVERSEAS POSTAL ADDRESS ( CORRESPONDENCE ) C/O, Number &
Street, Y, Suburb/Town, N, State/Region, N,
Country
POSTAL POSTAL ADDRESS ( CORRESPONDENCE ) Number &
Street, Y, Suburb/Town, Y, State, Y, ,
N

There are many address types in the system. contact details beyond
line 6 are used for phone numbers, email addresses etc. Currently
there is no provision for things like web, skype etc. but there could
be, because it's user configurable. If a licence to use the system is
bought, and the system is deployed in a particular geographic area,
the users there will configure the address format.

This isn't the way I would have done it, but I can see the
advantages. In particular, if I was doing it, I would have
incorporated a country_id for every address. So that's what I've got
to work with.

I am working on a web app, running in its own schema, which will
interface with the above. In other words I will need to have a table:
CONTACTS
--------
Contact_id NUMBER
Addr_type VARCHAR2 (foreign Key: addr_type.addr_type)
Line1 VARCHAR2
Line2 VARCHAR2
Line3 VARCHAR2
Line4 VARCHAR2
etc.

OK. What I want to do is to make sure the country, which is
configured to appear in line 4 for addr_type='OS-POSTAL', is
restricted to a list of countries in the world.

I can do this in the application by restricting the input to a drop-
down list, but I'd like to put a constraint in the database, where it
belongs.

One idea is to create a trigger, which fires on insert to contacts,
that selects the country for any column where the address is labeled
'Country' and raises an exception if it's not found.

Can anyone suggest a better way?

Thanks.

That is being kind

Try the "CHECK()-from-hell approach"

As opposed to those countries that are somewhere else?

CONSTRAINT GenralAddressValidation
CHECK( CASE county_code
WHEN 'xx'
THEN (CASE WHEN line1 NOT LIKE ..THEN 'F'
WHEN line2 NOT LIKE ..THEN 'F'
..END)
WHEN 'yy'
THEN (CASE WHEN line1 NOT LIKE ..THEN 'F'
WHEN line NOT LIKE ..THEN 'F'
..END)
.. ELSE 'T' END = 'T')

This is a pain to write, but the idea is to give each contry its own
second level CASE expression.

1) Do a web search about "Normal Forms" and get a book about database
design
2) Look up standards. Postal codes have standards. There are ISO
country and location codes. A database can enforce them by DRI and
CHECK constraints.
3) Do not confuse the front end (reporting / presentation layer) with
the backend (database). How you print addresses on paper, based on
local needs is a matter of the reporting tools and not the database.

Sorry, but there are fundamental problems with your design.

brgds

Philipp Post

On May 8, 11:01*pm, Philipp Post <Post.Phil... (AT) googlemail (DOT) com> wrote:
Damn right! We all know the design is imperfect, Philipp.
Unfortunately the large, old system cannot be re-written
(specifically, someone tried before and we know it will cost more than
AU$50M (1997?) to do that, and a larger budget is not available). I
am not in Utopia, where if you don't like your program/data structure
you can simply throw it away an do it again. As I've said, I wouldn't
do it that way; it's not my design, or my system.

Love it or hate it, the information is stored in that structure. My
question is, GIVEN the existing structure, what is the best way to put
some referential integrity in? Walking away is not an option, so I
want to make the best job of it instead.

On May 8, 10:51*pm, --CELKO-- <jcelko... (AT) earthlink (DOT) net> wrote:
First, I got to say this...
Asia, Europe and the Indian sub-continent! :-p
I suppose countries in the world == countries in the universe. That's
an aside.

Second, the check from hell sound interesting, and I have no doubt
that's the way CJ Date would've done it!
At first I thought you were joking! I might have to use something a
bit more sophisticated than a case statement though.
NB: I don't have country code either.

How about:
CONSTRAINT GeneralAddressValidation CHECK( f( line1, line1_label ) =
true AND f( line2, line2_label ) = true AND ... )

where f( v_line, v_label ) is
if v_label = 'Country' then
return( v_line1 in (country_list) )
else
return( true )
end if;

That would be equivalent to what you've put. No doubt that'll get a
few comments from the forum.

You will have to forget FOREIGN KEYS with such a heavy srew-up.

You could play around with CHECK constraints like this

CHECK((line1_type = 'Country' AND line1_content IN(< list of 248
countries in the world>) OR (line1_type LIKE '%' AND line1_content
LIKE '%'))

One CHECK constraint for each column pair that may hold a country
name. What if someone enters the country name in an other language
than your default one? Uh! This all is anything but pretty and I even
do not know if Oracle will process that properly. At least SQL Server
has issues with brackets in CHECK constraints and with the IN
predicate - things get mixed up by the system.

An other way might be AFTER INSERT, AFTER UPDATE triggers, where you
could certainly get better help in an Oracle group then.

brgds

Philipp Post

"xyzzy" <google (AT) m-streeter (DOT) demon.co.uk> wrote


question is, GIVEN the existing structure, what is the best way to put
some referential integrity in? Walking away is not an option, so I
want to make the best job of it instead.

<<

I hate to suggest this, but can you implement referential integrity
constraints in the application(s) that update(s) the database? I suspect
that if the database is as locked in as you say, then the application is
locked in as well. But maybe not.

The reason I hate to make such a suggestion is that in years gone by, there
have been any number of programmers who have visited c.d.t. and from time to
time they have offered variations on the same programming philosophy.
Roughly stated that philosophy comes down to treating the DBMS and the
database as a stupid storage and retrieval engine, and doing anything
requiring business rules knowledge in the application and not in the
database. It's all that the regulars in here can do to explain, over and
over again, why the data integrity features of a DBMS can be a good thing,
and preferable, in many instances to relying on error free application code.
This suggestion of mine is likely to set that debate back by years.

However, when I recommend this to you, xyzzy, I think you will treat my
suggestion with the caveat it deserves. BTW, I'm sorry you're stuck in a
database that resembles a bunch of twisty little passages, all different.

"Walter Mitty" <wamitty (AT) verizon (DOT) net> wrote:

[snip]

Aren't they all the same? Over and over again.

It is hardly that it has never been seen before.

For those who have led a sheltered life:

Announcing a new beginning for Entity Attribute Value DBMSs: EVE.
Do not put your constraint handling under one roof when you can apply
it all over on the eaves. Applications are for applying. ... Excuse
me. I am getting ill. You should, too.

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

Gene Wirchenko wrote:

The message didn't have much to do with theory, so I wouldn't blame
anybody for giving a pragmatic suggestion. If it really is an app
that's meant to be sold, there might be other ways to go at it, such as
automating the configuration set. That's been done for a long time with
lots of products but it seems that many db app producers don't realize
they could do it too. I've even seen miniature compilers used once or
twice. The fact is, most apps could be compiled, including many
'tables' and this doesn't subvert relational theory.


Of course the real theory problem is that people who design products
like Oracle usually wouldn't recognize a constraint theory from a hole
in the ground. Hard to blame the small-fry for that but surely Oracle
has the dough to spend a few million on a foundation that would go
beyond the miniscule efforts they and similar products show - keys,
foreign keys and slough the rest off on some half-assed scripting
language. Even Date gets mixed up by constraints, suggesting that you
can infer results from them whereas you can only infer possible values.


Regarding putting constraints in the dbms, somewhere Date made the
incisive comment that nearly everybody got it wrong in the 1960's and
1970's, thinking that logic had to come out of programs when obviously
(for other reasons besides constraints), it has to be in the 'front-end'
too. The so-called AJAX people persist even today in missing this boat.

On May 14, 7:18*am, Gene Wirchenko <ge... (AT) ocis (DOT) net> wrote:
You are right. I was troubled by the method of storing the data but I
couldn't quite put my finger on it. When I saw those three letters
EVA it sent a shiver down my spine - really! And my immediate reaction
was denial too! OK any newbie that wants to know what the fundamental
problem is, look up Entity-Attribute-Value model both in this thread
and in Wikipedia. Lets focus on ways out rather than just how bad EAV
is.

The table Addr_type (ahh... the word 'type') was supposed to store the
prompt to be displayed on the screen for that row of the address. My
original problem was no validation on user input for those fields.
The result? address line 4 is always a country but only if it's an
overseas address. Address line 3 is always the state, but only if
it's a domestic address. And on and on. What a disaster. I bet the
guy that thought of it was pleased with himself. They always are
aren't they. All I can say is thank god it's only a few lines in the
address and not the whole system.

Perhaps I can create a view based on person_address and addr_type.
Hmmm...

Also search all_tables for %_type.