To implement the objects and the ref constraints to the existing tables, what we can do? Simply drop the both tables and recreate with objects and ref constrains.
But you can achieve this with out dropping the tables and without losing the data by creating object views with references.
Ex:
a) Create the following tables
SQL> Create table student1(no number(2) primary key,name varchar(2),marks
number(3));
SQL> Create table student2(no number(2) primary key,hno number(3),city varchar(10),id
number(2),foreign Key(id) references student1(no));
b) Insert the records into both tables
SQL> insert into student1(1,’a’,100);
SQL> insert into student1(2,’b’,200);
SQL> insert into student2(11,111,’hyd’,1);
SQL> insert into student2(12,222,’bang’,2);
SQL> insert into student2(13,333,’bombay ’,1);
c) Create the type
SQL> create or replace type stud as object(no number(2),name varchar(2),marks
number(3));/
d) Generating OIDs
SQL> Create or replace view student1_ov of stud with object identifier(or id) (no) as
Select * from Student1;
e) Generating references
SQL> Create or replace view student2_ov as select no,hno,city,make_ref(student1_ov,id)
id from Student2;
d) Query the following
SQL> select *from student1_ov;
SQL> select ref(s) from student1_ov s;
SQL> select values(s) from student1_ov;
SQ> select *from student2_ov;
SQL> select deref(s.id) from student2_ov s;
No comments:
Post a Comment