1 2 3 Previous Next 40 Replies Latest reply on Jun 19, 2015 2:20 PM by Richard Harrison .

    How to force uniqueness across 2 tables ?

    SalimWer

      Hi

       

      I have a column email1 in two tables A and B.

      and I don't want to allow to insert the email twice in both tables, the email should either exists in table A or in table B not both.

       

      Is there an easy way to implement this feature ?

       

      Best Regards

      Salim

        • 1. Re: How to force uniqness accross 2 tables ?
          Lothar Flatz

          Hi Salim,

           

          you should review your database design. Given the normalization rules of Dr. Codd it is unlikely that such situation should exist. Database normalization - Wikipedia, the free encyclopedia

          Apart from that the answer could be a trigger, but that would be curing the symptom and not the disease.

          • 2. Re: How to force uniqueness across 2 tables ?
            SalimWer

            Hi Lother

             

            Thanks for you reply, I Totally agree with you that in normalized database this case should not happen

            But sometimes in legacy systems you don't have much control over your database design.

            a trigger is one of the solutions to implement this feature but I always use triggers as a last solution,

            another method that I am researching now is using materialized views.

             

            Thanks a lot

            Salim

            • 3. Re: How to force uniqueness across 2 tables ?
              Niranjana Hulikeremath -Oracle

              Hello Salim,

               

              How about creating a view on table B and not to expose email column in that. But this means, users may not insert or update data into table B at all.

              I am not sure if that is something you are expecting. Otherwise, I think trigger is what can do this job for you.

               

              Thanks

              Niru

              • 4. Re: How to force uniqueness across 2 tables ?
                Celal Özdemir

                apart  from lothar flatz says ,

                 

                this can be done through trigger ,

                 

                it can be done through Table C .

                  - Table C will be  union All table A's email  and Table B's email .

                  - email is Primary Key of Table C 

                  - at trigger on Table A and Table B   ,this emails insert to Table C .

                 

                Table C's PK (email) will be provide uniqueness.

                • 5. Re: How to force uniqueness across 2 tables ?
                  Martin Preiss

                  the data model is indeed questionable - but you could use a materialized view to ensure the uniqueness. The MView would be defined as a UNION ALL over both tables and you would create a unique index on the email solumn.

                  • 6. Re: Re: How to force uniqueness across 2 tables ?
                    Stew Ashton

                    Martin Preiss wrote:

                     

                    ...you could use a materialized view to ensure the uniqueness. The MView would be defined as a UNION ALL over both tables and you would create a unique index on the email solumn.

                    Oracle says: "I'm sorry, Martin, I'm afraid I can't do that." The "classic" way is to create a join-based MV that should be empty, then raise an error when it is not.

                    Note to OP: the error must be raised on commit, because that is when the MV is refreshed.

                     

                    > create table t(email varchar2(64) primary key)

                    /

                    table T created.

                     

                    > create materialized view log on t with rowid

                    /

                    materialized view LOG created.

                     

                    > create table u(email varchar2(64) primary key)

                    /

                    table U created.

                     

                    > create materialized view log on u with rowid

                    /

                    materialized view LOG created.

                     

                    > create materialized view tu refresh fast on commit as

                    select rowid rid, email from t

                    union all

                    select rowid rid, email from u

                    /

                    Error starting at line : 1 in command -

                    create materialized view tu refresh fast on commit as

                    select rowid rid, email from t

                    union all

                    select rowid rid, email from u

                    Error at Command Line : 15 Column : 30

                    Error report -

                    SQL Error: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

                    12054. 00000 -  "cannot set the ON COMMIT refresh attribute for the materialized view"

                    *Cause:    The materialized view did not satisfy conditions for refresh at

                              commit time.

                    *Action:  Specify only valid options.

                     

                    > create materialized view tu refresh fast on commit as

                    select t.rowid trid, u.rowid urid

                    from t join u using(email)

                    /

                    materialized view TU created.

                     

                    > alter materialized view tu add constraint same_email_in_t_and_u_not check(1=0) DEFERRABLE

                    /

                    materialized view TU altered.

                     

                    > insert into t values('me@email.com')

                    /

                    1 rows inserted.

                     

                    > insert into u values('me@email.com')

                    /

                    1 rows inserted.

                     

                    > commit

                    /

                    Error starting at line : 24 in command -

                    commit

                    Error report -

                    SQL Error: ORA-12008: error in materialized view refresh path

                    ORA-02290: check constraint (STEW.SAME_EMAIL_IN_T_AND_U_NOT) violated

                    12008. 00000 -  "error in materialized view refresh path"

                    *Cause:    Table SNAP$_<mview_name> reads rows from the view

                              MVIEW$_<mview_name>, which is a view on the master table

                              (the master may be at a remote site).  Any

                              error in this path will cause this error at refresh time.

                              For fast refreshes, the table <master_owner>.MLOG$_<master>

                              is also referenced.

                    *Action:  Examine the other messages on the stack to find the problem.

                              See if the objects SNAP$_<mview_name>, MVIEW$_<mview_name>,

                              <mowner>.<master>@<dblink>, <mowner>.MLOG$_<master>@<dblink>

                              still exist.

                    • 7. Re: Re: How to force uniqueness across 2 tables ?
                      Stew Ashton

                      Celal Özdemir wrote:

                       

                      this can be done through trigger ,

                       

                      it can be done through Table C .

                        - email is Primary Key of Table C 

                        - at trigger on Table A and Table B   ,this emails insert to Table C .

                       

                      Table C's PK (email) will be provide uniqueness.

                      Triggers are almost always very hard to do correctly.

                      At first glance, this one seems OK. Table C just needs the email, nothing else. It can also be an Index Organized Table (IOT), so there is just the index segment to manage.

                      One advantage to this solution is that the constraint can be either immediate or deferrable, depending on what the OP wants. The MV solution can only be deferrable.

                      • 8. Re: Re: Re: How to force uniqueness across 2 tables ?
                        Karthick2003

                        Something like this I guess

                         

                        SQL> create table a (id integer, email varchar2(100));
                        
                        Table created.
                        
                        SQL> alter table a add constraint a_uk unique(email);
                        
                        Table altered.
                        
                        SQL> create table b (id integer, email varchar2(100));
                        
                        Table created.
                        
                        SQL> alter table b add constraint b_uk unique(email);
                        
                        Table altered.
                        
                        SQL> create materialized view log on a with rowid including new values;
                        
                        Materialized view log created.
                        
                        SQL> create materialized view log on b with rowid including new values;
                        
                        Materialized view log created.
                        
                        SQL> alter materialized view log on b add (email);
                        
                        Materialized view log altered.
                        
                        SQL> alter materialized view log on a add (email);
                        
                        Materialized view log altered.
                        
                        SQL> create materialized view mv build deferred refresh fast on commit
                          2  as
                          3  select count(*) cnt
                          4    from a join b
                          5      on a.email = b.email;
                        
                        Materialized view created.
                        
                        SQL> alter materialized view mv add constraint mv_chk check (cnt = 0);
                        
                        Materialized view altered.
                        
                        SQL> exec dbms_mview.refresh('MV','C')
                        
                        PL/SQL procedure successfully completed.
                        
                        SQL> insert into a values (1, 'a@yahoo.com');
                        
                        1 row created.
                        
                        SQL> insert into b values (1, 'a@yahoo.com');
                        
                        1 row created.
                        
                        SQL> commit;
                        commit
                        *
                        ERROR at line 1:
                        ORA-12008: error in materialized view refresh path
                        ORA-02290: check constraint (V195588.MV_CHK) violated
                        
                        SQL> rollback;
                        
                        Rollback complete.
                        
                        SQL> insert into a values (1, 'a@yahoo.com');
                        
                        1 row created.
                        
                        SQL> insert into b values (1, 'b@yahoo.com');
                        
                        1 row created.
                        
                        SQL> commit;
                        
                        Commit complete.
                        
                        SQL> select * from a;
                        
                                ID
                        ----------
                        EMAIL
                        ------------------------------------------------------
                                 1
                        a@yahoo.com
                        
                        SQL> select * from b;
                        
                                ID
                        ----------
                        EMAIL
                        ------------------------------------------------------
                                 1
                        b@yahoo.com
                        
                        SQL> select * from mv;
                        
                               CNT
                        ----------
                                 0
                        
                        SQL>
                        
                        
                        • 9. Re: How to force uniqueness across 2 tables ?
                          Martin Preiss

                          Stew,

                          yes, that sounds and looks convincing...

                          Maybe I could have checked it before suggesting: but I was sure that the solution contained MViews...

                          • 10. Re: How to force uniqueness across 2 tables ?
                            Jarkko Turpeinen

                            Hi

                             

                            I have a column email1 in two tables A and B.

                            and I don't want to allow to insert the email twice in both tables, the email should either exists in table A or in table B not both.

                             

                            Is there an easy way to implement this feature ?

                            no there is not. Why do you care if your data model allows that? Change data model or stop caring .

                            • 11. Re: How to force uniqueness across 2 tables ?
                              Lothar Flatz

                              Hi Salim,

                               

                              I think a MVIEW would be overkill. You need all the mview logs and the synchronizing just for one check.

                              I think Codd would suggest a regular view because that was his solution for logical transformations. Well, still that does not give me a solution.

                              Would it be possible do define one tabla as the only source of the email column? Let's say table A form the sake of the argument. Than the column in table B could be ignored.

                              1 person found this helpful
                              • 12. Re: How to force uniqueness across 2 tables ?
                                SalimWer

                                Hi Lothar

                                 

                                Yes I think I should consider one of the tables as source table , or allow some duplication in this stage and handle it in later stage.

                                 

                                Regards

                                Salim

                                • 13. Re: How to force uniqueness across 2 tables ?
                                  Lothar Flatz

                                  If you have something like a staging area that could be a valid approach.

                                  • 14. Re: Re: How to force uniqueness across 2 tables ?
                                    Chris Saxon-Oracle

                                    Building on Celal's solution, it is possible to do this declaratively without needing a materialized view. The basic approach is:

                                     

                                    - Create a third table as a master list of email addresses. Also have a discriminator/type column on this table.

                                    - The primary key of this table is email_address. Also create a unique key over (email_address, type).

                                    - Add type columns to tables A and B.

                                    - Add check constraints to validate that these columns can only be type A or B respectively

                                    - Add foreign keys on A & B back to the master on (email_address, type).

                                    - Done!

                                    - You can now only insert rows into A or B that correspond to the type given in the master table.

                                     

                                    create table email_master (
                                      email_address varchar2(320) not null,
                                      email_type varchar2(10) not null 
                                        check (email_type in ('TABLEA', 'TABLEB')),
                                      constraint email_master_pk primary key (email_address),
                                      constraint email_master_u unique (email_address, email_type)
                                    );
                                    
                                    create table tablea (
                                      email_address varchar2(320) not null primary key
                                        references email_master (email_address),
                                      email_type varchar2(10) not null 
                                        check (email_type = 'TABLEA'),
                                      stuff varchar2(100),
                                      constraint taba_email_fk foreign key (
                                        email_address, email_type
                                      ) references email_master (email_address, email_type)
                                    );
                                    
                                    create table tableb (
                                      email_address varchar2(320) not null primary key
                                        references email_master (email_address),
                                      email_type varchar2(10) not null 
                                        check (email_type = 'TABLEB'),
                                      stuff varchar2(100),
                                      constraint tabb_email_fk foreign key (
                                        email_address, email_type
                                      ) references email_master (email_address, email_type)
                                    );
                                    
                                    insert into email_master values ('testa@test.com', 'TABLEA');
                                    insert into email_master values ('testb@test.com', 'TABLEB');
                                    insert into email_master values ('testc@test.com', 'TABLEA');
                                    
                                    insert into tablea values ('testa@test.com', 'TABLEA', 'stuff');
                                    insert into tableb values ('testb@test.com', 'TABLEB', 'stuff');
                                    insert into tablea values ('testc@test.com', 'TABLEA', 'stuff');
                                    
                                    
                                    insert into tableb values ('testc@test.com', 'TABLEA', 'stuff');
                                    ORA-02290: check constraint (USER_4_368F6.SYS_C00817948) violated
                                    insert into tableb values ('testc@test.com', 'TABLEB', 'stuff');
                                    ORA-02291: integrity constraint (USER_4_39CAFF.TABB_EMAIL_FK) violated - parent key not found
                                    

                                     

                                     

                                    If you're not able to modify the application code (quickly), you may need to add insert/update triggers on A & B to populate the master table.

                                     

                                    I'm not sure which normal form distributed uniqueness violates - it's compatible up to 5NF as far as I can see. Hugh Darwen proposes something similar to handle missing information (i.e. get rid of nulls): http://www.dcs.warwick.ac.uk/~hugh/TTM/Missing-info-without-nulls.pdf

                                    1 2 3 Previous Next