CODEWITHSUNDEEP
databaseoracle-databasedatabase-designrelational-databasedata-modeling
WENYANG SUN
WENYANG SUN

Reputation: 1

How to check overlap constraints in Oracle?

Suppose B and C are both subclass and A is a superclass. B and C can not have same id (disjoint) 

CREATE TABLE a(id integer primary key);
CREATE TABLE b(id integer references a(id));
CREATE TABLE c(id integer references a(id));
insert into a values('1');
insert into a values('2');
insert into b values('1');
insert into c values('2');

Could I use a trigger to prevent the same id appearing in tables B and C?

Upvotes: 0

Views: 723

Answers (3)

Camille
Camille

Reputation: 2531

You can use Oracle Sequence:

CREATE SEQUENCE multi_table_seq;

INSERT INTO A VALUE(1);
INSERT INTO A VALUE(2);
INSERT INTO B VALUE(multi_table_seq.NEXTVAL());  -- Will insert 1 in table B
INSERT INTO C VALUE(multi_table_seq.NEXTVAL());  -- Will insert 2 in table C
...

With trigger:

-- Table B
CREATE TRIGGER TRG_BEFORE_INSERT_B  -- Trigger name
BEFORE INSERT                       -- When trigger is fire
ON A                                -- Table name
DECLARE
  v_id            NUMBER;
BEGIN
  v_id := multi_table_seq.NEXTVAL();
  BEGIN
     SELECT TRUE FROM C WHERE id = v_id;
     RAISE_APPLICATION_ERROR(-20010, v_id || ' already exists in table C');
  EXCEPTION WHEN NO_DATA_FOUND -- Do nothing if not found
  END;
END;

And same trigger for table C who check if id exists in table B

Upvotes: 0

APC
APC

Reputation: 146269

"b and c can not have same id"

So you want to enforce a mutually exclusive relationship. In data modelling this is called an arc. Find out more.

We can implement an arc between tables without triggers by using a type column to distinguish the sub-types like this:

create table a (
  id integer primary key
  , type varchar2(3) not null check (type in ( 'B', 'C'))
  , constraint a_uk unique (id, type)
);
create table b (
  id integer 
  , type varchar2(3) not null check (type = 'B')
  , constraint b_a_fk foreign key (id, type) references a (id, type)
);
create table b (
  id integer 
  , type varchar2(3) not null check (type = 'C')
  , constraint c_a_fk foreign key (id, type) references a (id, type)
);

The super-type table has a unique key in addition to its primary key; this provides a reference point for foreign keys on the sub-type tables. We still keep the primary key to insure uniqueness of id.

The sub-type tables have a redundant instance of the type column, redundant because it contains a fixed value. But this is necessary to reference the two columns of the compound unique key (and not the primary key, as is more usual).

This combination of keys ensures that if the super-type table has a record id=1, type='B' there can be no record in sub-type table C where id=1.

Upvotes: 1

Sandeep
Sandeep

Reputation: 806

Design wise this is not good but we can do it using the below snippet. You can create a similar trigger on table b

CREATE TABLE a(id integer primary key);
CREATE TABLE b(id integer references a(id));
CREATE TABLE c(id integer references a(id));

create or replace  trigger table_c_trigger before insert on c for each row
declare
counter number:=0;
begin
select count(*) into counter from b where id=:new.id;
if counter<>0 then
raise_application_error(-20001, 'values cant overlap between c and b');
end if;
end;

insert into a values('1');
insert into a values('2');
insert into b values('1');
insert into b values('2');
insert into c values('2');

Upvotes: 0

Related Questions