mmuzahid mmuzahid - 7 months ago 11
SQL Question

Column value must exists in non primary column of another table

Suppose I have two table:

TABLE_1
ID AGE_T1 NAME
1 5 A
2 23 B
3 5 C
4 9 D

TABLE_2
AGE_T2 FREQUENCY
5 2
9 1
23 1


How can I ensure that
Age_t2
value must be one of the value of
Age_t1
(non-unique column of Table1). Need to mention that both
TABLE_1
and
TABLE_2
are physical table(not any logical structure as VIEW)

Note: If
Age_t1
were a Primary Key then Foreign Key Constraints was enough for
Age_t2
. I also have a plan for
INSERT TRIGGER
if there is no better solution.

MT0 MT0
Answer

Create a materialized view to contain only the distinct ages in TABLE_1:

CREATE MATERIALIZED VIEW LOG ON TABLE_1
   WITH SEQUENCE, ROWID(AGE_T1)
   INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW TABLE_1_MV
   BUILD IMMEDIATE
   REFRESH FAST ON COMMIT
   AS SELECT DISTINCT AGE_T1
      FROM   TABLE_1;

ALTER TABLE TABLE_1_MV ADD CONSTRAINT t1_mv__age_t1__pk PRIMARY KEY ( AGE_T1 );

Then you can add a FOREIGN KEY on TABLE_2 referencing this as the primary key:

ALTER TABLE TABLE_2 ADD CONSTRAINT t2__age__fk FOREIGN KEY ( AGE_T2 )
  REFERENCES TABLE_1_MV ( AGE_T1 );
Comments