Crazy: FirstName / LastName set as primary key...

[RickDB]

I am looking at a database where the Primary Key is a composite of the customer's First & Last name. Obviously a huge problem! The funny thing is, the table was set up to with an Autonumber CustomerID, so all customers have this information already.

Here's the question: there is a one to many relationship with an orders table, how can I change the primary key from the composite key it uses now, to the autonumber field and not mess up the relationships?

Thanks for help in advance.

 

[namliam]

Yes you can.

Add the single PK column to the orders table.
Now run an update query on the orders table adding the new key
Now you have what you want and how it should be, and you can remove the composit key.....

 

[RickDB]

Actually I was mistaken....

The Primary key is a composite of the First/Last Name, but there is a Autonumber CustomerID field that is a FK to the Orders table.

What I need is there, so all I need to do is change the primary key - the relationship needs not change.

When I try, I get the error message: "You cannot change the Primary Key. This table is the primary table in one or more relationships."

Any ideas?

 

[RickDB]

I think all I need to do is delete the relationship, change the PK, then reestablish the relationship. This works right?

The relationships are number based, so I would think they would stay the same if I temporarily delete then re-establish the relationship right?

I am testing now, seems to work!

 

[namliam]

Yep that should work.... Odd enough that there would be a PK set on first/lastname fields...

 

[RickDB]

Thanks for the help, it's always appreciated!