Re: One to One Relationship

From: Peter <no_email_at_no_email.com>
Date: Thu, 20 Mar 2003 10:11:47 +1000
Message-ID: <b5b11q$8c$1@bunyip.cc.uq.edu.au>

Hmmn, seems to be a misunderstanding between us.

Here is what the original posted question was:

> How can a one to one relationship between two tables be created in Oracle
> 9i?

And this is what you said:

By ignoring the rules of normalization and deciding to turn a relational database into an electronic version of a bunch of 3x5 cards.

You can do it with constraints. You can do it with triggers. And you can decide to be smart and not do it at all. Get a book on relational database theory and normalization and read it.

So I gave an example of a 1:1 relationship and asked how it could be done, and you have now replied to that answer (as can be seen below) stating that I should do it as a 1:1 relationship which is what I have done.

> Assuming a person manages one and only one department (otherwise it is a
1:M)
> put a column in the department table with the person id of the manager
from the
> person table.
>
> If it is a 1:M or M:M then I would suggest an intersecting entity with
two
> columns: One the department id and the second the person id of the
manager.

So now I'm all confused :(

I thought that you originally said that by creating a 1:1 relationship you would be, "...ignoring the rules of normalization and deciding to turn a relational database into an electronic version of a bunch of 3x5 cards."

But just now you have said, "Assuming a person manages one and only one department (otherwise it is a 1:M) put a column in the department table with the person id of the manager from the person table." and so creating a 1:1 relationship (although this can't be enforced with standard oracle referential integrity, I think).

So, just getting back to the original question, "How can a one to one relationship between two tables be created in Oracle 9i?" as I think that we have established the need for it. Received on Wed Mar 19 2003 - 18:11:47 CST