Using numeric autoincrement identity columns create a ton of problems. I touch of some of them in
this article especially for tables that heavily modified. That article covers Oracle and PostgreSQL, so I won't repost the DDL.
For SQL Server you would need something like this.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Issuer](
[ID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[Name] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Issuer] PRIMARY KEY CLUSTERED
(
[Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_Issuer] UNIQUE NONCLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Issuer] ADD CONSTRAINT [DF_Issuer_ID] DEFAULT (newid()) FOR [ID]
GO
As you can see, the row identity column is not the human meaningful data, thus, not what is going to be searched on most of the time. Therefore, the primary key is the human meaningful column "Name"
Additionally if you want to generate your IDs in code, IDENTITY columns require that you
Enable Identity Insert. On the other hand, uniqueidentifier columns with the default as shown above mean you can insert your own value from
Guid.NewGuid()
.
NOTE: Using any kind of sequential id generation in code (like MAX(ID)+1) could cause conflicts if multiple inserts are happening at the same time (in other words 2 processes or threads could generate the next id as 5). Where as using a Guid.NewGuid generates a pseudo-random number that has a 1 in 2
122 chance of duplication.