Re: implement a referential integrity constraint (long)

> 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?  <

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 Received on Wed May 13 2009 - 16:24:02 CEST