Changing a primary key on a foreign key relation

[darin]

I have a couple of tables that I'm changing. The change involves a change to the primary key; however, there are a number of relations on the old key. I do not want to change the relations, I just want to set the column as unique and make a different column the PK. However, the designer does not allow this. When I set the old key as unique (save it) and try to remove the check on the Primary Key, I get the following message:
"Sorry, this attribute is part of a foreign key relationship"

But, such a change is possible (at least on SQL 2005 and 2008) and if I create a column with the unique constriant (not a primary key) and create relations against it, it works fine. I just dont want to go through the hastle of deleting all the relations, making the changes and recreating the relations.

Any suggestions?

Thanks

[Paolo F Cantoni]

I have a couple of tables that I'm changing. The change involves a change to the primary key; however, there are a number of relations on the old key. I do not want to change the relations, I just want to set the column as unique and make a different column the PK. However, the designer does not allow this. When I set the old key as unique (save it) and try to remove the check on the Primary Key, I get the following message:
"Sorry, this attribute is part of a foreign key relationship"

But, such a change is possible (at least on SQL 2005 and 2008) and if I create a column with the unique constraint (not a primary key) and create relations against it, it works fine. I just don't want to go through the hassle of deleting all the relations, making the changes and recreating the relations.

Any suggestions?

Thanks

Hi Darin,

check out: BAD: [  ] Unique - totally defective.  You may be falling foul of the same problem...

Also, searching the forum for "primary unique" will yield other users with similar problems.

It looks from your experience that Sparx haven't fixed things yet so, you may have to take the hard route...

HTH,
Paolo

[«Midnight»]

Seconded...

I gave up on this one during the 7.0 beta. The problem might well have been there before, but that's were it became something I couldn't work with. Somehow I (we) don't seem to be able to get the point across.

The end result is perfectly legal SQL, but enforcement of bad [= incorrect] database design.

[darin]

I'm not sure if it's the same issue. From what I've seen the primary key and Unique checks work as I would expect them to (judging from the script that is created).

However, the issue is when changing a column that is involved in a foreign key relation.

To recreate my issue create two table TableA and TableB. Add column to each A1, A2 and B1, B2. Make A2 the primary key. and B1 the primary key. Create a fk relation from TableB B2 to TableA A2. Now change your mind (we are designing and being able to change ones mind is very important). Make B2 Unique (we still want to FK on this field) now try to remove the PK check. So we get the message and it will not allow us to remove the PK constraint.
In my mind this is the real bug, if I made A2 unique from the start (and not a primary key) I would still be able to create my FK as described before and make A1 the primary key.

As for the statement bad[=incorrect] database design. I'm not sure what means, what is bad? Does the statement mean that not joining a fk on the pk is bad design, and as such the software (in spite of what you can leagally do in the language) should not allow you to do that. But, sometimes (if you take the overall system into account) the so called "bad" design (considered bad maybe from an academic standpoint) is the correct design for the system: then it's not really a bad design.

[Paolo F Cantoni]

I'm not sure if it's the same issue. From what I've seen the primary key and Unique checks work as I would expect them to (judging from the script that is created).

Probably because you are using very simple cases.  With the proviso that I haven't recently gone through this area of EA, the more complex (and, for me, more usual cases) don't work.

However, the issue is when changing a column that is involved in a foreign key relation.

Yes

To recreate my issue create two table TableA and TableB. Add column to each A1, A2 and B1, B2. Make A2 the primary key. and B1 the primary key. Create a FK relation from TableB B2 to TableA A2. Now change your mind (we are designing and being able to change ones mind is very important). Make B2 Unique (we still want to FK on this field) now try to remove the PK check. So we get the message and it will not allow us to remove the PK constraint.
In my mind this is the real bug, if I made A2 unique from the start (and not a primary key) I would still be able to create my FK as described before and make A1 the primary key.

You're correct.  The issue appears to be similar to the problem I had with Embarcadero ER/Studio.  It took me 6 years to convince Embarcadero that you didn't have to link to a primary key (IDEF1X) and you could link to a unique key in the foreign table.  In this case, it probably is EA being too cautious with your changes.  You should be able to unselect the primary key, so long as the key remains unique.   However, since EA intimately binds the IsUnique property on the column with the IsInPK property, you're stuffed.  In one of my posts, I said EA needed to distinguish between the IsUnique on the column and IsUnique on the Index/Key.  In this case your proposed changes have nothing to do with the nature of the foreign key relationship and should be allowed.

As for the statement bad[=incorrect] database design. I'm not sure what means, what is bad? Does the statement mean that not joining a FK on the pk is bad design, and as such the software (in spite of what you can legally do in the language) should not allow you to do that. But, sometimes (if you take the overall system into account) the so called "bad" design (considered bad maybe from an academic standpoint) is the correct design for the system: then it's not really a bad design.

I'll leave David to clarify what he meant by that, but I'm pretty sure he didn't mean that NOT joining to the PK was bad design. As I said above, it sometimes takes a LONG time for modelling providers to get the message.

Paolo

[«Midnight»]

Paolo pretty much has it right. [No surprise there.]

With the same proviso as Paolo, EA's handling of Unique constraints is a bit to coarse-grained. This shows up in various places where a multi-part primary or unique key is created, and at least one of the component fields is not unique.

David