SQL (access) - add a new field to a composite primary key

Question

I have a table called table1 which has a composite primary key using 'ScreenId' and 'ActivityItemId'. I have added a new field 'ProductId' which has a NOT NULL constraint. I want to add ProductId to the composite primary key.

I thought that this would work

db.execute "ALTER TABLE table1 PRIMARY KEY (ScreenId, ActivityItemId, ProductId)"

but i get an error, i think this syntax only works when creating a table.

Can anyone help me out with the SQL? (i can't use a visual basic solution here btw, i'm actually using a ruby interface in order to run the sql, so it needs to be just in SQL)

thanks max

Answer 1

Try to drop your current primary key and then create new one:

ALTER TABLE table1 DROP CONSTRAINT pk_name

ALTER TABLE table1 ADD CONSTRAINT pk_name PRIMARY KEY (ScreenId, ActivityItemId, ProductId)

Answer 2

Think about a surrogate key instead of a composite key. You don't want to have to change your schema every time your business logic changes.

It might be worth it to keep those relationships as unique constraints and add a surrogate key.

Answer 3

Have you tried to change the primary key with the design view instead of using SQL?