1

I am still battling to work out if the use of an identity column for a PK, when you COULD get away with using an exisiting column, is the right way to go.

Example:

CREATE TABLE person_link
(
     person_link_id INT NOT NULL IDENTITY(1,1)
    ,owner_person_id INT NOT NULL
    ,link_person_id INT NOT NULL
    ,link_date_created DATETIME NOT NULL DEFAULT(GETDATE())
    ,deleted_person_id INT NULL

     CONSTRAINT pk_person_link PRIMARY KEY(person_link_id)
    ,CONSTRAINT fk_person_link_owner FOREIGN KEY (owner_person_id) REFERENCES person (person_id)
    ,CONSTRAINT fk_person_link_link FOREIGN KEY (link_person_id) REFERENCES person (person_id)
)

OR, should I remove the person_link_id, and rather put a primary key across my two columns which will always be unique. i.e:

CONSTRAINT pk_person_link PRIMARY KEY(owner_person_id, link_person_id)

Is it just a personal choice, or is there a good reason NOT to use the identity (which, I am in favour of, purely because - I do it all the time).

Craig
  • 18,074
  • 38
  • 147
  • 248
  • possible duplicate of [Surrogate vs. natural/business keys](http://stackoverflow.com/questions/63090/surrogate-vs-natural-business-keys) – D'Arcy Rittich Aug 16 '12 at 01:22

3 Answers3

3

An advantage of using the identity column as the clustered index is that every insert will be the last record, so there is no need for SQL Server to resort the index.

There is also an advantage when joining, you only need to have 1 reference in the child table.

These may not be relevant depending on the data that you are adding or the database schema (if you need to use this elsewhere)

Greg
  • 3,442
  • 3
  • 29
  • 50
2

I favor the identity column. Repeatedly, I find that it is useful to find the exact row that something is on.

For instance, I almost instinctively write the following query to find the most recent rows added to a table:

select t.*
from t
order by 1 desc

This works, because I always have the identity be the first column in the table.

In addition, it helps to have the primary key be a single field when doing joins on the table. If you want to identify a particular entity, there is nothing like having a specific entity id.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
2

Depending on the needs I personally would use a natural primary key on the record so the two columns which will always be unique.

So the table would be:

table:
owner_person_id int -- PK
link_person_id int -- PK

But it really depends on the plans that you have for the table and your database design on which option to choose.

Taryn
  • 242,637
  • 56
  • 362
  • 405