Auto Increment Composite Primary Key

Question

I have an Access database that I am trying to create. I want to use two columns from the table to create the primary key.

Projectbl (ProjectID, ProjectRegion, other columns)

I want the ProjectID to autoincrement. That's fine. So far whenever I add a new project, the ProjecID increments by itself.

I want to create a new column in that same table that will be the ProjectRef where the new number would be a composite of ProjectID and ProjectRegion.

So for example

ProjectID ProjectRegion OtherCol 
---------------------------------
1            500          ...
2            100          ...
3            200          ...
4            500          ...
5            500          ...
6            100          ...

I want the table to actually look like that

ProjectRef ProjectID ProjectRegion OtherCol 
--------------------------------------------
5001        1            500          ...
1002        2            100          ...
2003        3            200          ...
5004        4            500          ...
5005        5            500          ...
1006        6            100          ...

So I am trying to add a new project: the projectID will autoincrement to 7, but the ProjectRef will be 'ProjectReg'7 whatever the ProjectReg is.

I know I can create a composite key with

CREATE TABLE 'Projectbl'
(
     ProjectID INT(10) NOT NULL AUTO_INCREMENT,
     ProjectRegion INT(10) NOT NULL,
     other columns VARCHAR(100),

     CONSTRAINT ProjectRef 
         PRIMARY KEY ('ProjectRegion', 'ProjectID')
)

How can I actually display ProjectRef in the table?

I'm using Access and every time I try to run the query create table, it say syntax error in the Auto_Increment. But before the auto_increment, I run it. I want to display the ProjectRef and have ProjectID autoincrement — DrD4rk
What is the means by which new records come into the table? Most likely, you'll want a VBA function for this. It won't be terribly complicated, but it will allow you a lot of flexibility in how you perform this task. — Pat Jones
Also, the syntax that you are using in the CREATE TABLE is SQL Server syntax, which often does not carry over to Access (Jet) SQL. That's probably why you're getting the syntax error. — Pat Jones
I see. Well, what you're trying to do is essentially a calculated column that depends on other columns. It's redundant. When you design the table, Access gives you the capability to define a compound key. You should do so with the two original columns and leave it like that. — Pat Jones

Tom H Tom H · Accepted Answer · 2017-11-16T19:08:20

You shouldn't have the ProjectRef column at all. This violates basic rules of database normalization. If you want your front end to display the ProjectRef then just calculate it from the columns that you have.

Auto Increment Composite Primary Key

1 Answers