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 Address
street varchar(30) zip varchar(30) city varchar(30) state varchar(30)
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.
create type ZipLocation_obj as object ( Zip varchar2(30), City varchar2(30), State varchar2(30) );
create table ZipLocation_objtab of ZipLocation_obj ( primary key (Zip) ) object identifier is primary key;
create type Address_obj as object ( Street varchar2(30), ZipLocation ref ZipLocation_obj );
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.