In the Oracle database, I have a table called "Agent," which has a column object called "Address." Columns in Agent...
Agent_ID number(3,0) Agent_Name varchar(30) Address AddressColumns 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.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:
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.
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.
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.