Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3103
  • Last Modified:

How to enforce one-to-one relationship in Oracle

When using a data modeling tool like ERWin I can indicate a one-to-one relationship, but is there a way I can specify (in terms of SQL statement), that through a database constraint (referential integrity etc).

I understand that one-to-one usually ends up being just one table, but the scenario I've does warrant a separate table.

Any responses will be greatly appreciated. Thanks in advance.
1
macksfo7725
Asked:
macksfo7725
  • 3
  • 3
1 Solution
 
jdlambert1Commented:
If TableA has a primary key named "aID" and you want to create a foreign key on TableB which has a column named "aID":

ALTER TABLE TableB ADD CONSTRAINT TableBfk
    FOREIGN KEY (aID) REFERENCES TableA(aID)
    INITIALLY DEFERRED DEFERRABLE;
0
 
jdlambert1Commented:
To define a foreign key at the time of table creation:

CREATE TABLE TableB (<column definitions> FOREIGN KEY(<column name(s)>) references (<table/column name(s)>));
0
 
macksfo7725Author Commented:
I'm still not very clear, on how to have a enforce one-to-one relationship between 2 tables..
Bottomline - How can I ensure that only1 instance from each of these tables are related??

Pl further clarify
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
jdlambert1Commented:
Sorry, I left off half the solution. You also need a unique constraint, which you can create by defining the primary key.

Say your "main" table is called TableA and it's primary key is called "ID". Create your second table, say TableB, also with a primary key named "ID". Now define TableB(ID) as a foreign key to TableA(A).

Having TableB(ID) as a foreign key means it can only have a value if it exists in TableA(ID), and having it be the primary key means that it can't have a value more than once.

I should have also mentioned that ERWin is capable of forward-engineering, where it takes the changes you make in the data model and changes the database schema to match it -- creating tables, adding/dropping columns & constraints, etc.
0
 
macksfo7725Author Commented:
Thanks so much for replying and sorry I was away for vacation hence the delay in comment.

So in ERWin here's what I did -

For TABLE 'A' - I defined the primary key say 'id'

Then separately For TABLE 'B' - I defined the primary key as 'id' as well, with same datatype as that of TABLE 'A'

Then I used the relationship line (1-many as there isn't any one-to-one), and afterwards went in and made it a 'one-to-one' thru the radio button for that option..

So effectively, I've 'id' as primary key of both tables and the ERWin generated(with PK and FK defined after table creation) -


ALTER TABLE A
       ADD  ( PRIMARY KEY (id) ) ;

ALTER TABLE B
       ADD  ( PRIMARY KEY (id) ) ;

ALTER TABLE B
       ADD  ( FOREIGN KEY (id)
                             REFERENCES A) ;

Does this look correct? - Your thoughts and thanks again
0
 
macksfo7725Author Commented:
Just wanting to get verification on the approach i have outlined in my previous note - any help is greatly appreciated.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now