1

In an Access database I have a linked table that is connected to a SQL Server view.

The source view has data in the form

c1  |  c2  |  c3  |  c4
-----------------------
a   |  a1  |  xya |  q
a   |  a1  |  xya |  w
a   |  a1  |  xyb |  e

And in Access the linked table is

c1  |  c2  |  c3  |  c4
-----------------------
a   |  a1  |  xya |  q
a   |  a1  |  xya |  q
a   |  a1  |  xya |  q

Edit:

There are more rows like these. The copies are grouped by c1.

I have found a reason for this.

enter image description here

Column c1 was set as an unique identifier, whilst all of them should be.

Nevertheless I still a have question here then. "To ensure data integrity and to update records you must choose a field or fields that uniquely identify each record. Select up to ten fields."

If then I have more than 10 columns that together uniquely identify each record I cannot choose them, which therefore leaves me with no columns picked as identifier, which, as I understand, means that I cannot be sure that the data integrity will be preserved and records cannot/might not be updated?

On the other hand I should have a single column with unique identifier otherwise it's a bad design, is that correct?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
zaptask
  • 687
  • 1
  • 8
  • 18

1 Answers1

1

Having the unique record identifier spread across more than one column - a composite key - is not in itself a bad design, but having it spread across ten almost certainly is.

A composite key will more likely be a "natural key" (one made up of values that exist for reasons other than to make a key), and in my experience is more common in older systems.

The alternative of a single column with a unique identifier (that could be an int, guid, or some other type) is known as a surrogate key, and the value has no meaning other than to uniquely identify a record.

There is no right or wrong answer as to which is best, it depends on the particular requirement. Below are some links with further info on how to make the appropriate choice:

Surrogate vs. natural/business keys

http://www.techrepublic.com/blog/10-things/10-tips-for-choosing-between-a-surrogate-and-natural-primary-key/

http://www.agiledata.org/essays/keys.html

Community
  • 1
  • 1
beercohol
  • 2,577
  • 13
  • 26