Solved

How to enforce one-to-one relationship in Oracle

Posted on 2004-09-16
6
2,638 Views
1 Endorsement
Last Modified: 2008-03-03
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
Comment
Question by:macksfo7725
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12080710
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
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12080725
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
 

Author Comment

by:macksfo7725
ID: 12114587
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Accepted Solution

by:
jdlambert1 earned 100 total points
ID: 12114708
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
 

Author Comment

by:macksfo7725
ID: 12138562
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
 

Author Comment

by:macksfo7725
ID: 12172704
Just wanting to get verification on the approach i have outlined in my previous note - any help is greatly appreciated.
0

Featured Post

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Email question 19 92
Wordpress -  Updating All Posts on Website 3 93
Question about Common Table Expressions 3 45
Batch convert csv to xlsx 10 60
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question