What is an Auto-increment Primary Key?
Auto-increment fields fill automatically. These are also called "Identity" fields.
Whenever a new record is
created in a table that has an auto-increment,
the DBMS automatically enters a value in that field.
(The user is not allowed to
manually enter or change a value in an auto-increment primary key field.)
The value entered into the auto-increment field is always
a unique value. That is, no other record has the same value in that
field and no record has a Null value either. For this reason,
an auto-increment field is an excellent candidate
key because (a) every record has a value in that field (not Null), (b)
no value is ever duplicated in another record. In most cases, the record
values follow a simple sequence: the first record will be 1,
the second 2, the third 3, and so on. (However, it is not necessary to
start from 1, and it is possible to increment new records by 2s, 3s, 10s,
and so on. For example, the series could be 120, 130, 140, 150, ....)
How to Create an Auto-increment Primary Key
There are several ways to make an auto-increment primary key. The
method described below uses the SQL Server Management Studio GUI
to create this key and to auto-populate it with unique, sequential values.
- In the Management Studio IDE ...
Open SQL Server Management Studio. Find your database,
then find the table to which you want to add an auto-increment PK.
Right-click on that table and select
"Design" in the dropdown menu ....
This will open up the table designer tool.
(1) Add the PK field, naming it xxxID ("LockerID" in this illustration),
(2) Select "int" as the data type,
(3) Right-click the left margin of that field and select
Set Primary Key from the dropdown menu.
Do NOT save your changes yet!
(4) With the PK field selected (see illutration below),
search the Column Properties window for the
"Identification Specification" property (highlighted in blue below).
(5) Open Identity Specification by clicking the arrowhead (in the left margin).
(6) Change the properties as illustrated here.
(7) Close and save the Design window.
-- END OF NOTES ON CREATING AUTO-INCREMENT PRIMARY KEYS
End of Instructions
|