How is unique constraint applied from more than one table in oracle?

Question

I have 3 tables is shown this tables:
table A

table B

B(
    code2
    .
    .
    .
)

table C

C(
    code3
    .
    .
    .
)

I want to be unique between code1,code2,code3 of this tables.
How do i recieve to my goal in oracle?
Does any syntax of oracle exist about this problem?

Unique constraints only apply to one table. Although you can do this with triggers, I would question your database design, and suggest alternatives that don't have this issue. — Gordon Linoff, Nov 19 '16 at 12:25
What is your idea about oracle's View from this tables and apply this constraint to it?do it possible? — reza ramezani matin, Nov 19 '16 at 12:39
look at http://stackoverflow.com/questions/4668146/how-to-add-a-primary-key-on-a-oracle-view. — Turo, Nov 19 '16 at 12:54

score 1 · Answer 1 · answered Nov 19 '16 at 14:41

1

Based on materialized view.

P.s.
The code is not validated.
I currently have access only to oracle XE 11gR2, so I'm not able to use the materialized view log feature.

create table A   (code int primary key);
create table B   (code int primary key);
create table C   (code int primary key);

create materialized view log on a with primary key;
create materialized view log on b with primary key;
create materialized view log on c with primary key;

create materialized view ABC_MV
refresh fast
as
          select code from A
union all select code from B
union all select code from C
;

alter table ABC_MV add unique (code);

answered Nov 19 '16 at 14:41

David דודו Markovitz

42,900
6
64
88

This is a clever solution. Do you know if it actually works in practice? Materialized views have different refresh options. It would seem that the constraint (if allowed) would break the materialized view but still allow the data to go into the underlying tables. – Gordon Linoff Nov 19 '16 at 14:54
@GordonLinoff, clever solution, but not mine. I saw it few weeks ago on SO but since it was a different use case involving a single table, I answered and didn't flag it as duplicate. The refresh on demand indeed causing the MV to break, but this is not surprising. I assume that refresh on commit will do the job but as I said I currently can't test it. – David דודו Markovitz Nov 19 '16 at 16:07

score 0 · Answer 2 · answered Nov 19 '16 at 12:28

0

You can do that with trigger.

CREATE OR REPLACE TRIGGER table1_check
BEFORE INSERT OR UPDATE ON table1
FOR EACH ROW
BEGIN
    IF EXISTS (
        SELECT attribute FROM table2
        WHERE attribute = :NEW.attribute
    ) THEN
        RAISE_APPLICATION_ERROR(-20001,
        'Already exist in table2');
    END IF;
END;
/

You can modify it to check in more tables as well.

answered Nov 19 '16 at 12:28

quantummind

2,086
1
14
20

I know to add a trigger but is not there a solution without trigger? – reza ramezani matin Nov 19 '16 at 12:33
The best solution is to use trigger. There is no other good way to do that. Of course you can redesign, maybe you don't need 3 tables. – quantummind Nov 19 '16 at 13:13

David דודו Markovitz · Answer 3 · 2016-11-19T13:37:46.597

Using a 4th table

create table ABC (tab char(1) not null ,code int primary key, unique (tab,code));

create table A   (tab char(1) as ('A') virtual not null,code int primary key,foreign key (tab,code) references ABC(tab,code));
create table B   (tab char(1) as ('B') virtual not null,code int primary key,foreign key (tab,code) references ABC(tab,code));
create table C   (tab char(1) as ('C') virtual not null,code int primary key,foreign key (tab,code) references ABC(tab,code));

insert into ABC (tab,code) values ('A',1);
insert into A (code) values (1);
insert into A (code) values (1);

[Code: 1, SQL State: 23000] ORA-00001: unique constraint (SYS.SYS_C0012834) violated

insert into B (code) values (1);

[Code: 2291, SQL State: 23000] ORA-02291: integrity constraint (SYS.SYS_C0012839) violated - parent key not found

Thanks for your answer but this solution force me when a record is inserted to any of A or B or C,it is inserted to 4th table too — reza ramezani matin, Nov 19 '16 at 13:06

How is unique constraint applied from more than one table in oracle?

3 Answers3