implement a referential integrity constraint (long)

From: xyzzy <google_at_m-streeter.demon.co.uk>
Date: Thu, 7 May 2009 20:15:55 -0700 (PDT)
Message-ID: <e3a0a8d4-454c-4261-9db7-69b5596e2386_at_z23g2000prd.googlegroups.com>

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 dropdown  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. Received on Fri May 08 2009 - 05:15:55 CEST