1

If there is no unique column can identify each row in the table,

then my primary key will be at least a set of two fields.

Is that correct?

If it is correct,then when I draw the Relationship Diagram, I have to underline the two attributes that formed the primary key?

Thankyou

runcode
  • 3,533
  • 9
  • 35
  • 52

3 Answers3

2

Here is some terminology:

  • A superkey is a set of columns that, taken together, uniquely identify rows.
  • A candidate key (or just: "key") is a minimal1 superkey. Sometimes a key contains just one column, sometimes it contains several (in which case it is called "composite").
  • For practical reasons, we classify keys as either primary or alternate. One table has one primary key and zero or more alternate keys.
  • A key is "natural" if it arises from the intrinsic properties of data. In other words, it "means" something.
  • A key is "surrogate" if it doesn't have any meaning by itself - it is there only for identification purposes. It's typically implemented as an auto-incrementing integer, but there may be other strategies such as GUIDs (useful for replication). It is quite common for natural keys to be composite, but that almost never happens for surrogates.

If there are no "obvious" natural keys, the whole row can always act as a key2. However, this is rarely practical and in such cases you'll typically introduce a surrogate key just for the purpose of identifying rows.

Sometimes, but not always, it is useful to introduce a surrogate in addition to the existing natural key(s).

An ER diagram will clearly identify the PK3, whether it is natural or surrogate and whether it is composite or not. How exactly this will look like depends on a notation being used, but PK will typically be drawn in a graphically distinct manner and possibly prefixed with "PK".

1 I.e. if you were to remove any column from it, it would no longer be unique.

2 A database table is a physical representation of the mathematical concept of "relation". Since relation is set, there is no purpose in having two identical rows, so at the very least the whole row must be unique (an element is either in the set or isn't - it cannot be "twice" in the set, as opposed to multiset).

3 Assuming it not just entity-level so no attributes are show at all.

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
1

You are correct, after a fashion. Technically, a primary key and a unique key can be two distinct things. You can have a primary key on a table or entity uniquely identifying that entity and also. On the same table, you can have a unique key constraint which can then be used to ensure that no two rows, according to criteria chosen by you, end up having the same property. So you can have both a primary key and a unique constraint on the same table. Simply have a primary key column that will be autogenerated in your DB and then pick the two columns in your table that you want to use to enforce the unique key constraint

kolossus
  • 20,559
  • 3
  • 52
  • 104
0

If you don't have primary key you can identify your datas but it's not performant.

And as best practise you use primary on your table.

The preference is to use auto increment column as primary key

Aghilas Yakoub
  • 28,516
  • 5
  • 46
  • 51