How to set a primary key on a table correctly?-CodePudding

I'm a bit confused, all tablets I've seen (I haven't seen many) have an auto-increment field as their primary key.

Is this the best way to set a primary key?

For example, I have the following tables called Assets for inventory and I don't know the best way to set the field as a primary key

Ask: I don't know whether to set an auto-increment field (indentity) as a primary key or set the code field as a primary key

Way one

CREATE TABLE Asset_A
(
    code INT NOT NULL,
    name VARCHAR(150),
    PRIMARY KEY(code)
);

Way two

CREATE TABLE Asset_B
(
    id INT IDENTITY,
    code INT NOT NULL, -- BUT THIS IS THE CODE OF ASSET. For SECOND PK?
    name VARCHAR(150),
    PRIMARY KEY(id)
);

The code field is a numeric field where a unique ID is set for this asset example: name: iPhone 10 code: 1000 etc..

example of tables with data:

GO
INSERT INTO Asset_A
    (code,name)
VALUES
    (1000, 'iphone 10'),
    (1001, 'iphone 11'),
    (1002, 'iphone 12'),
    (1003, 'Samsung Galaxy S8'),
    (1004, 'Samsung Galaxy S9'),
    (1005, 'Samsung Galaxy S10');
GO
INSERT INTO Asset_B
    (code,name)
VALUES
    (1000, 'iphone 10'),
    (1001, 'iphone 11'),
    (1002, 'iphone 12'),
    (1003, 'Samsung Galaxy S8'),
    (1004, 'Samsung Galaxy S9'),
    (1005, 'Samsung Galaxy S10');

GO
SELECT *
FROM Asset_A 

GO
SELECT *
FROM Asset_B

GO
sp_columns Asset_A;
GO
sp_columns Asset_B;

CodePudding user response：

Your question is not something simple caused by lack of experience. It is a judgement call.

A key that represents an already existing asset is called a "natural" key, in your case, [code]. A system-generated key is called a "surrogate" key, in your case, [id].

Should you use a surrogate key or stick to the natural one? Well, each has its pros and cons, and this is one huge debate for which you will find a plethora of sources. My personal best link is this. There are other links in the comments, and probably will be in other answers. Search the internet more, too.

If I had to go out of my way and choose for you based on my own experience and how much "keyworthy" I find your [code] to be, I would indeed use [code] over a surrogate. But, this is subjective. Read, study, practice, and these types of judgement calls will come easier.

CodePudding user response：

The Primary Key must be unique within the table, and there is no such thing as a second Primary Key in the same table. You can have another unique non-clustered index, but not a second Primary Key. In this case, if code is guaranteed unique across all other columns in every row, it will work as a Primary Key and you won't need the IDENTITY column.

I would recommend doing some additional research on primary keys and how to choose them. It's certainly not the case that a column with IDENTITY set has to be the primary key. However, there are limitations, such as the PK in SQL Server can be no larger than 900 bytes.