CODEWITHSUNDEEP
databasevbams-access
Danny Houston
Danny Houston

Reputation: 91

Creating a custom primary key in access

Is there a way to create a primary key in access based on provided attributes? For example, say I have a school database, with an entity containing a course's code being in the form LL and it's qualification being in the form LLLA. How would I make it so that whenever a new course is entered into this entity, the primary key for that record is the two attributes amended together in the form LL\LLLA? (Eg IT\BTEC, RM\GCSE)

(Just for reference, a composite key is not an option here as this attribute needs to be referenced in another table)

Upvotes: 0

Views: 1136

Answers (1)

John Mo
John Mo

Reputation: 1326

You can create a composite primary key and reference it in another table, but I don't recommend it. As you may be anticipating, it will be extra work to propagate both columns to the related table and all joins on both tables will then have to reference both of the columns in the composite key. Access does provide a little help in working with composite keys in the form of cascading updates and deletes, but the overall effort involved in dealing with composite keys probably isn't worth it.

I recommend using an autonumber column as a surrogate primary key and creating a unique index on the columns that represent your natural key.

Upvotes: 1

Related Questions