I have a table t1 that has many the following records
ID NAME TITLE ....
1 abc X1
2 xyz X1
3 qwe X1
4 asd X2
5 pqr X2
I want to create a new table t2 that has a primary key and it references a new foreign key in t1. Also, the information of the column "title" is distinct in t2 and hence ,any records added in t2 should be reflected in t1 and the deletes should be cascaded from t1 to t2.
I have done the following :
create table t2 AS SELECT distinct (title) FROM t1;
ALTER TABLE t2
ADD (s_id,column1,column 2);
alter table t2 add primary key (s_id);
create sequence s_seq
start with 1
increment by 1
nomaxvalue;
update t2 set s_id=s_seq.nextval;
ALTER TABLE t1
add constraint fk_s_id
FOREIGN KEY (f_t2)
REFERENCES t2(s_id) on delete cascade;
This doesnt work on the cascades and inserts. PLease help.
I was missing how you create f_t2 which needs to be added to t1 at one point. So I added and populated the column f_t2 and it worked.
ALTER TABLE t1
ADD (f_t2 number);
update t1 set f_t2 = (select s_id from t2 where t2.title = t1.title);
Running the following produced the desired result.
drop table t1;
create table t1 (id number,name varchar2(100), title varchar2(100));
insert into t1 values (1, 'abc', 'X1');
insert into t1 values (2, 'xyz', 'X1');
insert into t1 values (3, 'qwe', 'X1');
insert into t1 values (4, 'asd', 'X2');
insert into t1 values (5, 'pqr', 'X2');
drop table t2;
create table t2 AS SELECT distinct (title) FROM t1;
ALTER TABLE t2
ADD (s_id number,column1 varchar2(10),column2 varchar2(20));
drop sequence s_seq;
create sequence s_seq
start with 1
increment by 1
nomaxvalue;
update t2 set s_id=s_seq.nextval;
alter table t2 add primary key (s_id);
ALTER TABLE t1
ADD (f_t2 number);
update t1 set f_t2 = (select s_id from t2 where t2.title = t1.title);
select * from t1;
select * from t2;
ALTER TABLE t1
add constraint fk_s_id
FOREIGN KEY (f_t2)
REFERENCES t2(s_id) on delete cascade;
delete from t2 where title='X2';
select * from t1;
select * from t2;
Output:
table T1 dropped.
table T1 created.
1 rows inserted.
1 rows inserted.
1 rows inserted.
1 rows inserted.
1 rows inserted.
table T2 dropped.
table T2 created.
table T2 altered.
sequence S_SEQ dropped.
sequence S_SEQ created.
2 rows updated.
table T2 altered.
table T1 altered.
5 rows updated.
ID NAME TITLE F_T2
---------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ----------
1 abc X1 1
2 xyz X1 1
3 qwe X1 1
4 asd X2 2
5 pqr X2 2
TITLE S_ID COLUMN1 COLUMN2
---------------------------------------------------------------------------------------------------- ---------- ---------- --------------------
X1 1
X2 2
table T1 altered.
1 rows deleted. --- DELETE in T2 affects T1
-- THIS IS T1:
ID NAME TITLE F_T2
---------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ----------
1 abc X1 1
2 xyz
X1 1
3 qwe X1 1
-- THIS IS T2:
TITLE S_ID COLUMN1 COLUMN2
---------------------------------------------------------------------------------------------------- ---------- ---------- --------------------
X1 1