Solved

How to enforce one-to-one relationship in Oracle

Posted on 2004-09-16
6
2,316 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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction: I have seen many questions on EE and elsewhere, asking about how to find either gaps in lists of numbers (id field, usually) ranges of values or dates overlapping date ranges combined date ranges I thought it would be a good …
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
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…

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now