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
Solved

How to enforce one-to-one relationship in Oracle

Posted on 2004-09-16
6
2,477 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
  • 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

In today’s complex data management environments, it is not unusual for UNIX servers to be dedicated to a particular department, purpose, or database.  As a result, a SAS® data analyst often works with multiple servers, each with its own data storage…
I guess that all of us know that caching the data usually increase the performance, but I worried if all of us are aware about the risk that caching the data provides and how to minimize this.  That’s the reason why I decided to write this short art…
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…

792 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