SQL: How to generate a unique value for a Primary Key

Question

as the title states. I do have the following table:

CREATE TABLE exampleTable(
    ID int NOT NULL,
    Text varchar(255),
    PRIMARY KEY (ID)
);

By inserting Values:

INSERT INTO exampleTable (ID, Text)
VALUES (123, 'Hello World');

Sometimes a value can be added for a Primary Key, which is already in the Table, thereby will give you an Error.

Questions:

1. By which approach can we ensure that the value of a choosen Primary Key is always unique?

2. How to generate a Primary Key value, that is always going to be unique.

For 2. Question, possible approach:

//Check if the new Value is already in the Table by
//Iterating and comparing through all the Primary Key Values.

Answer 1

The first answer is simple

1. The PRIMARY KEY Constraint guarantee that the values are UNIQUE and you get an error if not

2. is more complicated, you can try to get the max(id) and then increase it either in source code or in a BEFORE INSERT TRIGGER, but in heavy duty servers you can get still errors anyway, that is why we use auto_increment or uuids

Answer 2

If you sent a primary key constrains, it only allow unique value. You can enable Auto-increment/IsIDentity column true.

---

An alternate way is change the type of id to uniqueidentifier and insert Guid manually. That would always unique. But not recommended

Answer 3

Why would you want to get a unique value for a primary key without using auto-increment? One answer: When there's a distributed application with multiple people trying to generate keys that are unique across all instances AND network latency prohibits accessing a central server for a key AND it's permissible to have a small "collision" rate.

Comments about this answer:

1. It's cumbersome and might take too much computer time.
2. It does not 100% guarantee unique keys -- just very, very close.
3. It's possible to get an arbitrary confidence factor that the generated key is unique by generating a longer key (change Result_Length to a larger number.
4. I have tested this algorithm on 8-letter keys and find less than 1 collision per 1,000,000 keys generated.

The solution expressed in VBA code:

Public Function fnGenerateAlmostUniqueKey as String
    ' Function to return almost-guaranteed-unique key value
    ' Increasing Result_Length decreases the probability of a collision
    ' Function returns a string like "AF4r9U7Y"
    Const Result_Length as long = 8   ' Adjust as needed
    Const Alpha as string = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789"
    Dim idx as long
    Dim Result as string
    Dim s as string        ' Intermediate value
    Randomize(Now())       ' Prevents everyone from using the same RND sequence
    For idx = 1 To Result_Length
        s = Mid(Alpha, Int(1 + Rnd() * 60), 1)
        Result = Result & s
    Next idx
    fnGenerateAlmostUniqueKey = Result
End Function