How to change primary keys without the need to break relationships?

[Thomasso]

Hello everybody,

I have a table ORDERS and ORDER DETAILS.



The ORDERS table has primary key set as the internal ID of that order, for example 2017ABC0001 (year, supplier code and number). We need to change this to ORD2017ABC0001 and DEL2017ABC0001 (order and delivery) to distinguish accordingly.

The problem is that I already have many ORDER DETAILS items assigned to the orders so it won't let me do that.

Any work around this?

Thanks very much

Tomas

[aytee111]

The reason why most people use autonumber primary keys!

You will have to recreate the tables:
- make a copy of both tables and add this as a new field
- run an update query on Orders table and change the primary key, putting the result in the new field
- run an update query on Order Details table, join by the old PK and update the new field to the one on the Orders table
- on both tables, remove the current primary key, change the new field to the primary key

Test and check, test and check!

[jwhite]

Make a backup copy of your database first.

In Table Relationships, delete the link from the primary table and the child table.
Change the primary key value in both tables as needed.
Recreate the link in Table Relationships as it was previously, and save.

It begs to ask a question, why you would need to do this - primary keys are not meant to change.

[Thomasso]

I just wanted to make the change for the upcoming orders but I didn't know that I can actually change input mask and leave past orders intact. Nevermind then, I just changed the input mask and will use the new format from now on. I can live with the past orders to have the old format.

Thanks anyway.

[ArviLaanemets]

Close all forms until ended;
In Orders table, add a new field OrderIDNew;
Run an Update query on Orders table, setting new ID values into OrderIDNew field;
Run an Delete query on OrderDetails table, deleting all orphan entries (OrderID don't exist in Orders table);
Run an Update query on OrderDetails table, setting OrderID values to corresponding OrderIDNew values from table Orders;
Repeat last 2 steps for any tables having OrdeID as foreign key;
Run an Update query on Orders table, setting IrderID = OrderIDNew;
Delete the column OrderIDNew from Orders table.

Edit. As first step, change the format of OrderID in all tables to match with length of new OrderID.

[orange]

Tomas,

In addition to all the advice you have received, remember that Access ( and relational databases generally) work very well with 1 fact in 1 field. I recommend you do not try to concoct codes when storing data. You can always concatenate atomic fields for presentation.
Fix the root problem if this system is important; don't add workarounds to address symptoms.

eg 2017ABC0001 (year, supplier code and number).

OrderDate
SupplierCode
Order????


Here is a link to info on database design and planning that may be useful.