Re: How can I specify columns for foreign key relationships in Oracle Designer?

"Ally" <afirenze95_at_yahoo.com> wrote in message news:e7f9ce51.0106011119.189dc6b9_at_posting.google.com...
> Hi, would you readers out there know how I can specify which columns
> are used in establishing foreign key relationships in Oracle Designer?
>
> I am using Designer 6i, Oracle server 8.1.7 on Win2K. I created some
> entities in ERD, and after I ran it through the Database Design
> Transformer and view the entities again in Design Editor, Designer has
> created a new column in one of the entities for it to refer to the
> primary key in the other entity. I can view the properties of that new
> column, but I can't seem to change any of its properties, including
> its name.
>
> Here is some info about my situation and what I am trying to
> accomplish --
> -> I have a table named Addresses, with a primary key column named
> Address_id.
> -> I have another table named Employees, with a primary key column
> named Employee_id. This table also has a column called "Home_Address"
> which contains an Address_id. This table has another column called
> "Business_Adsress" which contains another Address_id.
>
> -> I want to have 2 foreign key relationships in the Employees table,
> columns "Home_Address" and "Business_Address", both referring to the
> primary key column in the Addresses table.
>
> -> I have run into 2 problems with Designer in trying to accomplish my
> task:
> 1) Because each employee can have more than one address, it is a one
> to many relationship in the ERD. From the documentation that I have
> read, it's always the "many" side of the relationship that gets a
> foreign key column created. Therefore, Oracle Designer decides that
> the Addresses table that should have a foreign key column referring to
> the Employees table. So it generates a foreign key column in the
> Addresses table refering to the primary key in the Employees table.
> But that is the opposite of what I want to do.
>
> 2) I have already specified the "Home_Address" and "Business_Address"
> columns in my Employees table, and I would like these 2 columns to be
> the foreign key columns. But I cannot find a way to specify that using
> these columns' Property Palette, or the Foreign Key Column Property
> Palette.
>
> I am new to Designer, so maybe the more experienced users could give
> me some pointers in how they approach an issue like this. I don't
> understand how this is suppposed to work, I mean, if you accept the
> foreign key column Designer creates for you, but you are not allowed
> to modify the name or any other properties of that column, then it's
> not doing you any good, is it?
>
> Anyway, any insight would be appreciated. I've pulled way too much of
> my hair out over this!
>
> Thanks!
> Ally

1. it looks like you've drawn your relationship the other way around
2. it looks like you need two entities on your ERD, home address and business address, and you need to draw foreign key relationships between those two entities and employees. Do NOT do this on table level, from your description it looks like your ERD is incorrect, Designer would have taken care of that.
3. if you are making manual changes to the table definition you will what I call 'break the rope', ie any further change on the ERD and regenerating the table level will force you to repeat your manual alterations. You should *NOT* bother about the foreign key columns, *NOT* change the properties (if your ERD is correct, the column properties will be correct too) and you should learn to live with the naming conventions. You can specify ERD and table short names, and they will be used creating foreign keys. If you think you need to foreign key properties and naming, it looks like you have trouble understanding one of the main advantages of Designer: that you can and should do on an as high as level as possible (on the conceptual/ERD level) and you should have Designer to the rest for you.

Hth,

Sybrand Bakker, Oracle DBA Received on Sat Jul 21 2001 - 23:48:27 CEST