Sparx Systems Forum
Enterprise Architect => General Board => Topic started by: HenryStock on June 15, 2008, 12:55:22 pm
-
I am working on my first attempt to use Enterprise Architect to do data modeling for my application database. So right now, all my classes are stereotyped as tables and I am using associations stereo typed as links to try to build foreign keys.
One thing that I have noticed is that in the association properties dialog the source tab gives me the names of all the fields, but the destination tab only gives me the names of primary key indexes or unique indexes. Even if I build a secondary index on other fields, that index does not show up as an option in the destination dialog. What I don't understand is why the dialog doesn't show the columns. If I build a foreign key manually that is what I am going to use to build it.
What am I missing here?
-
Hi Henry,
Assuming I understand your concern correctly, perhaps I can shed some light.
Think about a foreign key for a moment. From the 'non-lookup' table - the one that reads the lookup table - the appropriate lookup code (the foreign key) must point to a single record in the lookup table. Imagine a (very simple) example with (say) 2-letter country codes. If an address table had a foreign key of "CA" for my country, looking that up in the Country table should return a single record for Canada. So far so good. Convention tells us to make the lookup field (or fields) in the lookup table the primary key. In reality a candidate key would do just fine.
In fact, if this were just an ordinary field the application would seem to work correctly. As long as the country codes were unique we would get the expected results. However, the whole concept of the foreign key - allowing the DBMS engine to enforce the relationship - has been compromised.
If someone later came along and decided to add US states and Canadian provinces - let's say this application is used in only these two countries - we'd suddenly have a problem. Suddenly my address would lead to a result of both CA = "Canada" and CA = "California" and the application would fail.
DBMS engines need to know that the target on the lookup table is a unique field or set of fields. With most engines this is done through definition of a unique key. [With some products this may only be done via a primary key.]
EA does it's best to help you by enforcing this. It also needs to ensure that it can generate correct DDL to set up both ends of the relationship.
HTH, David
-
I was working on the premise that you don’t mess with primary keys after they are created. I could be totally off base with this assumption. I know that it was true in some databases that I have worked with in the past. I was creating an intermediate table to allow a many-to-many relationship between people and phone numbers. If changing the primary key is not a problem my primary key would be a combination of two integers, (person_id and phone_id). Then if I switch a person from one phone number to another phone number already in the phone table, all I have to do is to alter the phone_id.
But for some reason, I thought that was not allowed, so I created a separate id field as the primary key.
Another issue though ... I want to build a foreign key check constraint. From what I have read so far, I would do this through the class operations, using a stereo type of check. Beyond that, I am not sure what I should do next. The following code is an example of the code I would like Enterprise Architect to generate when I generate the DDL.
ALTER TABLE [dbo].[property] WITH CHECK ADD CONSTRAINT [FK_property_property_def] FOREIGN KEY([property_def_id])
REFERENCES [dbo].[property_def] ([id])
In this sample, the property table uses the integer column property_def_id as part of its primary key. But I want to ensure that whatever integer is inserted already exists in the property_def table.
-
You can do it either way Henry. It all depends on how you want to construct the join table.
You can create a 'surrogate' key field which is unique, then the two fields for the entity table id fields don't need to be part of the key. You'd still want to make the combination of the two fields unique. See creating keys (or indexes) in the EA documentation. Some organizations create surrogate keys for join tables; this could happen if you have lower-level joins off the join table. [I've not explained it well, but if you have this situation you'll know.]
You can also use the two id fields to create a compound primary key. Just mark both of them as the primary key when you first create the table. EA will recognize that since there can only be one primary key, you want to concatenate the marked fields. Do all this before you attempt to invoke the foreign key dialog. Most organizations use this method. A few insist upon it for legacy reasons.
[My understanding is that the math works either way, so go with what your data designers tell you.]
It's been a while since I did this, so I have not attempted to walk you through the individual actions. If you get into trouble send me a message.
David