CODEWITHSUNDEEP
ms-accessduplicatesprimary-keycomposite-key
Kunal Binivale
Kunal Binivale

Reputation: 11

MS Access - Making two columns with duplicate entry as one unique Composite Primary key

I am trying to develop a database to store energy related data. Basically, it will have 4 columns 

1. Entity Name:            Text format
2. Time-Date:              DD/MM/YYYY hh:mm format
3. Value:                  Number 0.00 format
4. Description:            Text format

e.g. 

Main_Meter_kWh | 29/04/2015 00:30 | 30.00 | Mains meter energy reading
Main_Meter_kWh | 29/04/2015 01:00 | 20.00 | Mains meter energy reading
Main_Meter_kWh | 29/04/2015 01:30 | 25.00 | Mains meter energy reading
Main_Meter_kWh | 29/04/2015 02:00 | 32.00 | Mains meter energy reading
HVAC_Meter_kWh | 29/04/2015 00:30 | 30.00 | HVAC meter energy reading
HVAC_Meter_kWh | 29/04/2015 01:00 | 20.00 | HVAC meter energy reading
HVAC_Meter_kWh | 29/04/2015 01:30 | 25.00 | HVAC meter energy reading
HVAC_Meter_kWh | 29/04/2015 02:00 | 32.00 | HVAC meter energy reading

So now as you can see, the issue is that I want to create a composite primary key using Col 1 & 2 i.e. Entity Name and Date-Time. But both the columns would have duplicates values.

Therefore, I want to know if I can create a Composite Primary Key which as a combination of Col 1 & 2 would be Unique but individually would accept duplicate values.

Look forward to hear from Wise Minds!!!

Thanks in advance.

Upvotes: 1

Views: 254

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123549

There is no problem with duplicate values in the individual columns of a multi-column Primary Key, provided that the Primary Key does not contain duplicate values across all columns. So a Primary Key on ([Entity Name], [Time-Date]) would be fine for the sample data in your question because even though there are repeated values in the [Entity Name] column and repeated values in the [Time-Date] column there are no rows where both values are identical.

Upvotes: 1

Related Questions