CODEWITHSUNDEEP
databaseoracle-databasedatabase-designdata-modeling
Salman Ali
Salman Ali

Reputation: 255

One-One relationship constraint

i actually want to implement a compulsory one-one relationship between two tables from my ERD in Oracle. The two tables are Governor and State. A governor can govern only one state and a state must have one and only one governor. I want to implement that in Oracle. I have written the queries as follows

create table gov
(gid number(3) ,name varchar2(100),
constraint gov_pk primary key (gid)
);

create table state
(
sid number(3) ,
name varchar2(100),
gid number(3),
constraint state_pk primary key (sid),
constraint gov_state_fk foreign key (gid) references gov(gid),
constraint state_uk unique(gid,name)
);

But that does not seem to work. I could not find any alternative way. Please help me with this. I will be thankful to you. And please let me know why is is failing to establish one-one relationship.

Upvotes: 1

Views: 2404

Answers (3)

APC
APC

Reputation: 146249

You're pretty close to successfully implementing your requirements.

" a state must have one and only one governer"

So make GID mandatory on STATE table.

"A governer can govern only one state "

So enforce a unique key on just GID.

create table state
(
sid number(3) ,
name varchar2(100),
gid number(3) not null,
constraint state_pk primary key (sid),
constraint gov_state_fk foreign key (gid) references gov(gid),
constraint state_uk unique(gid)
);

"I can successfully add data to gov table with out adding any row in state table."

Enforcing a Parent must have a Child relationship is pretty hard.

  • SQL standards have the concept of Assertions, which could enforce that kind of business rule, but Oracle (nor any other DBMS vendor) have not implemented them.
  • a foreign key on GOV referencing STATE is right out, because circular dependencies are deadly.
  • that leaves us with a trigger on GOV.

Here is such a trigger:

create or replace trigger enforce_gov_state 
    before insert or update on gov
    for each row
is
    l_sid state.sid%type;
begin
    select s.sid into l_sid
    from state s
    where s.gid = :new.gid;
exception
    when no_data_found then
        raise_application_error(-20000, 'Governor must have a state');
end;
/

So that's okay then. Just one little wrinkle: how do we insert rows into either table???? We can't insert into GOV until the state exists; we can't insert into STATE until the governor exists.

There is a workaround: defer the foreign key on STATE so it's not enforced until the whole transaction is committed. This permits the creation of STATE record followed by GOV record. Of course, we need to know the value of STATE.GID before we create the GOV record.

Also, there are similar snags attached to changing the GOV - STATE relationship. Except that it can be solved by updating all the GOV attributes (except GID) to fit a new Governor. Which is kind of sketchy but there you go.

Why does Oracle make this so hard? Often a one-to-one relationship between tables which is mandatory on both sides points to a flawed data model.

  1. Sometimes 1:1 points to a single table. That is unsatisfactory when we have two distinct entities such as here.
  2. More likely the 1:1 relationship is wrong, and it's actually 1:N or even M:N. Consider that a State can have many Governors, one current, many previous and optionally one elect. Likewise a politician can theoretically be Governor of more than one state over the course of a career.

So a more truthful implementation would have STATE_GOV as an intersection table between STATE and GOV. It is much simpler to maintain such a table, which is a good sign.

Upvotes: 3

TommCatt
TommCatt

Reputation: 5636

Remove FK from state table. Having it and making it unique means you can't enter a state without already knowing the governor. Create an intersection table between State and Gov with a unique constraint on each FK:

create table StateGov(
  StateID   number( 3 ) not null references State( sid ),
  GovID     number( 3 ) not null references Gov( gid ),
  constraint UQ_StateGov_State unique StateID,
  constraint UQ_StateGov_Gov unique GovID
);

No state can appear more than once, no governor can appear more than once. No circular references, no assertions, no problem with inserting a state record before you know the governor.

Upvotes: 2

Tony Andrews
Tony Andrews

Reputation: 132610

Add a unique constraint to STATE:

create table state
(
sid number(3) ,
name varchar2(100),
gid number(3),
constraint state_pk primary key (sid),
constraint gov_state_fk foreign key (gid) references gov(gid),
constraint state_uk unique(gid,name)
constraint gov_state_uk unique (gid)
);

Upvotes: 1

Related Questions