Solved

How to enforce one-to-one relationship in Oracle

Posted on 2004-09-16
6
2,365 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Database tuning – How to start and what to tune. This question is frequently asked by many people, both online and offline. There is no hard and fast rule-of-thumb for performance tuning, however, before beginning the tuning process one should a…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
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…

910 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

20 Experts available now in Live!

Get 1:1 Help Now