Q
Manage Learn to apply best practices and optimize your operations.

Establishing referential integrity

In the Oracle database, I have a table called "Agent," which has a column object called "Address." Columns in Agent...

are:

Agent_ID number(3,0)
Agent_Name  varchar(30)
Address Address
Columns in Address object are:
 
street 	varchar(30)
zip 	varchar(30)
city 	varchar(30)
state	varchar(30)
Now I have a given set of state, city and zip. Each zip has to be from a particular city, and each city has to be from a particular state.

How do I establishe the referential integrity? How do I use REFs or scoped REFs to solve this? I want to be able to add city, state and zip dynamically at run time.


I pulled the following two paragraphs from Oracle 8i documentation:

Unfortunately, unlike referential integrity constraints, scoped REFs do not ensure that the referenced row objects exist; they only ensure that the referenced object tables exist. Therefore, if you specify a scoped REF to a row object and then delete the row object, the scoped REF becomes a dangling REF because the referenced object no longer exists.

In general, you should use referential integrity constraints wherever possible because they are the only way to ensure that the row object for the REF exists.
With that in mind, I might implement a solution this way. Assuming your statement is correct, that every zip resolves to one and only one city, then I might first declare an object for storing the Zip/City/State relationship like this:
create type ZipLocation_obj as object
( Zip varchar2(30),
  City varchar2(30),
  State varchar2(30) );  
Then I might create an object table for ZipLocations like this:
create table ZipLocation_objtab of ZipLocation_obj 
( primary key (Zip)
) object identifier is primary key;
Then I might declare an object for addresses, like this (note the object reference for the ZipLocation column):
create type Address_obj as object 
( Street varchar2(30),
  ZipLocation ref ZipLocation_obj
);  
When I create my Agent table, I include a foreign key referencing the ZipLocation object table from the ZipLocation attribute of the Address. Here's the SQL:
create table Agent 
( Agent_ID number(3) primary key,
  Agent_Name varchar(30),
  Address Address_obj,
  foreign key (Address.ZipLocation) 
    references ZipLocation_objtab
);

For More Information

  • What do you think about this answer? E-mail the edtiors at editor@searchDatabase.com with your feedback.
  • The Best SQL Web Links: tips, tutorials, scripts, and more.
  • Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

This was last published in March 2002

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close